sqlite question.md

Given these two tables

lists

id	name	weight
1	Groceries	0
2	Pharmacy	1
3	Misc	2

list_items

id	list	weight
1	1	1
2	1	0
3	1	2
4	2	1
5	2	0
6	1	3

Can I construct a query that returns:

id	name	items	totalNumberOfItems
1	Groceries	2, 1, 3	4
2	Pharmacy	5, 4	2
3	Misc	null	0

Ie. all of the lists — regardless of whether they have items or not — sorted by weight, with the firs three items of each list (also sorted by the item's weight) concatenated and comma separated.

I have this so far:

SELECT 
  lists.id,
  lists.name,
  lists.theme,
  lists.iconName,
  lists.weight,
  items.id itemId,
  items.productId,
  group_concat(
    CASE items.showsPlural
      WHEN TRUE THEN json_extract(p.namePlural, '$.' || REPLACE(items.locale, '-', '_'))
      ELSE json_extract(p.nameSingular, '$.' || REPLACE(items.locale, '-', '_'))
    END
  ) AS products

FROM lists
LEFT JOIN (
  SELECT * FROM list_items WHERE isDeleted = FALSE ORDER BY weight
) items ON items.listId = lists.id
LEFT JOIN products p ON p.id = items.productId
WHERE lists.isDeleted = FALSE
GROUP BY lists.id
ORDER BY lists.weight;

However, as soon as I add a LIMIT to the LEFT JOIN subquery I get no results — so the join isn't capped at 3 and I can't seem to get the total count of items without messing it all up.

This version adds the count, but I assume that if I can get a limit on the join to work that count will be capped at that limit:

SELECT
  lists.id,
  lists.name,
  lists.theme,
  lists.iconName,
  lists.weight,
  items.id itemId,
  items.productId,
  group_concat(
    CASE
      items.showsPlural
      WHEN TRUE THEN json_extract(p.namePlural, '$.' || REPLACE(items.locale, '-', '_'))
      ELSE json_extract(
        p.nameSingular,
        '$.' || REPLACE(items.locale, '-', '_')
      )
    END
  ) AS products,
  count(items.id) as numberOfRemainingItems
FROM
  lists
  LEFT JOIN (
    SELECT
      *
    FROM
      list_items
    WHERE
      isDeleted = FALSE
      AND completedAt IS NULL
    ORDER BY
      weight
  ) items ON items.listId = lists.id
  LEFT JOIN products p ON p.id = items.productId
WHERE
  lists.isDeleted = FALSE
GROUP BY
  lists.id
ORDER BY
  lists.weight;