Postgres: what's wrong with the syntax of this query?

2
votes

I'm trying to write a fairly straightforward PSQL query to retrieve some data (I realise it's not the most efficient query right now):

SELECT c.name AS article, c.id AS article_id, t.name AS template, t.id AS template_id, brand_names, COUNT(p.component_id)
FROM publications p
INNER JOIN components c 
(SELECT string_agg(b.name, ', ') AS brand_names 
 FROM brands b
 INNER JOIN brands_components 
 ON b.id = brands_components.brand_id
 WHERE brands_components.component_id = c.id
) brand_query
ON c.id = p.component_id 
INNER JOIN brands_components bc
ON c.id = bc.component_id
AND bc.brand_id IN (16, 23, 24, 35, 37)
INNER JOIN components_templates ct
ON c.id = ct.component_id
INNER JOIN templates t
ON t.id = ct.template_id

This gives me a syntax error though on line 4. What's missing? If I run the subquery alone it works fine:

syntax error at or near "SELECT" LINE 4: (SELECT string_agg(b.name, ', ') AS brand_names ^ : SELECT c.name AS article, c.id AS article_id, t.name AS template, t.id AS template_id, brand_nam

The subquery is designed to retrieve all the brand names per component and display them in a single row instead of many. Their join table is brands_components.

A fiddle that is available here, the desired result should be something like:

article         article_id   template       template_id   count   brands
--------------------------------------------------------------------------------------------------------------
component one | 1          | template one | 1           | 4     | brand one, brand two, brand three, brand four
3
sqlpostgresql
Oh magic crystal ball, reveal to us this mysterious "error" OP speaks of - Phil
Ha, sorry, updated. - t56k
So what usually comes after JOIN something alias...? Typically it's the keyword "ON" - Phil
What are you attempting to do with that in-line select right after your first INNER JOIN? That's your syntax error. - rotarydial
Use a lateral join - a_horse_with_no_name

3 Answers

4
votes

Your immediate problem could be solved with a a lateral join:

SELECT c.name AS article, c.id AS article_id, t.name AS template, t.id AS template_id, brand_names, COUNT(p.component_id)
FROM publications p
  JOIN components c ON c.id = p.component_id 
  JOIN brands_components bc ON c.id = bc.component_id AND bc.brand_id IN (1, 2, 3, 4)
  JOIN LATERAL (
    SELECT b.id, string_agg(b.name, ', ') AS brand_names 
    FROM brands b
      JOIN brands_components ON b.id = brands_components.brand_id
    WHERE brands_components.component_id = c.id
    GROUP BY b.id
  ) brand_query ON brand_query.id = bc.brand_id
  JOIN components_templates ct ON c.id = ct.component_id
  JOIN templates t ON t.id = ct.template_id
GROUP BY 1,2,3,4

The above would still not run because the group by doesn't include the brand_names column. Postgres doesn't know that brand_names is already aggregates.

However, the derived table is not really needed if you move the aggregation to the outer query:

SELECT c.name AS article, 
       c.id AS article_id, 
       t.name AS template, 
       t.id AS template_id, 
       string_agg(b.name, ',') as brand_names, 
       COUNT(p.component_id)
FROM publications p
  JOIN components c ON c.id = p.component_id 
  JOIN brands_components bc ON c.id = bc.component_id AND bc.brand_id IN (1, 2, 3, 4)
  JOIN brands b on b.id = bc.brand_id
  JOIN components_templates ct ON c.id = ct.component_id
  JOIN templates t ON t.id = ct.template_id
GROUP BY c.name, c.id, t.name, t.id;
3
votes

Try this Query:

SELECT c.name AS article, c.id AS article_id, t.name AS template, t.id AS template_id,MAX(brand_names) AS brand_names, COUNT(p.component_id) AS Counts
FROM publications p
INNER JOIN components c 
ON c.id = p.component_id 
INNER JOIN brands_components bc
ON c.id = bc.component_id
AND bc.brand_id IN (1, 2, 3, 4)
INNER JOIN components_templates ct
ON c.id = ct.component_id
INNER JOIN templates t
ON t.id = ct.template_id
INNER JOIN (SELECT string_agg(b.name, ', ') AS brand_names 
 FROM brands b
 INNER JOIN brands_components bcc
 ON b.id = bcc.brand_id
 INNER JOIN components c ON bcc.component_id = c.id
) brand_query ON brand_names IS NOT NULL
Group by c.name,c.id,t.name,t.id
1
votes

I got it working with a function:

CREATE FUNCTION brands(int) RETURNS varchar AS $$
  SELECT string_agg(b.name, ', ') AS brand_names 
  FROM brands b
  INNER JOIN brands_components 
  ON b.id = brands_components.brand_id
  WHERE brands_components.component_id = $1
$$ LANGUAGE SQL;

SELECT c.name, c.id, t.name AS template_name, t.id AS template_id, brands(c.id), COUNT(p.component_id)
FROM publications p
INNER JOIN components c 
ON c.id = p.component_id 
INNER JOIN brands_components bc
ON c.id = bc.component_id
AND bc.brand_id IN (1, 2, 3, 4)
INNER JOIN components_templates ct
ON c.id = ct.component_id
INNER JOIN templates t
ON t.id = ct.template_id
GROUP BY 1, 2, 3, 4

Not sure which is preferable, likely it's DineshDB's though.