Rails Query Multiple Params From Same Table

Question

How can I search for multiple params? I have checkboxes in my view, so if multiple checkboxes are selected, I would like all the params selected to be chosen. I can currently only get the search to work with one param with code below.

There is a has_many to has_many association between car model and colour_collection model.

Controller:

@cars = car.joins(:colour_collections).where("colour_collections.name = ?", params[:colour_collection])

logs show this if two colours selected (e.g. red and green) creating duplicates in the resulting querie:

(0.7ms)  SELECT COUNT(*) FROM "colour_collections"
  ColourCollection Load (0.5ms)  SELECT "colour_collections".* FROM "colour_collections"
  Car Load (2.5ms)  SELECT "cars".* FROM "cars" INNER JOIN "car_colour_collections" ON "car_colour_collections"."car_id" = "cars"."id" INNER JOIN "colour_collections" ON "colour_collections"."id" = "car_colour_collections"."colour_collection_id" WHERE "colour_collections"."name" IN ('Subtle', 'Intermediate') ORDER BY "cars"."created_at" DESC
  CarAttachment Load (0.5ms)  SELECT  "car_attachments".* FROM "car_attachments" WHERE "car_attachments"."car_id" = $1 ORDER BY "car_attachments"."id" ASC LIMIT $2  [["car_id", 21], ["LIMIT", 1]]
  CACHE (0.0ms)  SELECT  "car_attachments".* FROM "car_attachments" WHERE "car_attachments"."car_id" = $1 ORDER BY "car_attachments"."id" ASC LIMIT $2  [["car_id", 21], ["LIMIT", 1]]
  CarAttachment Load (0.5ms)  SELECT  "car_attachments".* FROM "car_attachments" WHERE "car_attachments"."car_id" = $1 ORDER BY "car_attachments"."id" ASC LIMIT $2  [["car_id", 20], ["LIMIT", 1]]
  CACHE (0.0ms)  SELECT  "car_attachments".* FROM "car_attachments" WHERE "car_attachments"."car_id" = $1 ORDER BY "car_attachments"."id" ASC LIMIT $2  [["car_id", 20], ["LIMIT", 1]]

bitsapien · Accepted Answer · 2018-01-07 05:46:50Z

1

If you want to search for multiple values in a single column for example

params[:colour_collection] = ['red','green','blue']

Then you would expect your query to look like this

SELECT * FROM cars c 
INNER JOIN colour_collections s 
WHERE s.name IN ('red','green','blue');

In this case the corresponding ActiveRecord statement would look like this

Car.
joins(:colour_collections).
where(colour_collections: { name: params[:colour_collection] })

answered Jan 7, 2018 at 5:46

bitsapien

1,84313 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dgreen22 Over a year ago

Exactly what I was looking for. Thanks. Question - I'm currently getting duplicates of each item in views - so if item has both attribute colour 'green' and 'blue' and both coloured are searched in one query, I think it's showing twice because one is returning green results and other is returning blue results.

bitsapien Over a year ago

Could you update your question to share an example of the data table related to the scenario you are talking about ?

dgreen22 Over a year ago

Ok - just added.

bitsapien Over a year ago

I meant an example of the data stored in your table. A couple of rows maybe

bitsapien · Accepted Answer · 2018-01-07 06:49:46Z

1

Rails 5 comes with an or method but Rails 4 does not have the or method, so you can use plain SQL query in Rails 4.

In Rails 4 :

@cars = car.
        joins(:colour_collections).
        where("colour_collections.name = ? or colour_collections.type = ?", params[:colour_collection], params[:type])

In Rails 5 :

@cars = car.
        joins(:colour_collections).
        where("colour_collections.name = ?", params[:colour_collection]).or(car.joins(:colour_collections).where("colour_collections.type = ?", params[:type]))

edited Jan 7, 2018 at 6:49

bitsapien

1,84313 silver badges24 bronze badges

answered Jan 7, 2018 at 5:40

Ajay sharma

411 silver badge9 bronze badges

Comments

RajG · Accepted Answer · 2018-01-07 03:15:11Z

0

Depending on whether you want to use OR or AND. There are multiple ways of achieving this but simple example is

Article.where(trashed: true).where(trashed: false)
the sql generated will be
SELECT * FROM articles WHERE 'trashed' = 1 AND 'trashed' = 0

Foo.where(foo: 'bar').or.where(bar: 'bar') This is norm in Rails 5 or simply
Foo.where('foo= ? OR bar= ?', 'bar', 'bar')

answered Jan 7, 2018 at 3:15

RajG

8262 gold badges15 silver badges26 bronze badges

2 Comments

dgreen22 Over a year ago

Does it matter if it's searching the same table/column twice. So instead of Foo.where('foo= ? OR bar= ?', 'bar', 'bar'), it would be Foo.where('foo= ? OR foo= ?', 'bar', 'bar'). I've tried this using params[:colour_collection] as the params ('bar') and I get an error message with Invalid Statement. When I try #{params[:colour_collection]}, it finally works, but only searches one item still. Any thoughts?

dgreen22 Over a year ago

So someone can essentially search for 2 colours by checking 2 boxes.

Josh Brody · Accepted Answer · 2018-01-07 04:15:17Z

0

@cars = car.joins(:colour_collections).where("colour_collections.name = ?", params[:colour_collection]).where("cars.make = ?", params[:make])

More discussion on chaining How does Rails ActiveRecord chain "where" clauses without multiple queries?

edited Jan 7, 2018 at 4:15

answered Jan 7, 2018 at 3:08

Josh Brody

5,3911 gold badge16 silver badges26 bronze badges

1 Comment

dgreen22 Over a year ago

Does it matter if it's searching same table/column, meaning in your example, there's colour_collection and make - I'm looking for if someone chooses 2 colours. I tried this but nothing...here's what I tried

@w_artisans = car.joins(:colour_collections).where("colour_collections.name = ?", params[:colour_collection]).where("colour_collections.name = ?", params[:colour_collection])

- I get this error PG::DatatypeMismatch: ERROR: argument of AND must be type boolean, not type record Any thoughts?

Collectives™ on Stack Overflow

Rails Query Multiple Params From Same Table

4 Answers 4

4 Comments

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related