postgresql : column does not exist on left join-CodePudding

to get the activity of the user

I have a query that left join product and its reviews and grouping them by reviews_product_id.

SELECT product_name, product_description, product_image, count, average_rating, latest_review_date, product_id, user_id
FROM products 
  LEFT JOIN (
    SELECT reviews_product_id, MAX(created_at) AS latest_review_date, COUNT(*), TRUNC(AVG(rating),1) AS average_rating 
    FROM reviews 
    GROUP BY reviews_product_id
  ) reviews on products.product_id = reviews.reviews_product_id

This is the returned rows

Result: -[ RECORD 1 ]------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
product_name        | Nescafe
product_description | nice.
product_image       | image
count               | 6
average_rating      | 3.5
latest_review_date  | 2021-12-30 14:07:11.158849 08
product_id          | 3
user_id             | 166e0efb-ee3f-47ab-b8c9-5c9b74187ec4

-[ RECORD 2 ]------- -----------------------------------------------------------------------------

product_name        | Zesto
product_description | So Refreshing!
product_image       | image
count               | 7
average_rating      | 2.4
latest_review_date  | 2021-12-30 14:07:27.917427 08
product_id          | 2
user_id             | 166e0efb-ee3f-47ab-b8c9-5c9b74187ec4

data is correct but I want also to filter so the returned rows will output the user who made that review

SELECT product_name, product_description, product_image, count, average_rating, latest_review_date, product_id, user_id
FROM products 
  LEFT JOIN (
    SELECT reviews_product_id, MAX(created_at) AS latest_review_date, COUNT(*), TRUNC(AVG(rating),1) AS average_rating 
    FROM reviews 
    GROUP BY reviews_product_id
  ) reviews on products.product_id = reviews.reviews_product_id
WHERE reviews.user_id = '1234';

But it's telling me that

column reviews.user_id does not exist

CodePudding user response：

As mentioned in the comments the subquery does not return user_id. Once you have added that column to the SELECT in subquery, you can add reviews.user_id = '1234' to the on conditions, as so on products.product_id = reviews.reviews_product_id and reviews.user_id = '1234'.

(I know this should have been a comment but I don't have enough reputation to add comments :) Trying to build it up right now.)

CodePudding user response：

I need to read more about postgres, I was using a different join method.

Replacing left join with inner join worked!

SELECT product_name, product_description, product_image, count, average_rating, latest_review_date, product_id, user_id
    FROM products INNER JOIN 
    (SELECT reviews_product_id, MAX(created_at) AS latest_review_date, COUNT(*), TRUNC(AVG(rating),1) AS average_rating FROM reviews WHERE reviews.user_id = '339d1daa-2f66-46b7-b7bd-bdd980039185' GROUP BY reviews_product_id) 
        reviews 
        on products.product_id = reviews.reviews_product_id;