Rails Mysql: How to only include has_many relation object that meet my condition?

Question

I have the following classes:

    class User < ActiveRecord::Base
      has_many :keys
    end

    class Key < ActiveRecord::Base
      belongs_to :room
    end

    class Room < ActiveRecord::Base
      #has column "surface_area"
    end

I want to query all users that have keys with rooms with surface_area of 10, only including the keys with rooms that have a surface area of 10.

I have the following query:

User.joins(keys: :room).where('room.surface_area= ?', 10)

This returns the Users which have keys with room.surface_area of 10, but it includes all keys, so also keys that have room with a different surface area than 10. I only want to return the subset of the keys which have a room with the surface area of 10.

Wrong return value:

Users:{
 keys: [
  {id: 1, surface_area: 11},
  {id: 2, surface_area: 10},
  {id: 3, surface_area: 10}
 ]
}

Correct return value:

  Users:{
     keys: [
      {id: 2, surface_area: 10},
      {id: 3, surface_area: 10}
     ]
    }

json.rabl file for users

collection @users, :root => :users, :object_root => false

child :keys do
  extends "keys"
end

Answer 1

User.joins(keys: :room).where('room.surface_area= ?', 10)

This command queries users and returns users that have keys for rooms with surface area = 10. This is correct. But when you call keys on one of these users, you get all the keys they have. And this is also correct, because this is what has_many :keys association does.

What you need is to define a new instance method in User model, like so:

def keys_for_rooms_with_surface_area(area)
  keys.joins(:room).where('rooms.surface_area = ?', area)
end

If you want has_many :keys to always return keys for rooms with surface area = 10, you can do it like so:

has_many :keys, ->() { joins(:room).where('rooms.surface_area = 10') }

In this case your very first query would look like this:

User.joins(keys: :room)