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/