CODEWITHSUNDEEP
sqlruby-on-railsrails-activerecord
phoenixairs
phoenixairs

Reputation: 82

Query data from two associated tables

For my app:

Perhaps the location's fields should just be part of the image. But regardless, I'm trying to write this query in Rails:

    SELECT image.caption, location.latitude, location.longitude 
    FROM   image, location 
    WHERE  location.image_id = image.id 
    AND    image.user_id = 5

or alternatively, if it's easier:

    SELECT  image.*, location.*
    FROM    image, location
    WHERE   location.image_id = image.id 
    AND     image.user_id = 5

How would I write this as an ActiveRecord query?

Upvotes: 0

Views: 125

Answers (1)

deefour
deefour

Reputation: 35370

I think you want to read about Eager Loading Associations.

@images = Image.includes(:location).where("images.user_id = ?", 5)

This will find Image instances where user_id = 5. It then runs a 2nd query that will JOIN and build the associated Location instance (thats what the .includes(:location) will do for you).

This more closely matches your alternative query, as it does select all columns from images and location tables.

You can build an Array based on this containing a hash with only the keys you're interested in through something like this.

@hash_object = @images.collect { |i| { caption: i.caption, latitude: i.location.latitude, longitude: i.location.longitude } }

If you want to build this with only a single query, you can use .joins(:location) in combination with .includes(:location)

Image.joins(:location).includes(:location).where("images.user_id = ?", 5)

Important: This will omit Image instances who have no assoicated Location. You can modify the joins() a bit to help with this, but the above will have this omission.

If you really want only specific columns to be selected, read up on Selecting Specific Columns though there are warnings for the use of this

If the select method is used, all the returning objects will be read only.

and

Be careful because this also means you’re initializing a model object with only the fields that you’ve selected.

In Rails master (not out in 3.2.11) you can pass multiple columns to .pluck() but this appears to only be restricted to a single table (you wouldn't be able to get the locations table's :latitude and :longitude when plucking from Image). It's good to know about though.

Upvotes: 1

Related Questions