CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordassociationshas-many
ScottOBot
ScottOBot

Reputation: 849

Ruby on Rails - Querying Active Record Model With has_many Association

I am creating a RESTful API endpoint to return a given user a set of locations that are both public and private. I have a model named Location that has an owner_id and a location_type field. The owner_id links the Location to a user that owns it, and the location_type defines the type of location which can be either a contact, business or brand. Locations of type business or brand are visible to all users, while contacts are only visible to the users that own them. Currently I use the following code to retrieve all locations for a given user:

@locations = Location.where("location_type IN ('business', 'brand') OR
                 (location_type = 'contact' AND owner_id = ?)", params[:id])
                  .order(created_at: :desc)

This code performs as expected returning all public locations (business or brand), and all private locations for that user (contacts owned by that user).

The problem arises when I removed the owner_id from the Location model and created a separate model, LocationOwner. LocationOwner belongs_to Location and is connected through a has_many association. This was done so that each location of type contact could have additional owners added to it when a contact was shared with another user.

How can I modify the above code to query for all locations of type business and brand, and all locations of type contact that have a LocationOwner that is equal to the user id provided by the params[:id]? Is this possible to do in one query? Am I going to have to use two queries as I am now querying two different tables, or does the has_many association take care of it?

Upvotes: 0

Views: 81

Answers (1)

Jake Shorty
Jake Shorty

Reputation: 727

You can join the tables on the LocationOwner's foreign key to the Location (assuming that table has a location_id or equivalent). In this case, since business- or brand-type locations won't have corresponding rows in the LocationOwner table that point to them, you can use a left outer join that ensures all locations remain included. Try this:

Location.joins("LEFT OUTER JOIN location_owners ON locations.id = location_owners.location_id")
        .where("location_type IN ('business', 'brand') OR (location_owners.id = ?)", params[:id])
        .order(created_at: :desc)

Upvotes: 3

Related Questions