Rails .where() query not working

Question

Thanks a lot for your help. I have a locations and ads table. Location has_many :ads I perform the following query on Location Model.

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

Then I want to perform an additional query on @locations and filter it based on :ads

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

This query does not work. It returns an empty object.

=> #<Location::ActiveRecord_Relation:0x1e29270>

This are the parameters:

1. location_params

   @location_params.require(:location).permit(:id) 
   <ActionController::Parameters {"id"=>1} permitted: true>
   

2. ads_params

   @ads_params.require(:ads).permit(:id)
   <ActionController::Parameters {"id"=>1} permitted: true>
   

It is not permitted to give a specific column input like this:

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

I have postgresql, I did not think about using SQL, but I am not sure.

Query Parameters

I am using rails console to do this query, for testing.

@location_params 
=> <ActionController::Parameters {"location"=><ActionController::Parameters {"id"=>1} permitted: false>} permitted: false>


@location_params.require(:location).permit(:id)
=> <ActionController::Parameters {"id"=>1} permitted: true>

@ads_params
=> <ActionController::Parameters {"ads"=><ActionController::Parameters {"id"=>1} permitted: false>} permitted: false>

@ads_params.require(:ads).permit(:id)
=> <ActionController::Parameters {"id"=>1} permitted: true>

Benzine

Can you write the whole params hash that you see in the server log here – Horacehoracio

simply add binding.pry, then in the console run @locations.joins(:ads).where(ads: ads_params).first and see the invalid PG statement error, which you have to fix. – Libertine

@AndreyDeineko @RSB I updated my post for your request. I am testing in the console, I run the code with .first and I receive the TypeError: cant't quote ActionController::Parameters – Benzine

Exactly, you need to convert whitelisted params to hash, edited answer. – Horacehoracio

I wrote about the way you're passing the params into query, but was not sure. it was Location.joins(:ads).where(ads: ads_params.permit!.to_h – Libertine

@AndreyDeineko Did you delete your comment? It may be working what you wrote. Just let me know. As it may be working I am close to accepting the correct answer. If RSB query is fixed I will accept his answer in the other older post, as he already helped me with this query. Thanks a lot to everyone – Benzine

Andrey - He has already whitelisted params in that method – Horacehoracio

@RSB yes, but it was still not a hash object, which AR query is expecting (which you successfully incorporated into your answer after my comment :)) – Libertine

@Andrey to_h is needed as you said, but I think .permit! is not needed again. Thanks for your suggestion, you can add your answer. – Horacehoracio

@RSB you're right, params has permitted: true, thus permit! is redundant. There is no sense in adding an answer which will repeat the one already given :) – Libertine

@RSB Ok, I test this RSB and if you 2 agree and if it work I will accept your answers. Still I have seen Andrey comment telling me to use the .to_h method (also with .permit!). I tested that it worked then he deleted the comment, maybe you 2 found the solution almost at the same time. – Benzine

Andrey was the one to suggest to_h after your comment, I was a bit late to add saw your comment later on binding.pry result. This is a Rails 5 specific problem though #34950005 – Horacehoracio

Guys, the SO prime goal is to help, since the answer given works, I say accept it, we're glad we helped :) P.S. Always add the environment setup information (Ruby version, Rails version etc :)) – Libertine

Thanks a lot to both Andrey and RSB. I am accepting your answer RSB. – Benzine

Answer 1

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!

Horacehoracio