SQL: filtering on one JOIN while sorting on another

Question

I have a Product model that has many Skus, and each product can also have a Customization that includes being featured. Schema looks like this:

Product
| id | category_id | title | image |

Sku
| id | product_id | subtitle | price |

Customization
| id | product_id | title | featured |

I'm filtering/sorting the Product based on its category_id and the prices of its Skus like so:

SELECT id, title, image, prices
  FROM (
    SELECT p.id, p.title, p.image, array_agg(s.price) as prices
    FROM "Products" p
    JOIN "Skus" s
    ON p.id = s.product_id
    WHERE p.category_id in (1,2,3,4) AND s.price > 99
    GROUP BY p.id
  ) x
ORDER BY id DESC;

I'd like to add an additional filter for Products whose Customizations have featured = true.

Is it possible to include another JOIN to do this, or is there a better approach?

Gordon Linoff · Accepted Answer · 2015-11-03 02:23:38Z

2

First, you don't need the subquery. Second you can do this with a JOIN (or IN or EXISTS):

SELECT p.id, p.title, p.image, array_agg(s.price) as prices
FROM "Products" p JOIN
     "Skus" s
      ON p.id = s.product_id JOIN
      Customization c
      ON p.id = c.product_id AND c.featured = true
WHERE p.category_id in (1, 2, 3, 4) AND s.price > 99
GROUP BY p.id
ORDER BY p.id DESC

edited Nov 3, 2015 at 2:23

answered Nov 2, 2015 at 23:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Tyler Over a year ago

Thanks, this works great and improves my original query as well!

Collectives™ on Stack Overflow

SQL: filtering on one JOIN while sorting on another

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related