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
> 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]
> <mailto:[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]
> <mailto:hobousers%[email protected]>.
> > To post to this group, send email to [email protected]
> <mailto:[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]
> <mailto:hobousers%[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[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.
--
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 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.
