Sequalize limit by unique values in column

Question

I have the following database structure:

id (int) | user_id (int) | product_id (int) | data (jsonb)

A combination of the id, user_id and product_id make the primary key. So there can be multiple rows with the same product_id and user_id.

The data column has some JSON containing the following { "item": boolean }

The query I need is to select all rows where user_id = x and data-->item = true. This part I can do, but I need to apply a limit. The limit should not restrict the number of rows that are returned, but instead restrict the number of DISTINCT product_ids that are returned. So if I apply a limit of 10 I could have 50 rows returned if each of the unique products have 5 rows belonging to the user_id and and item true.

This is what I have so far but it makes no attempt at this limit. I believe I may need a subquery or GROUPBY but I'm not sure how to achieve this in Sequalize.

return this.myModel.findAll({
  where: {
    user_id: userId,
    'data.item': true,
  },
});

Any guidance will be much appreciated! Thanks

Answer 1

A query to do this involves JOINing a subquery:

SELECT m.*
FROM my_model m
JOIN (
    SELECT DISTINCT product_id FROM model LIMIT 10
) n ON n.product_id = m.product_id
WHERE m.user_id = $1 AND (data->>'item')::boolean IS TRUE;

To my knowledge, Sequelize cannot represent this query structure, although inlining the subquery as a literal may be possible. But it looks like you'll be running at least some raw SQL one way or the other.