leaving out the appending of base in RequestArguments.pm
does makes it work in my case.
--- RequestArguments.pm.orig 2017-03-28 22:59:23.117637072 +0530
+++ RequestArguments.pm 2017-03-28 22:59:34.685636949 +0530
@@ -371,12 +371,11 @@
# might be a sql function instead of a column name
# e.g. {colname => {like => '%foo%'}}
else {
# but only if it's not a hashref
unless ( ref $value eq 'HASH' ) {
- $search_params->{ join( '.', $base, $column ) } =
- $param->{$column};
+ $search_params->{ $column } = $param->{$column};
}
else {
die "unsupported value '$value' for column
'$column'\n";
}
}
>
> 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/
