CODEWITHSUNDEEP
ruby-on-railsactiverecord
danielbker
danielbker

Reputation: 402

Improve query with table joins?

I want to retrieve a list of all Downloads as a result of a particular users content. For example, If I have 2 uploads, I want to filter the contents of the Download table to show only those downloads related to my Uploads.

Users {
    user_id: integer
}

Upload {
    upload_id: integer
    user_id: integer (FK)
}

Download {
    download_id: integer
    upload_id: integer (FK)
}

The downloads table is a log of every public user that has downloaded a file ("upload") on my blog. As you can see, an Upload is linked to a user (user can have many uploads) and a download is linked to an upload (one upload can be downloaded many times).

This is the solution I am using so far (it's really messy and probably very inefficient):

@user = User.find_by_id(3)
# grab a list of the User 3's upload ids, e.g. [3, 6, 2, 34, 45]
@upload_ids = @user.uploads.map { |u| u.upload_id }

# now find all Downloads that have these id numbers in their upload_id
@downloads = Download.where(:upload_id => @upload_ids)

I'm sure there is a way to achieve this with table joins. UPDATE: All associations are explicitly configured.

Upvotes: 0

Views: 36

Answers (1)

Simone Carletti
Simone Carletti

Reputation: 176552

Assuming all the associations are explicitly configured, you can use something like:

Download.joins(:upload).where("uploads.user_id = ?", 3)

You can already improve your query by making some assumptions:

  1. You don't need to fetch the user
  2. Use pluck instead of mapping over the ids

Here's an example of using 2 optimized queries with no JOIN:

@downloads = Download.where(upload_id: Upload.where(user_id: 3).ids)

Upvotes: 1

Related Questions