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/
