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

[Rajesh Kumar Mallah]

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/
>>
>
>