Find a cheaper SQL for a complicated Rails query

Question

A Country has_many residences and a Residence has_many listed_amenities which are linked to amenities. I need to know how many residences which are located in the Country with the id 1 have the amenities with the ids [48, 49, 50] (they can have more but they have to have all three of them).

The following Ruby on Rails (v4.2.4) code does the trick:

id_list = [48, 49, 50]
Country.first.
        residences.
        where(id: Residence.
        joins(:listed_amenities).
        where(listed_amenities: {amenity_id: id_list}).
        group('residences.id').
        having("count(listed_amenities.*) = ?", id_list.size).
        pluck(:id)).
        count

It results in the following SQL:

SELECT  "countries".* 
FROM "countries"  
ORDER BY "countries"."id" ASC LIMIT 1

SELECT "residences"."id" 
FROM "residences" 
INNER JOIN "listed_amenities" 
ON "listed_amenities"."residence_id" = "residences"."id" 
WHERE "listed_amenities"."amenity_id" IN (48, 49, 50) 
GROUP BY residences.id 
HAVING count(listed_amenities.*) = 3

SELECT COUNT(*) 
FROM "residences" 
WHERE "residences"."country_id" = $1 
AND "residences"."id" 
IN (51, 59, 60, ...)  [["country_id", 1]]

Obviously that is an expensive way to solve the given problem and each table has more than 1,000,000 entries.

How can I solve this on a PostgreSQL (9.4.4) in a better/faster way? Is there one magic SQL line which does the trick?

Thanks to "Get the count of rows count after GROUP BY" I have the following SQL query for doing the search over all residences but I lack the SQL know-how to connect the Country to it:

SELECT count(*) AS ct
FROM  (
   SELECT 1
   FROM   listed_amenities
   WHERE  amenity_id IN (48, 49, 50)
   GROUP  BY residence_id 
   HAVING count(*) = 3
   ) sub;

The Ruby on Rails setup:

rails g scaffold Country name
rails g scaffold Residence country:references name:string
rails g scaffold Amenity name:string
rails g scaffold ListedAmenity residence:references amenity:references

app/models/country.rb

class Country < ActiveRecord::Base
  has_many :residences
end

app/models/residences.rb

class Residence < ActiveRecord::Base
  belongs_to :country

  has_many :listed_amenities
  has_many :amenities, through: :listed_amenities
end

app/models/amenities.rb

class Residence < ActiveRecord::Base
  belongs_to :country

  has_many :listed_amenities
  has_many :amenities, through: :listed_amenities
end

app/models/listed_amenities.rb

class ListedAmenity < ActiveRecord::Base
  belongs_to :residence
  belongs_to :amenity
end

Your code results in 3 queries? – Jakub Kania Sep 22 '15 at 07:32 — Jakub Kania, Sep 22 '15 at 07:32

David Aldridge · Accepted Answer · 2015-09-22T08:34:24.473

1

I think you could probably simplify this to:

Residence.
    joins(:listed_amenities, :country).
    where(listed_amenities: {amenity_id: id_list}).
    where(countries: {id: 1}).
    group('residences.id').
    having("count(listed_amenities.*) = ?", id_list.size)

For the count, try adding:

    count.count

edited Sep 22 '15 at 08:34

answered Sep 22 '15 at 07:52

David Aldridge

51,479
8
68
96

That results in the list of `residences` but I need the count. Adding the `.count` method to it doesn't work. – wintermeyer Sep 22 '15 at 08:12
I'm going to need a hint as to the error message ;) – David Aldridge Sep 22 '15 at 08:29
`... 529042=>3, 529050=>3, 529062=>3, 529064=>3, 529067=>3, 529070=>3, 529071=>3, 529077=>3, 529082=>3, 530547=>3}` – wintermeyer Sep 22 '15 at 08:30

Find a cheaper SQL for a complicated Rails query

The Ruby on Rails setup:

1 Answers1