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

Question

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

Oh magic crystal ball, reveal to us this mysterious "error" OP speaks of — Phil
– Phil, Commented Jan 29, 2018 at 5:32
So what usually comes after JOIN something alias...? Typically it's the keyword "ON" — Phil
– Phil, Commented Jan 29, 2018 at 5:33
What are you attempting to do with that in-line select right after your first INNER JOIN? That's your syntax error. — rotarydial
– rotarydial, Commented Jan 29, 2018 at 5:35

score 4 · Accepted Answer · 2018-01-29 07:08:16Z

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;

That's miles more efficient and makes a lot more sense. Thank-you!
@CD-RUM: actually the intermediate step that left out the brands table in the sub-select was wrong ;)
Ha, gotcha. This seems to get a lot of duplicates in the brand_name results when it should be unique. Any way to address that too? Not that you haven't helped enough already...
@CD-RUM: you could simply use string_agg(distinct b.name, ',')
@CD-RUM: the "duplicates" stem from the joins as you get each brand and brand_component multiple times. The idea with the derived table to eliminate those duplicates wasn't that bad to begin with - for large tables that might actually be more efficient - however it's not that easy to integrate because of the different levels of aggregation that you need there

DineshDB · Accepted Answer · 2018-01-29 06:42:23Z

3

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

edited Jan 29, 2018 at 6:42

answered Jan 29, 2018 at 5:49

DineshDB

6,2238 gold badges38 silver badges54 bronze badges

2 Comments

t56k Over a year ago

That returns this error:

invalid reference to FROM-clause entry for table "c" LINE 12: WHERE brands_components.component_id = c.id ^ HINT: There is an entry for table "c", b

t56k Over a year ago

That works! I also got it working myself, but I'll accept yours and post mine. I'm not sure what's more efficient though.

t56k · Accepted Answer · 2018-01-29 06:49:01Z

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.

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Related