[Catalyst] complex search using Catalyst::Controller::DBIC::API::REST

[Hartmaier Alexander]

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.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*