ruby on rails - Query search, how to pass params from check_box form as a row sql? -

hi have controller function, based on check_box_form ( there can 1 or more value passed), this:

def show_spells     #there 3 params 'elemensts', 'levels', , 'tags'     if params[:tags].blank?       @chosen_spells = spell.where(element: params[:elements], level: params[:levels] ).to_a     else             @chosen_spells = spell.where( "element = ? , level = ? , tags = array[?]", params[:elements], params[:levels], params[:tags]).to_a     end    end 

and references object table:

class createspells < activerecord::migration   def change     create_table :spells |t|       t.string :element       t.string :level       t.string :desc       t.text :tags, array: true, default: []        t.timestamps null: false     end   end end 

the firs query works perfect, second 1 making problem, when pass 1 value elements , levels doesn't show resoults, , when pass more 1 walue elements or levels show me error: pg::invalidtextrepresentation: error: invalid input syntax type boolean

i`m new sql query searches, understand, active record query guide :

object.where(attr: params[:foo]) should equal object.where("attr = ?", params[:foo]). , seems isn`t. appreciated. :)

there few different issues here you're running into. however, believe of them related bad query syntax. activerecord's query interface powerful, without proper usage, can throw crazy errors hard debug. understanding of sql navigate better.

first, regarding "pass[ing] more 1 value elements or levels": = operator in postgres can compare against single value, , rails' parameter binding simplistic. when run code:

object.where("attr = ?", [1, 2, 3]) 

rails going run query like:

select * objects attr = 1, 2, 3; 

which invalid sql -- rails doesn't know that, pgerror. proper sql syntax is:

select * objects attr in (1, 2, 3); 

and that, best way use where's hash syntax:

object.where(attr: [1, 2, 3]) 

second, array type, operation you're looking test inclusion, rails knows test equality. have no choice use interpolated params this, you're doing, syntax off. sql needs like:

select * objects some_array_field @> array['foo', 'bar']; 

to that, .where("some_array_field @> array[?]", things) best bet.

in conclusion, code written this:

@chosen_spells = spell.where(element: params[:elements],                              level:   params[:levels])                       .where('tags @> array[?]', params[:tags]) 

and should work desired.