[Catalyst] complex search using Catalyst::Controller::DBIC::API::REST

Hartmaier Alexander alexander.hartmaier at t-systems.at
Tue Apr 4 10:05:32 GMT 2017


Have you tried using the search parameter with JSON encoded parameters 
as suggested before?

DBI::API doesn't guess if the search parameter you've provided is a 
column name or a db function.

Best regards, Alex


On 2017-03-28 18:58, Rajesh Kumar Mallah wrote:
> Hi ,
>
> Thanks for the response.
>
> (1) The HTTP Request is:
>
> /api/rest/general/members?list_returns=holder1&search.member_balance(member_id)=21
>
> (2) controller config is:   https://pastebin.com/2iT1YSPm
>
>
> Error log:
>
> [2017/03/28 22:22:44]- API.pm-290: ERROR
> DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::Pg::st
> execute failed: ERROR:  schema "me" does not exist [for Statement "SELECT
> me.holder1 FROM general.members me WHERE ( ( me.member_balance(member_id)
> = ? AND me.society_id = ? ) ) ORDER BY member_id" with ParamValues:
> 1='21', 2='50']
>
>
> Regds
> Mallah.
>
>> Hi,
>>
>> please include your controller config and the http call.
>>
>> Best regards, Alex
>>
>>
>> On 2017-03-19 04:49, Rajesh Kumar Mallah wrote:
>>> Hi ,
>>>
>>> Including member_balance(member_id) in 'search_exposes' config param
>>> did help to proceed to some extent , but the function is being
>>> prefixed by the table alias whereas it should be left alone.
>>>
>>> eg:
>>>
>>>
>>>    search_exposes     => [
>>>           qw/member_balance(member_id)/,
>>>           ............
>>>           . . . . .
>>>       ],);
>>>
>>>
>>> produces following invalid SQL note: member_balance is prefixed by
>>> 'me' .
>>>
>>>
>>> ERROR DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception:
>>> DBD::Pg::st execute failed: ERROR:  schema "me" does not exist [for
>>> Statement
>>>
>>> "SELECT me.holder1 FROM general.members me WHERE ( (
>>> me.member_balance(member_id) = ? AND me.society_id = ? ) ) ORDER BY
>>> member_id" with ParamValues: 1='21', 2='50']
>>>
>>>
>>> Any help is appreciated.
>>>
>>> regds
>>> mallah.
>>>
>>>> Hi ,
>>>>
>>>> How to perform below search:
>>>>
>>>> select member_id,holder1 from general.members where
>>>>        member_balance(member_id , '2017-03-14') < 0 ;
>>>>
>>>> there is a function on LHS of the condition
>>>>
>>>> Regds
>>>> Mallah.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>> Hello Rajesh,
>>>>>
>>>>> C::C::DBIC::API supports that under the hood, so not sure what are you
>>>>> going after.
>>>>>
>>>>> You can either use: search=JSON.stringify(object) or construct the
>>>>> search
>>>>> passing params like: search.holder.-ilike=%mis%. Both should work out
>>>>> of
>>>>> the box.
>>>>>
>>>>> Regards
>>>>>
>>>>> On Wed, Mar 1, 2017 at 10:24 AM, Rajesh Kumar Mallah
>>>>> <mallah at redgrape.tech>
>>>>> wrote:
>>>>>
>>>>>> Hi ,
>>>>>>
>>>>>> CGI::Expand collapse_hash comes to rescue ,
>>>>>> below is a small snippet that converts the perl
>>>>>> hash reference to the TT's dotted format using
>>>>>> CGI::Expand.
>>>>>>
>>>>>>
>>>>>> ============================================================
>>>>>> #!/usr/bin/perl -w
>>>>>>
>>>>>> use strict;
>>>>>>
>>>>>> use CGI::Expand qw(expand_hash  collapse_hash);
>>>>>> use Data::Dumper;
>>>>>> use JSON::XS;
>>>>>> use URI::Escape;
>>>>>>
>>>>>>
>>>>>> # SQL::Abstract Syntax comes here.
>>>>>> my $where  = {
>>>>>>          search => {
>>>>>>             holder1 => { -ilike => '%mis%' },
>>>>>>             mobile1 => { -ilike => '%967%' },
>>>>>>             flat_no => 'A203'
>>>>>>           }
>>>>>> } ;
>>>>>>
>>>>>>
>>>>>>
>>>>>> my $flat_hash = collapse_hash( $where );
>>>>>>
>>>>>> print  join '&' ,   map { my $k = $_; my $v = uri_escape(
>>>>>> $flat_hash->{$k})  ; "$k=$v"   } keys %{$flat_hash};
>>>>>>
>>>>>> print "\n";
>>>>>> ======================================================
>>>>>>
>>>>>> Output:
>>>>>>
>>>>>>
>>>>>> $VAR1 = {
>>>>>>             'search.mobile1.-ilike' => '%967%',
>>>>>>             'search.holder1.-ilike' => '%mis%',
>>>>>>             'search.flat_no' => 'A203'
>>>>>>           };
>>>>>> search.mobile1.-ilike=%25967%25&search.holder1.-ilike=%
>>>>>> 25mis%25&search.flat_no=A203
>>>>>>
>>>>>>
>>>>>>
>>>>>> Regds
>>>>>> Mallah.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>> Hi ,
>>>>>>>
>>>>>>> In continuation of previous query kindly guide how to do a
>>>>>>> anchored or unanchored ilike search using
>>>>>>> Catalyst::Controller::DBIC::API::REST
>>>>>>>
>>>>>>>
>>>>>>> I have used DBIC search with lots of search conditions in past
>>>>>>> that uses SQL::Abstract, at this moment I need a guide(document)
>>>>>>> on how to convert SQL::Abstract's conventions to Query parameter
>>>>>>> format
>>>>>>>
>>>>>>>
>>>>>>> Eg:
>>>>>>>
>>>>>>    http://10.100.102.38:3000/api/rest/general/members?list_
>>>>>> returns=holder1&list_count=10
>>>>>>> Returns:
>>>>>>>
>>>>>>> {
>>>>>>>     "Result": "OK",
>>>>>>>     "Records": [
>>>>>>>       {
>>>>>>>         "holder1": "Sh. R. Krishna Kumar"
>>>>>>>       },
>>>>>>>       {
>>>>>>>         "holder1": "Sh. Sharad Kumar Srivastava"
>>>>>>>       },
>>>>>>>       {
>>>>>>>         "holder1": "Smt. Shubhra Jain ."
>>>>>>>       },
>>>>>>>       {
>>>>>>>         "holder1": "Sh. Balam Singh Negi"
>>>>>>>       },
>>>>>>>       {
>>>>>>>         "holder1": "Sh. Subodh Jain"
>>>>>>>       },
>>>>>>>       {
>>>>>>>         "holder1": "Smt. Punita Batra"
>>>>>>>       },
>>>>>>>       {
>>>>>>>         "holder1": "Sh.K C Sardana"
>>>>>>>       },
>>>>>>>       {
>>>>>>>         "holder1": "Smt. Sunita Mishra"
>>>>>>>       },
>>>>>>>       {
>>>>>>>         "holder1": "Sh. Vijay Kumar Khanna"
>>>>>>>       },
>>>>>>>       {
>>>>>>>         "holder1": "Smt. Daisy Tyagi"
>>>>>>>       }
>>>>>>>     ]
>>>>>>> }
>>>>>>>
>>>>>>>
>>>>>>> I need a Query param that would return all holder1 matching
>>>>>>> Mis. ie holder1 ilike '%Mis%'
>>>>>>>
>>>>>>>
>>>>>>> As always Thanks in anticipation of your valuable time/attention.
>>>>>>>
>>>>>>>
>>>>>>> Regds
>>>>>>> Mallah.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> List: Catalyst at lists.scsys.co.uk
>>>>>>> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
>>>>>>> Searchable archive:
>>>>>>> http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
>>>>>>> Dev site: http://dev.catalyst.perl.org/
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> List: Catalyst at lists.scsys.co.uk
>>>>>> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
>>>>>> Searchable archive: http://www.mail-archive.com/
>>>>>> catalyst at lists.scsys.co.uk/
>>>>>> Dev site: http://dev.catalyst.perl.org/
>>>>>>
>>>>> _______________________________________________
>>>>> List: Catalyst at lists.scsys.co.uk
>>>>> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
>>>>> Searchable archive:
>>>>> http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
>>>>> Dev site: http://dev.catalyst.perl.org/
>>>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> List: Catalyst at lists.scsys.co.uk
>>> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
>>> Searchable archive:
>>> http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
>>> Dev site: http://dev.catalyst.perl.org/
>>
>>
>>
>   *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
>> T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
>> Handelsgericht Wien, FN 79340b
>>
>   *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
>> Notice: This e-mail contains information that is confidential and may be
>> privileged.
>> If you are not the intended recipient, please notify the sender and then
>> delete this e-mail immediately.
>>
>   *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
>> _______________________________________________
>> List: Catalyst at lists.scsys.co.uk
>> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
>> Searchable archive:
>> http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
>> Dev site: http://dev.catalyst.perl.org/
>>
>
>
>
>
> _______________________________________________
> List: Catalyst at lists.scsys.co.uk
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/
> Dev site: http://dev.catalyst.perl.org/




More information about the Catalyst mailing list