Ben Ben - 16 days ago 10
SQL Question

How to build an efficient select command across multiples tables in Rails4?

Trying to figure out an efficient ways to select records that have attributes across multiple tables. Here's the basic setup:

structure



  • Plants (fields: id, name_id, location_id, color) (1000 records)

  • Names (fields: id, Common_name) (50 records)

  • Location (fields: id, Bed_name) (125 records)



model



  • Plants - belongs_to Names, belongs_to Location

  • Names - has_many Plants

  • Location - has_many Plants



My goal is to output a list of every Rose in the side yard, and display the color, but I am stuck on the select command. If I get all plants (
p = Plant.all
) I know that I can easily create my output with a statement like
<%= "#{p.name.common_name} in bed #{p.location.bed_name} has a color of #{p.color}" %>

If I do two joins I'm looking at way more records that I need and a MUCH longer search time. As an example - I have 67 roses in 16 different beds, however, I only have 3 roses in the side yard.

My gut tells me that I should be able to do something like:
select all plants with the name of Rose, then from this selection select all Roses that are in the side yard.

Can anybody help point me in the correct direction?

Answer

You can combine it all into a single query like this:

Plant.joins(:name, :location).where(names: { common_name: "rose" }, locations: { bed_name: "side" })

This results in a single SQL query like this:

SELECT "plants".* FROM "plants" INNER JOIN "names" ON "names"."id" = "plants"."name_id" INNER JOIN "locations" ON "locations"."id" = "plants"."location_id" WHERE "names"."common_name" = 'rose' AND "locations"."bed_name" = 'side'

Note that you have to use the plural table names in the where clause, but the singular association name in the joins clause.

This will run nearly instantaneously even with enormous tables, assuming your tables are properly indexed.

This is a simple example, but you can do fairly complex joins with conditions. Full details can be found in the ActiveRecord documentation.

Comments