Postgresql @@ query with left join faced error

Question

I using textsearch-controls for fulltext search in postgres. And everything works correct. I created index and used in query. But when I have been needed join another table I faced with error.

> ERROR:  invalid reference to FROM-clause entry for table "p"
  LINE 10:             ON cp.product_id = p.id          
                                          ^
  HINT:  There is an entry for table "p", but it cannot be referenced from this part of the query.

my query

SELECT 
    p.id AS id,
    p.sku As sku,
    cp .category_id,
    ts_rank_cd(to_tsvector('english',name||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')), query) AS rank                        
FROM products p, to_tsquery('Urbanears:*') query
LEFT JOIN category_product cp 
        ON cp.product_id = p.id                                     
WHERE to_tsvector('english',name||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')) @@ query                                                                                   
ORDER BY rank DESC

without left join query with @@ query works correct without problem and without @@ query query with left join works correct. What I missed ? How to use left join with to_tsquery and @@ query ?

when I move left jpin after query I faced with another silent error

query

    SELECT 
p.id AS id,
p.sku As sku,
cp .category_id,
ts_rank_cd(to_tsvector('english',name||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')), query) AS rank
FROM products p, to_tsquery('Urbanears:*') query
WHERE to_tsvector('english',name||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')) @@ query                                       
LEFT JOIN category_product cp 
ON cp.product_id = p.id

ORDER BY rank DESC

error:

> ERROR:  syntax error at or near "LEFT"
  LINE 11:           LEFT JOIN category_product cp 
                     ^

> Time: 0.001s

UPDATE:

SELECT 
b.category_id, a.id, a.rank

FROM (SELECT 
p.id AS id,
ts_rank_cd(to_tsvector('english',name||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')), query) AS rank
FROM products p, to_tsquery('Urbanears:*') query
WHERE to_tsvector('english',name||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')) @@ query           

ORDER BY rank DESC) as a 


LEFT JOIN category_product b on b.product_id=a.id
WHERE b.category_id = 181

I did it when used subquery. Filter by full text search by some word and by category. But it is correct approach ?

jjanes · Accepted Answer · 2020-03-30 17:04:18Z

1

The code

FROM X, Y LEFT JOIN Z ON ...

Is interpreted as

FROM X, (Y LEFT JOIN Z ON ...)

References to X are not valid inside the ON, because ON is only applying to Y and Z.

You shouldn't mix comma joins and explicit joins, make them both explicit like this:

FROM products p JOIN to_tsquery('Urbanears:*') query ON to_tsvector('english',name||' '||coalesce(description,'')||' '||coalesce(sku,'')||' '||coalesce(price,0)||' '||coalesce(category,'')||' '||coalesce(brand,'')) @@ query 
LEFT JOIN category_product cp ON cp.product_id = p.id

answered Mar 30, 2020 at 17:04

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql @@ query with left join faced error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related