I'm using the sequelize count function as follows:

Definition:

const countAllOrdersWhere = async (conditions) =>
    Order.count({
        where: conditions,
    })
        .then((count) => ({ count, error: null }))
        .catch((error) => ({ count: null, error }));

Usage:

  const { count, error: countError } = await countAllOrdersWhere({
                    [Op.or]: [
                        { userId: userIds, status },
                        { status, venueId },
                    ],
                });

This works great, however I now need to add a condition based on an associated model:

1. Each order has many orderItems associated with it (there's an orderId on each row in the orderItem table.)
2. Each orderItem has one item associated with it - (there's an itemid on each row in the orderItem table.)

I have an array of itemIds and I would like to only count the orders which are associated with an orderItem which has an itemId which is in my list.

When querying the orders, I enforce this clause in a findAll function by doing

include: [
 {
 model: OrderItem,   
 where: Sequelize.literal(
      itemIds && itemIds.length > 0
      ? `"orderItems"."itemId" IN (${itemIds})`
      : 'true'
 )}]

however, I'm not sure how to do this in a count function

CodePudding user response：

The count method also has include option so you can use it the ame way as you did with findAll, see count

const countAllOrdersWhere = async (conditions) =>
    Order.count({
        include: [
 {
   model: OrderItem,   
   // this option is important in order to get the correct result (leads to INNER JOIN
  // instead of LEFT OUTER JOIN)
   required: true,
   where: (itemIds && itemIds.length > 0) ? {
     itemId: {
      [Op.in]: itemIds
     }
   } : {}
  }]
  }).then((count) => ({ count, error: null }))
    .catch((error) => ({ count: null, error }));