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

[Rajesh Kumar 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/
>