javascript - Sequelize query SELECT * FROM Table only returning one row

I'm currently using PostgreSQL and Sequelize.js to query some data. When I'm using sequelize.query(), it only returns one row data but when I enter it through pgAdmin it works as expected.

Here is the code I use in sequelize.query().

   SELECT table2.student_id,
          s.canvasser_name,
          l.level_name,
          table2.total_score
   FROM (SELECT table1.student_id,
                sum(table1.max_score) total_score
         FROM (SELECT sq.student_id,
               max(sq.score) max_score
               FROM public.student_quiz sq
               GROUP BY sq.quiz_id, sq.student_id) table1
         GROUP BY table1.student_id) table2
   INNER JOIN public.student s
           ON s.id = table2.student_id
   INNER JOIN public.level l
           ON l.id = s.level_id
   ORDER BY table2.total_score DESC
   LIMIT 10;

And here is the nodejs code

const getRank = (option, logs = {}) => new Promise(async (resolve, reject) => {
  try {
    let { offset, limit } = option;
    if (!limit) limit = 10;
    const result = await sequelize.query(
      `SELECT table2.student_id,
              s.canvasser_name,
              l.level_name,
              table2.total_score
       FROM (SELECT table1.student_id,
                    sum(table1.max_score) total_score
             FROM (SELECT sq.student_id,
                   max(sq.score) max_score
                   FROM public.student_quiz sq
                   GROUP BY sq.quiz_id, sq.student_id) table1
             GROUP BY table1.student_id) table2
       INNER JOIN public.student s
               ON s.id = table2.student_id
       INNER JOIN public.level l
               ON l.id = s.level_id
       ORDER BY table2.total_score DESC
       LIMIT 10;`,
      { plain: true }
    );

    return resolve(result);
  } catch (error) {
    let customErr = error;
    if (!error.code) customErr = Helpers.customErrCode(error, null, undefinedError);
    logger.error(logs);
    return reject(customErr);
  }
});

And here is the code that consume the function above

const getRankController = async (req, res) => {
  try {
    const { offset, limit } = req.query;
    const result = await getRank({ offset, limit });

    if (result.length < 1) {
      return Helpers.response(res, {
        success: false,
        message: 'cannot get score list'
      }, 404);
    }

    return Helpers.response(res, {
      success: true,
      result
    });
  } catch (error) {
    return Helpers.error(res, error);
  }
};

In the meantime, I'm trying another approach using the sequelize built in function, here is the code.

const getRank = (
  option,
  logs = {}
) => new Promise(async (resolve, reject) => {
  try {
    // eslint-disable-next-line prefer-const
    let { offset, limit } = option;
    if (!limit) limit = 10;
    const result2 = await StudentQuiz.findAll({
      attributes: ['studentId', [sequelize.fn('sum', sequelize.fn('max', sequelize.col('score'))), 'totalPrice'], 'quizId'],
      group: 'studentId',
      include: [
        {
          model: Student,
          include: [{
            model: Level
          }],
        },
      ],
      offset,
      limit
    });
    
    return resolve(result2);
  } catch (error) {
    let customErr = error;
    if (!error.code) customErr = Helpers.customErrCode(error, null, undefinedError);
    logger.error(logs);
    return reject(customErr);
  }
});

This one does not work since it is nested function, I kinda don't get it how to reproduce it.

I've tried to do some simple query like SELECT * FROM table and it returns one row, and then I've found out that I need to add "public" to table name so it became SELECT * FROM public.table and it works out. Well, until I'm trying the code in the second code block.

Any answer or advice will be appreciated, thank you.

I'm currently using PostgreSQL and Sequelize.js to query some data. When I'm using sequelize.query(), it only returns one row data but when I enter it through pgAdmin it works as expected.

Here is the code I use in sequelize.query().

   SELECT table2.student_id,
          s.canvasser_name,
          l.level_name,
          table2.total_score
   FROM (SELECT table1.student_id,
                sum(table1.max_score) total_score
         FROM (SELECT sq.student_id,
               max(sq.score) max_score
               FROM public.student_quiz sq
               GROUP BY sq.quiz_id, sq.student_id) table1
         GROUP BY table1.student_id) table2
   INNER JOIN public.student s
           ON s.id = table2.student_id
   INNER JOIN public.level l
           ON l.id = s.level_id
   ORDER BY table2.total_score DESC
   LIMIT 10;

And here is the nodejs code

const getRank = (option, logs = {}) => new Promise(async (resolve, reject) => {
  try {
    let { offset, limit } = option;
    if (!limit) limit = 10;
    const result = await sequelize.query(
      `SELECT table2.student_id,
              s.canvasser_name,
              l.level_name,
              table2.total_score
       FROM (SELECT table1.student_id,
                    sum(table1.max_score) total_score
             FROM (SELECT sq.student_id,
                   max(sq.score) max_score
                   FROM public.student_quiz sq
                   GROUP BY sq.quiz_id, sq.student_id) table1
             GROUP BY table1.student_id) table2
       INNER JOIN public.student s
               ON s.id = table2.student_id
       INNER JOIN public.level l
               ON l.id = s.level_id
       ORDER BY table2.total_score DESC
       LIMIT 10;`,
      { plain: true }
    );

    return resolve(result);
  } catch (error) {
    let customErr = error;
    if (!error.code) customErr = Helpers.customErrCode(error, null, undefinedError);
    logger.error(logs);
    return reject(customErr);
  }
});

And here is the code that consume the function above

const getRankController = async (req, res) => {
  try {
    const { offset, limit } = req.query;
    const result = await getRank({ offset, limit });

    if (result.length < 1) {
      return Helpers.response(res, {
        success: false,
        message: 'cannot get score list'
      }, 404);
    }

    return Helpers.response(res, {
      success: true,
      result
    });
  } catch (error) {
    return Helpers.error(res, error);
  }
};

In the meantime, I'm trying another approach using the sequelize built in function, here is the code.

const getRank = (
  option,
  logs = {}
) => new Promise(async (resolve, reject) => {
  try {
    // eslint-disable-next-line prefer-const
    let { offset, limit } = option;
    if (!limit) limit = 10;
    const result2 = await StudentQuiz.findAll({
      attributes: ['studentId', [sequelize.fn('sum', sequelize.fn('max', sequelize.col('score'))), 'totalPrice'], 'quizId'],
      group: 'studentId',
      include: [
        {
          model: Student,
          include: [{
            model: Level
          }],
        },
      ],
      offset,
      limit
    });
    
    return resolve(result2);
  } catch (error) {
    let customErr = error;
    if (!error.code) customErr = Helpers.customErrCode(error, null, undefinedError);
    logger.error(logs);
    return reject(customErr);
  }
});

This one does not work since it is nested function, I kinda don't get it how to reproduce it.

Any answer or advice will be appreciated, thank you.

Share Improve this question edited Jul 3, 2020 at 22:41 asked Jul 3, 2020 at 6:57 Randi Pratama 1121 silver badge9 bronze badges

1 Seems to be your tables are in Public schema and so it works where you select the table with schema name – Jim Macaulay Commented Jul 3, 2020 at 7:01
@JimMacaulay I see, so any idea why it only returns one row. I mean I get it if it returns none, but why does it only returns one? – Randi Pratama Commented Jul 3, 2020 at 7:04
1 Perhaps the problem is in the sequelize code. A good idea to add it to the question. – Bjarni Ragnarsson Commented Jul 3, 2020 at 7:31
@BjarniRagnarsson Nice call, I've updated it. Please have a look. – Randi Pratama Commented Jul 3, 2020 at 8:02
1 Did you try to set a breakpoint at return resolve(result); and look what is result contents? – Anatoly Commented Jul 3, 2020 at 17:04

| Show 1 more ment

2 Answers 2

Sorted by: Reset to default 3

I suppose you need to indicate a query type and remove plain: true option like this:

const result = await sequelize.query(
      `SELECT table2.student_id,
              s.canvasser_name,
              l.level_name,
              table2.total_score
       FROM (SELECT table1.student_id,
                    sum(table1.max_score) total_score
             FROM (SELECT sq.student_id,
                   max(sq.score) max_score
                   FROM public.student_quiz sq
                   GROUP BY sq.quiz_id, sq.student_id) table1
             GROUP BY table1.student_id) table2
       INNER JOIN public.student s
               ON s.id = table2.student_id
       INNER JOIN public.level l
               ON l.id = s.level_id
       ORDER BY table2.total_score DESC
       LIMIT 10;`,
      { 
        type: Sequelize.QueryTypes.SELECT
      }
    );

From Sequelize documentation:

options.plain - Sets the query type to SELECT and return a single row

For those still having issues with this, what worked for me was to use

    type: Sequelize.QueryTypes.RAW

instead of

     type: Sequelize.QueryTypes.SELECT

Full query:


const result = await sequelize.query(
      `SELECT table2.student_id,
              s.canvasser_name,
              l.level_name,
              table2.total_score
       FROM (SELECT table1.student_id,
                    sum(table1.max_score) total_score
             FROM (SELECT sq.student_id,
                   max(sq.score) max_score
                   FROM public.student_quiz sq
                   GROUP BY sq.quiz_id, sq.student_id) table1
             GROUP BY table1.student_id) table2
       INNER JOIN public.student s
               ON s.id = table2.student_id
       INNER JOIN public.level l
               ON l.id = s.level_id
       ORDER BY table2.total_score DESC
       LIMIT 10;`,
      { 
        type: Sequelize.QueryTypes.RAW
      }
    );

发布者：admin，转转请注明出处：http://www.yc00.com/questions/1745047698a4608196.html

javascript - Sequelize query SELECT * FROM Table only returning one row - Stack Overflow

2 Answers 2

发表回复

评论列表（0条）

联系我们

400-800-8888

javascript - Sequelize query SELECT * FROM Table only returning one row - Stack Overflow

2 Answers 2

相关推荐