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

2017-04-04 Thread Hartmaier Alexander 
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_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

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=%
25mis%25_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_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

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

2017-03-28 Thread Rajesh Kumar Mallah 

Hi ,

Thanks for the response.

(1) The HTTP Request is:

/api/rest/general/members?list_returns=holder1_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
 
 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=%
> 25mis%25_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_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"
>>  }
>>]
>> }

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

2017-03-14 Thread Rajesh Kumar 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
> 
> 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=%
>> 25mis%25_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_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/

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

2017-03-01 Thread Rajesh Kumar Mallah 


Dear Petrov,


Thanks i will try the JSON.stringify approach , the snippet was just
for spitting out what you typed , i am not sure if it would be
so easy to type out the parameters in case of complex conditions.

Warm Regards
Rajesh.

> 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
> 
> 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=%
>> 25mis%25_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_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/

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

2017-03-01 Thread Dimitar Petrov 
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 
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=%
> 25mis%25_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_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/

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

2017-03-01 Thread Rajesh Kumar Mallah 


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=%25mis%25_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_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/