[Catalyst] complex search using Catalyst::Controller::DBIC::API::REST
Rajesh Kumar Mallah
mallah at redgrape.tech
Tue Apr 4 10:16:48 GMT 2017
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
>>>>>> <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/
>>>
>>
>>
>>
>>
>> _______________________________________________
>> 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/
>
More information about the Catalyst
mailing list