Rails MySQL subquery count

Question

I have two models:

Product
Click

I would like to get all products that have more than x clicks.

The tables looks like this:

Products

id

,

Clicks

id | product_id

In ruby, the code might look like products.select {|p| p.clicks.size > x}. How might one write a similar query for mysql?

Thanks!

Are you just asking how to write SQL to perform this query? Or are you asking how to do it in ActiveRecord/Rails land? — acoffman
– acoffman, Commented Jan 28, 2013 at 23:54
In the end, I'll be writing it in Rails ARel. So that would be ideal. — alaup
– alaup, Commented Jan 29, 2013 at 17:45

acoffman · Accepted Answer · 2013-01-28 23:53:48Z

1

To do this efficiently, you may want to look into using a counter cache. ActiveRecord has built in support for this concept, and it will keep you from having to do a subquery.

Some links:

answered Jan 28, 2013 at 23:53

acoffman

7083 silver badges10 bronze badges

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

Comments

Hamlet Hakobyan · Accepted Answer · 2013-01-28 23:48:00Z

0

Try this:

SELECT *
FROM Products
WHERE Id IN
(
    SELECT 
        P.Id
    FROM Products P
       JOIN Clicks C
          ON P.Id = C.Product_ID
    GROUP BY P.ID
    HAVING COUNT(1) > x
) A

or this, if you need only product id

    SELECT 
        P.Id
    FROM Products P
       JOIN Clicks C
          ON P.Id = C.Product_ID
    GROUP BY P.ID
    HAVING COUNT(1) > x

answered Jan 28, 2013 at 23:48

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Collectives™ on Stack Overflow

Rails MySQL subquery count

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related