Thanks for the response, I shall relook that approach and post when I work on it again.
Kind Regds mallah > 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 >>>>>> <mal...@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@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@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@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@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@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@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@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@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/
