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
On Mon, Feb 10, 2014 at 3:33 PM, Ignacio Huerta <[email protected]> wrote:
> 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 [email protected].
> > To post to this group, send email to [email protected].
> > 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
> [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/hobousers.
> For more options, visit https://groups.google.com/groups/opt_out.
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/hobousers.
For more options, visit https://groups.google.com/groups/opt_out.
