Rails .joins() between tables and .where() condition

Question

Summary

Thanks a lot for your help. I have a locations and ads table. Location has_many :ads

I would like to make a query of Location Model with a join with Ad Model, to filter entries on parameters of Location and Ad.

@locations = Location.joins(:ads).where(locations: location_params, ads: location_params[:ads_attributes])

This is the location_params method (empty fields will be deleted with another method that is working fine).

params.require(:location).permit(:country, {:ads_attributes => [:remote, :days]})

This is an example of my query. I have a method which remove empty fields from location_params. It works fine.

SELECT "locations".* FROM "locations" INNER JOIN "ads" 
ON "ads"."location_id" = "locations"."id" 
WHERE "ads_attributes"."remote" = $1  [["remote", "1"]]

In this case location_params has the following fields:

<ActionController::Parameters {"ads_attributes"=>
<ActionController::Parameters {"remote"=>"1"} permitted: true>} permitted: true>

This is the result, an empty object even if I have entries with those parameters in my table

#<Location::ActiveRecord_Relation:0x3fb83def8190>

Update

1. First Issue - Solved from Péter Tóth

Solution. Using .includes(:ads) for not re-executing the query with @locations[0].ads.

@locations = Location.joins(:ads).where(locations: {id: 1}, ads: {id: 1})

The problem is, when i select from location the ads, it will perform again the query and delete the previous filter ads.id = 1.

@locations[0].ads

The result is not only ad with id=1 will be selected but all ads of that @location[0] will be selected.

1. Second Issue

I am able to execute the query:

@locations = Location.joins(:ads).where(locations: {id: 1}, ads: {id: 1})

Or

@locations = Location.joins(:ads).where(location_params)

But not

@locations = Location.joins(:ads).where(locations: location_params, ads: ads_params)

But this can be solved as follows: Perform a first query on Location

@locations = Location.joins(:ads).where(@location_params.require(:location).permit(:id)).includes(:ads)

which returns all locations with those parameters, then I need to filter @locations based on the ads filters. The problem is that I am not able to do the following query.

@locations = @locations.joins(:ads).where(ads: @ads_params.require(:ads).permit(:id)).includes(:ads)

Answer 1

You need to rewrite the query since ads_attributes is not a table, Try this

ads_params = @ads_params.require(:ads).permit(:id).to_h
location_params = @location_params.require(:location).permit(:id).to_h
@locations = Location.joins(:ads).where(locations: location_params, ads: ads_params)

Hope that helps!

Answer 2

@locations[0].ads will fetch all the ads for the first location object no matter what. Of course it will only perform the fetch process if they have been not fetched yet. One solution is to side load the ads:

@locations = Location.joins(:ads).where(locations: {id: 1}, ads: {id: 1}).includes(:ads)
@locations[0].ads

This way you can avoid the N + 1 queries problem. But you should use it careful since the @locations[0].ads.reload will load all the ads no matter what filter you used earlier.

A tip: It depends on what your purpose is, if you need only ads according some criteria, then I would suggest to start with Ad.join(:location).where(...).includes(:location)...

Update

This is working for me if I send GET /locations?location[country]=IT&ad[remote]=1:

class LocationsController < ApplicationController
  def index
    @locations = Location.joins(:ads).where(locations: location_filters, ads: ad_filters).includes(:ads)
  end

  private

  def location_filters
    params.require(:location).permit(:country)
  end

  def ad_filters
    params.require(:ad).permit(:remote, :days)
  end
end

Or if you don't have all the prams available in each case, then you might want to build a query:

class LocationsController < ApplicationController
  def index
    @locations = Location.joins(:ads).includes(:ads)
    @locations = @locations.where(locations: location_filters) if location_filters.present?
    @locations = @locations.where(ads: ad_filters) if ad_filters.present?
  end

  private

  def location_filters
    params.fetch(:location, {}).permit(:country)
  end

  def ad_filters
    params.fetch(:ad, {}).permit(:remote, :days)
  end
end