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
<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/
_______________________________________________
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/
