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)

Commitment

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!

Hammers

Sorry I can not select a specific column with { country: location_params[:country] }. There are several fields and there is a method which delete them if they are empty. If location_params[:country] is blank then it will be deleted and it will be nil. I would like to just input location_params to the query without giving a specific column to be selected. Do you know how to fix the following query so that it will work? @locations = Location.joins(:ads).where(locations: new_params, ads: new_params[:ads_attributes]) Thanks – Commitment

I have edited my answer, this should solve your problem. – Hammers

Thanks RSB, it still does not work. this is location_params <ActionController::Parameters {"country"=>"IT", "location"=>"Leizpig"} permitted: true> while this is ads_params <ActionController::Parameters {"remote"=>"1"} permitted: true> and the query is empty #<Location::ActiveRecord_Relation:0x3f9eeac68e00> – Commitment

I can not figure out how to solve it. I am thinking of doing 2 steps: 1. first I filter everything based on the Location model 2. I filter the result based on the Ad Model. If you have any advice tell me then we edit your answer with the final answer and I will accept. – Commitment

I am thinking maybe the problem is not the query but Association between Location and Ad. I am selecting from Location Model, but Location has_many :ads. Even if i am able to make the query with a join, then when i do location.ads it will loose the filter that i made on Ad – Commitment

Do you want to edit your answer with the content of the other discussion? I think also this question should have you as correct answer. #42761081 – Commitment

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

Andromache

Thanks. This solved this specific issue, where I could not do @locations[0].ads, but my problem now is getting @locations filled. In my case I can not use id or a specific parameter, because the fields get deleted with another method when they are blank. So I would like to use the solution of @RSB ,but when I try the query in my console or in debug it does not work. – Commitment

Sorry, but the problem was that the parameters needed to be converted .to_h – Commitment