Help! Casting Search parameters to satisfy Postgres' strong typing

[Tim Griffin]

Hi all;

Bit of an urgent plea here to help me understand how to adapt search parameters in a Hobo Index function to satisfy PostgreSQL's strong typing. 

I have a Plan class:

    plan_no     :decimal, :default => 0, :precision => 12, :scale => 3   # to accommodate 123456789.999

    clsr             :integer       

In my index method, I include these in search scopes:

    scopes = {                                                               

      :search => [ params[:search], :plan_no, :clsr ]

    }

Then, I search:

    @plans = Plan.regional(session[:region_id].to_i).apply_scopes(scopes)

But, Postgres is very unhappy about comparing the text I type in the Search box with the decimal and integer fields plan_no and clsr:

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

: SELECT  "plans".* FROM "plans" WHERE (plans.region_id = 1) AND (((plans.plan_no ILIKE '%4430%') OR (plans.clsr ILIKE '%4430%'))) DESC LIMIT 40 OFFSET 0

PG::UndefinedFunction: ERROR:  operator does not exist: numeric ~~* unknown

LINE 1: ... WHERE (plans.region_id = 1) AND (((plans.plan_no ILIKE '%44...

Can anyone enlighten me on how to work those suggested casts into the search process without resorting to preparing my own SQL statement? This seems doubly difficult given that both fields are of different types, and I never know whether someone is searching by plan_no or by clsr!

Many thanks,

Tim

[Ignacio Huerta]

Hi Tim,

I think a good solution would be using some custom SQL:
search = params[:search]
@plans = Plan.regional(session[:region_id].to_i).where("name LIKE ? OR
last_name LIKE ? OR first_name LIKE ?",
"%#{search}%","%#{search}%","%#{search}%")

You could also create a nice scope and then use it like:
@plans = Plan.regional(session[:region_id].to_i).search(params[:search])

Warm regards,
Ignacio


El 08/02/14 14:14, Tim Griffin escribió:

> Hi all;
>
> Bit of an urgent plea here to help me understand how to adapt search
> parameters in a Hobo Index function to satisfy PostgreSQL's strong typing.
>
> I have a Plan class:
>
> plan_no :decimal, :default => 0, :precision => 12, :scale => 3
> # to accommodate 123456789.999
> clsr :integer
>

> In my /index/ method, I include these in search scopes:

>
> scopes = {
>
> :search => [ params[:search], :plan_no, :clsr ]
> }
>
> Then, I search:
>
> @plans = Plan.regional(session[:region_id].to_i).apply_scopes(scopes)
>
>
> But, Postgres is very unhappy about comparing the text I type in the

> Search box with the decimal and integer fields /plan_no/ and /clsr:/
> /
> /

> HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
> : SELECT "plans".* FROM "plans" WHERE (plans.region_id = 1) AND
> (((plans.plan_no ILIKE '%4430%') OR (plans.clsr ILIKE '%4430%'))) DESC
> LIMIT 40 OFFSET 0

> /

> PG::UndefinedFunction: ERROR: operator does not exist: numeric ~~* unknown

> /

> LINE 1: ... WHERE (plans.region_id = 1) AND (((plans.plan_no ILIKE '%44...
>
> Can anyone enlighten me on how to work those suggested casts into the
> search process without resorting to preparing my own SQL statement? This
> seems doubly difficult given that both fields are of different types,
> and I never know whether someone is searching by plan_no or by clsr!
>
> Many thanks,
> Tim
>

> --
> You received this message because you are subscribed to the Google
> Groups "Hobo Users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to hobousers+...@googlegroups.com.
> To post to this group, send email to hobo...@googlegroups.com.
> Visit this group at http://groups.google.com/group/hobousers.
> For more options, visit https://groups.google.com/groups/opt_out.

--
Ignacio Huerta Arteche
http://www.ihuerta.net
Teléfono: 0034 645 70 77 35
Email realizado con software libre

[Tim Griffin]

Hi Ignacio;

Thanks for the suggestions, but no go. 

Doing this:

    search= params[:search]

    @plans = Plan.regional(session[:region_id].to_i)

       .where("plan_no LIKE ? OR plan_type LIKE ? OR clsr LIKE ?", "%#{search}%","%#{search}%","%#{search}%")

Just gives me roughly the same thing:

PG::UndefinedFunction: ERROR:  operator does not exist: numeric ~~ unknown

LINE 1: ..."plans"  WHERE (plans.region_id = 1) AND (plan_no LIKE '%442...

                                                             ^

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

: SELECT COUNT(*) FROM "plans"  WHERE (plans.region_id = 1) AND (plan_no LIKE '%4420%' OR plan_type LIKE '%4420%' OR clsr LIKE '%4420%')

I resorted to building my own search fields on the Index page, circumventing the usual Hobo approach of 

scopes = { :search => [ params[:search], :plan_no, :plan_type, :clsr ] }

When you're searching against fields of mixed types with a single value, this approach just doesn't work with PostgreSQL. It cannot compare the text coming from the Search box against the numeric fields without explicit type casts. MySQL is more forgiving. 

Now, I pass separate search fields as individual query parameters to the #index method, convert them to types that match the fields, and use them to build up an array of conditions (using a variation of the addition to Array described in http://zargony.com/2008/06/08/activerecord-condition-building-made-easy, with a slight correction to the code there).

    conditions = []

    conditions.add_condition!( {:plan_no => params[:search_plan_no].to_f }, "OR") unless params[:search_plan_no].blank?

    conditions.add_condition!( {:clsr => params[:search_clsr].to_i }, "OR") unless params[:search_clsr].blank? 

    scopes = { :order_by => parse_sort_param(:plan_no, :clsr, :plan_type) }

    scopes.merge!({ ... }) #other scopes for filters

    @plans = Plan.regional(session[:region_id].to_i)

      .apply_scopes(scopes)

      .where(conditions)

      .order('plan_no DESC').paginate(:per_page => 100, :page => params[:page])

I was just surprised by all this extra work switching to PostgreSQL. That will teach me to pick my application's database with more care at the start! But, the end result has been a more powerful search interface (especially in other models where I have 10 fields to search against and can now use combinations of fields as necessary with AND or OR conditions. 

Tim

You received this message because you are subscribed to a topic in the Google Groups "Hobo Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/hobousers/qrnSSyzDAZQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to hobousers+...@googlegroups.com.

[Ignacio Huerta]

Hi Tim,

I'm glad you found the solution, thanks for sharing. This is the kind of
stuff they never tell you about when saying "Rails is database
agnostic". Yeah, sure :D.

Warm regards,
Ignacio

El 11/02/14 04:40, Tim Griffin escribió:

> Hi Ignacio;
>
> Thanks for the suggestions, but no go.
>
> Doing this:
>
> search= params[:search]
> @plans = Plan.regional(session[:region_id].to_i)
> .where("plan_no LIKE ? OR plan_type LIKE ? OR clsr LIKE ?",
> "%#{search}%","%#{search}%","%#{search}%")
>
> Just gives me roughly the same thing:
>
> PG::UndefinedFunction: ERROR: operator does not exist: numeric ~~ unknown
> LINE 1: ..."plans" WHERE (plans.region_id = 1) AND (plan_no LIKE '%442...
> ^
> HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
> : SELECT COUNT(*) FROM "plans" WHERE (plans.region_id = 1) AND (plan_no
> LIKE '%4420%' OR plan_type LIKE '%4420%' OR clsr LIKE '%4420%')
>
>
> I resorted to building my own search fields on the Index page,
> circumventing the usual Hobo approach of
>
> scopes = { :search => [ params[:search], :plan_no, :plan_type, :clsr ] }
>
> When you're searching against fields of mixed types with a single value,

> this approach *just doesn't work with PostgreSQL*. It cannot compare the

> <mailto:hobousers%2Bunsu...@googlegroups.com>.

> > To post to this group, send email to hobo...@googlegroups.com

> <mailto:hobo...@googlegroups.com>.

> > Visit this group at http://groups.google.com/group/hobousers.
> > For more options, visit https://groups.google.com/groups/opt_out.
>
> --
> Ignacio Huerta Arteche
> http://www.ihuerta.net
> Teléfono: 0034 645 70 77 35
> Email realizado con software libre
>
> --
> You received this message because you are subscribed to a topic in
> the Google Groups "Hobo Users" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/hobousers/qrnSSyzDAZQ/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> hobousers+...@googlegroups.com

> <mailto:hobousers%2Bunsu...@googlegroups.com>.

> To post to this group, send email to hobo...@googlegroups.com

> <mailto:hobo...@googlegroups.com>.

[Ignacio Huerta]

Fixed: https://github.com/Hobo/hobo/issues/120