I have two models: Category and Bookmark. Bookmark is associated with category through categoryId column.
Category also have name
, createdAt
, orderId
columns;
I can get category and all its bookmarks with:
const category = await Category.findOne({ where: { id: req.params.id }, include: [ { model: Bookmark, as: 'bookmarks' }, ] });
But now I want to change how bookmarks are ordered. Order type can be name
, createdAt
or orderId
;
const order = orderType == 'name' ? [[ { model: Bookmark, as: 'bookmarks' }, Sequelize.fn('lower', Sequelize.col('bookmarks.name')), 'ASC' ]] : [[{ model: Bookmark, as: 'bookmarks' }, orderType, 'ASC']] const category = await Category.findOne({ where: { id: req.params.id }, include: [ { model: Bookmark, as: 'bookmarks' }, ], order });
It works fine when orderType is createdAt
or orderId
but fails when it’s name
I’m getting this error: SQLITE_ERROR: near "(": syntax error
and SQL query generated by Sequelize is:
SELECT `Category`.`id`, `Category`.`name`, `bookmarks`.`id` AS `bookmarks.id`, `bookmarks`.`name` AS `bookmarks.name`, `bookmarks`.`categoryId` AS `bookmarks.categoryId`, `bookmarks`.`orderId` AS `bookmarks.orderId`, `bookmarks`.`createdAt` AS `bookmarks.createdAt` FROM `categories` AS `Category` INNER JOIN `bookmarks` AS `bookmarks` ON `Category`.`id` = `bookmarks`.`categoryId` ORDER BY `bookmarks`.lower(`bookmarks`.`name`) ASC;
Advertisement
Answer
Changed it to:
const order = orderType == 'name' ? [[Sequelize.fn('lower', Sequelize.col('bookmarks.name')), 'ASC']] : [[{ model: Bookmark, as: 'bookmarks' }, orderType, 'ASC']];
and now it’s working.