I need to combine the results of the following two queries in one query.
Query #1:
SELECT
sets.set_num, sets.name AS set_name, sets.year, sets.theme_id,
sets.num_parts, themes.name AS theme_name
FROM
sets
INNER JOIN
themes ON sets.theme_id = themes.id
WHERE
(sets.year IS NULL OR sets.year LIKE '%' || :set_year || '%')
AND (sets.name IS NULL OR sets.name LIKE '%' || :set_name || '%')
AND (themes.name IS NULL OR themes.name LIKE '%' || :theme_name || '%')
ORDER BY
set_num
LIMIT :limit OFFSET :offset
Query #2:
SELECT
COUNT(a.set_num) AS count, a.year AS key
FROM
(SELECT sets.set_num, sets.year
FROM sets
INNER JOIN themes ON sets.theme_id = themes.id
WHERE (sets.year IS NULL OR sets.year LIKE '%' || :set_year || '%')
AND (sets.name IS NULL OR sets.name LIKE '%' || :set_name || '%')
AND (themes.name IS NULL OR themes.name LIKE '%' || :theme_name || '%')
LIMIT :limit OFFSET :offset) AS a
GROUP BY
a.year
Is it possible to make these two queries into one since they both have the same joins and conditions?
I want to end up with the same result but with executing one query:
{
"sets": [
{
"num": "21162-1",
"name": "The Taiga Adventure",
"year": 2020,
"themeId": 577,
"themeName": "Minecraft",
"numParts": 74
}
],
"setCountByYear": [
{
"key": "2020",
"count": 1
}
]
}
Schema of the sets table:
CREATE TABLE sets
(
set_num varchar(16) PRIMARY KEY,
name varchar(128),
year smallint,
theme_id smallint,
num_parts int,
FOREIGN KEY(theme_id) REFERENCES themes(id)
);
Schema of the themes table:
CREATE TABLE themes
(
id smallint PRIMARY KEY,
name varchar(64),
parent_id smallint
);
Sample data for sets table:
| set_num | name | year | theme_id | num_parts |
|---|---|---|---|---|
| 001-1 | Gears | 1965 | 1 | 43 |
| 0011-2 | Town Mini-Figures | 1978 | 84 | 12 |
| 0011-3 | Castle 2 for 1 Bonus Offer | 1987 | 199 | 0 |
| 0012-1 | Space Mini-Figures | 1979 | 143 | 12 |
| 0013-1 | Space Mini-Figures | 1979 | 143 | 12 |
Sample data for themes:
| id | name | parent_id |
|---|---|---|
| 1 | Technic | |
| 2 | Arctic Technic | 1 |
| 3 | Competition | 1 |
| 4 | Expert Builder | 1 |
| 5 | Model | 1 |
5
If you just wanted an extra column on your data, then you could use a window function:
COUNT(*) OVER (PARTITION BY s.year)
But what you are asking for is really two separate resultsets. The best you can do is use a CTE and combine them using JSON.
It’s unclear why you expect those columns to be null, or why you would want to use LIKE on numeric columns.
WITH cte AS (
SELECT
s.set_num,
s.name AS set_name,
s.year,
s.theme_id,
s.num_parts,
t.name AS theme_name
FROM
sets s
INNER JOIN
themes t ON s.theme_id = t.id
WHERE
s.year = :set_year
AND s.name LIKE '%' || :set_name || '%'
AND t.name LIKE '%' || :theme_name || '%'
ORDER BY
set_num
LIMIT :limit OFFSET :offset
)
SELECT
json_object(
'sets', (
SELECT json_group_array(json_object(
'set_num', set_num,
'set_name', set_name,
'year', year,
'theme_id', theme_id,
'num_parts', num_parts,
'theme_name', theme_name
))
FROM cte
),
'setCountByYear', (
SELECT json_group_array(json(eachYear))
FROM (
SELECT
json_object(
'key', year,
'count', COUNT(*)
) AS eachYear
FROM cte
GROUP BY
year
) j1
)
) AS json;
db<>fiddle
12