CODEWITHSUNDEEP
mysqlruby-on-railspostgresqljoinactiverecord
Jake
Jake

Reputation: 407

Complex MySql joins - rails

So, I have the models User, Submission, and Download. A User can have many submissions, and a Submission has many downloads. A download tracks the IP address etc, and is structured as time series data. The problem is, I need to be able to do a query like finding the user with the most amount of downloads in the past week. I'm really not sure where to start with this, and any help would be appreciated.

The main parts of the tables include the User which has an id, Submission which has a user_id and id, and Download which has submission_id and id.

So basically, I need to select all the downloads for submissions that are owned by a specific user, count them up, and then sort them to find the user that has the most downloads. Is this really possible, or efficient? Should I just add a say owner_id to the Download model, and then query based on that?

Thanks for any help.

Upvotes: 0

Views: 54

Answers (5)

Woody008
Woody008

Reputation: 96

Sorry I dont have 50 reputation so I cant add a comment on you raw sql answer but you can try this to write in rails query this:

User.all.joins(:submission => :downloads).group(:user_id).order("count(user_id) DESC")

So get all user, joins submission table by user_id and get its downloads association table as well, then group by user_id...etc

Give that a try?

Upvotes: 1

Thomas E
Thomas E

Reputation: 3838

Try:

Submission.joins(:user,:downloads).
group('submissions.id','downloads.submission_id').
order('count_all DESC').
limit(10).
count

You'll figure it out.

There are other joins you might wanna try out, but this is an easy starting point.

Upvotes: 1

Woody008
Woody008

Reputation: 96

Why don' you try this Download.all.group(:user_id).order("count(id) DESC" )

So you get all the Downloads, group them by user_id and count all the downloads for that user and order by DESC.

I think that's what you want?

*by the way don't forget to add the user_id column.

Upvotes: 1

Jake
Jake

Reputation: 407

So, I ended up solving the issue with a raw sql query, and this is what I came up with:

SELECT 
    users.id, 
    COUNT(*) AS subdownload_count
FROM users
    INNER JOIN submissions ON users.id = submissions.creator_id
    INNER JOIN downloads ON submissions.id = downloads.submission_id
GROUP BY 
    users.id
ORDER BY
    subdownload_count DESC

I've been using nosql a lot the past year, so I don't completely understand joins in rails, but I'll probably figure out how to implement this soon. Thanks to anyone who commented!

Upvotes: 0

jcuenod
jcuenod

Reputation: 58405

I'm fairly new to Rails but I think you should use

has_many :downloads, :through => :submission

on your user model. Then I think you can do something like User.downloads.count

Upvotes: 1

Related Questions