[dancer-users] SimpleCRUD question
Rene Stoutjesdijk
r.stoutjesdijk at gmail.com
Sun Feb 9 11:22:36 GMT 2014
Hi David,
today i did some tests and took an approach which is maybe a bit different
then you suggested.
Please see below:
i've added two keywords in my dancer app, see example below:
basic_where => 1,
basic_where_clause => " WHERE client.`client_name`LIKE'%%rene%%'",
the rule basic_where is used as a boolean identifier to specify or there's
a basic where clause which must always be used.
the second line identifies the where clause :)
in the SimpleCRUD.pm i've added a few lines of code starting at line
910/911 (just after you define the $query)
see snapshot below, the lines between rene start and rene stop have been
added :)
QUOTE
my $query = "SELECT $col_list $add_actions FROM $table_name";
### rene start
if ($args->{basic_where}) {
$query .= $args->{basic_where_clause};
}
### rene stop
# If we have foreign key relationship info, we need to join on those
tables:
if ($args->{foreign_keys}) {
while (my ($col, $foreign_key) = each %{ $args->{foreign_keys} }) {
my $ftable = $dbh->quote_identifier($foreign_key->{table});
my $lkey = $dbh->quote_identifier($col);
my $rkey = $dbh->quote_identifier($foreign_key->{key_column});
# Identifiers quoted above, and $table_name quoted further up,
so
# all safe to interpolate
my $what_to_join = $ftable;
my $join_reference = $ftable;
if (my $alias = $fk_alias{$col}) {
$what_to_join = " $ftable AS $alias ";
$join_reference = $alias;
}
# If this join is not a left join, the list view only shows
rows where the
# foreign key is defined and matching a row
$query .= " LEFT JOIN $what_to_join ON $table_name.$lkey =
$join_reference.$rkey ";
}
}
# If we have a query, we need to assemble a WHERE clause...
if (params->{'q'}) {
my ($column_data)
= grep { lc $_->{COLUMN_NAME} eq lc params->{searchfield} }
@{$columns};
debug(
"Searching on $column_data->{COLUMN_NAME} which is a "
. "$column_data->{TYPE_NAME}"
);
if ($column_data) {
my $search_value = params->{'q'};
if (params->{searchtype} eq 'c') {
$search_value = '%' . $search_value . '%';
}
### rene start
if ($args->{basic_where}) {
$query
.= " AND $table_name."
. $dbh->quote_identifier(params->{searchfield})
. (params->{searchtype} eq 'c' ? 'LIKE' : '=')
. $dbh->quote($search_value);
}
else {
### rene stop
$query
.= " WHERE $table_name."
. $dbh->quote_identifier(params->{searchfield})
. (params->{searchtype} eq 'c' ? 'LIKE' : '=')
. $dbh->quote($search_value);
### rene start
}
### rene stop
$html
UNQUOTE
I think this is helping me with the functionality for having a specific
where clause which i can set from within the dancer app.
I did some basic tests and it looks to be working fine, even no problem
with download and/or pagination. Even when having the query within the
webpage it is working as expected.
(didn't do any tests with joins or other db engines besides MySQL or ......)
Please let me know or this is working correctly according to you. And if
yes can this be somehow integrated within SimpleCRUD.
Thx in advance for your response and support
On Sat, Feb 8, 2014 at 10:19 AM, Rene Stoutjesdijk <r.stoutjesdijk at gmail.com
> wrote:
> Hi David,
> thx for the answers and explanations.
>
> Looking forward to hear from you.
>
> I'll start looking into the option you proposed for the custom_callbacks
> option as it looks the best option for now.
>
> thx
> rene
>
>
> On Mon, Feb 3, 2014 at 11:29 AM, David Precious <davidp at preshweb.co.uk>wrote:
>
>> On Sun, 2 Feb 2014 11:43:05 +0100
>> Rene Stoutjesdijk <r.stoutjesdijk at gmail.com> wrote:
>>
>> > Hi All,
>> >
>> > first let me say that i'm not an experienced dancer user.
>> > My experience for now is that it really works :).
>> >
>> > I've just started to use the SimpleCRUD plugin, which makes live very
>> > easy for this kind of actions.
>> >
>> > I do have 2 related questions which i don't know how to resolve:
>> > 1) can i remove the option to add an entry in the database, as i
>> > would only want the users to do an edit action (delete was easy to
>> > remove as it's a configurable parameter).
>>
>> OTTOMH, I think adding and editing are lumped together at the moment;
>> I'll see how difficult it would be to add a config parameter to
>> disallow adding, too.
>>
>> > 2) can i have somewhere the option to filter (eg by a where clause in
>> > mysql) based upon the login credentials? I would like to limit the
>> > table view by a where clause based upon the login name.
>>
>> Hmm, there's nothing like that, currently, but it may well be possible
>> to add a hook in the right place that would let you modify the query
>> about to run, or pass extra criteria to go in the WHERE clause.
>>
>> What you could do, is use the custom_callbacks option to pass callbacks
>> to HTML::Table::FromDatabase to modify/skip rows upon output - that'd
>> be a bit hacky though, as:
>>
>> - if you used the download options, you'd get the original data
>> unchanged
>> - if you were using pagination, filtering out rows at that step would
>> throw the pagination out
>>
>> So, a way to supply additional criteria for the where clause via a hook
>> may be the way to go. I'll take a look at that when I have a chance,
>> but I'm afraid I can't make any guesses as to when that'll be - $work,
>> and life in general at the moment, is keeping me rather busy!
>>
>> Cheers
>>
>> Dave P
>>
>> --
>> David Precious ("bigpresh") <davidp at preshweb.co.uk>
>> http://www.preshweb.co.uk/ www.preshweb.co.uk/twitter
>> www.preshweb.co.uk/linkedin www.preshweb.co.uk/facebook
>> www.preshweb.co.uk/cpan www.preshweb.co.uk/github
>>
>>
>> _______________________________________________
>> dancer-users mailing list
>> dancer-users at dancer.pm
>> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.preshweb.co.uk/pipermail/dancer-users/attachments/20140209/8aa8937f/attachment-0001.html>
More information about the dancer-users
mailing list