Skip to content

Instantly share code, notes, and snippets.

@simme

simme/sqlite question.md

Last active January 5, 2022 19:52
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save simme/6dc40af6620d5f38ad8eb1cbb98d7eb6 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save simme/6dc40af6620d5f38ad8eb1cbb98d7eb6 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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.

@simme
Copy link
Author

simme commented Jan 5, 2022 •
edited
Loading

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;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment