[Catalyst] complex search using Catalyst::Controller::DBIC::API::REST
Rajesh Kumar Mallah
mallah at redgrape.tech
Tue Mar 28 16:58:30 GMT 2017
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
>>>> <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/
>>>>
>>>
>>
>>
>>
>>
>> _______________________________________________
>> 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/
>
>
>
>
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
> 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 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/
>
More information about the Catalyst
mailing list