CODEWITHSUNDEEP
mysqlnode.jssequelize.js
oniramarf
oniramarf

Reputation: 903

Sequelize findAndCountAll rows not in associations

There are two tables: Books, listing available books, and BorrowedBooks, listing books that are currently borrowed. I want to retrieve only books that are currently available (not borrowed), providing pagination and total number count.

I would use the method findAndCountAll provided by Sequelize, which is really easy to use and does most of the works, but it does not work properly. I have tried the following piece of code, using the findAll method, which works correctly.

Books.findAll({
    where: { '$BorrowedBooks.bookId$': null },
    include: [BorrowedBooks]
}).then(...).catch(...);

Then I changed the method to findAndCountAll, in order to obtain the total count of elements and also providing pagination.

Books.findAndCountAll({
    where: { '$BorrowedBooks.bookId$': null },
    include: [BorrowedBooks],
    offset: offset,
    limit: limit,
    distinct: true
}).then(...).catch(...);

This version produces an error Unknown column 'BorrowedBooks.bookId' in 'where clause'.

EDIT

The generated query with the piece of code that does not work is the following:

SELECT 
    `books`.*,
    `borrowedBooks`.`bookId` AS `borrowedBooks.bookId`,
    `borrowedBooks`.`userId` AS `borrowedBooks.userId`,
FROM
    (SELECT 
        `books`.`id`,
        `books`.`title`,
        `books`.`author`,
        `books`.`isbn`
    FROM
        `books` AS books`
    WHERE
        (SELECT 
                `bookId`
            FROM
                `borrowedBooks`
            WHERE
                (`borrowedBooks`.`bookId` = `books`.`id`
                    AND `borrowedBooks`.`bookId` IS NULL)
            LIMIT 1) IS NOT NULL
    LIMIT 0 , 10) AS `books`
        INNER JOIN
    `borrowedBooks` ON `books`.`id` = `borrowedBooks`.`bookId`
        AND `borrowedBooks`.`bookId` IS NULL;

If I would write down directly the query I would do something like this:

SELECT * FROM `books`
LEFT OUTER JOIN `borrowedBooks` ON `books`.`id` = `borrowedBooks`.`bookId`
WHERE `borrowedBooks`.`bookId` IS NULL

I think that the error is due to the INNER JOIN that Sequelize uses.

Upvotes: 1

Views: 14614

Answers (2)

Thuvarahan Sivakumar
Thuvarahan Sivakumar

Reputation: 433

Node pagination with sequelize
client needs to provide page and pageSize
Pass distinct: true into the query and it will work how you want it to. you will have included data as well.
const page = 1 const pageSize = 2 const offset = page * pageSize const limit = offset + pageSize return model .findAll({ attributes: [], include: [{}], distinct: true, limit, offset }) .then((tasks) => res.status(200).send(tasks)) .catch((error) => { console.log(error); res.status(400).send(error); });

Upvotes: 1

Ashh
Ashh

Reputation: 46481

Correct syntax to use include

Books.findAll({
    include: [{
      model: BorrowedBooks,
      where: {
        bookId: null
      }
    }],
    offset: offset,
    limit: limit,
    distinct: true
}).then(...).catch(...);

Upvotes: 4

Related Questions