[dancer-users] SimpleCRUD question
David Precious
davidp at preshweb.co.uk
Sun Feb 9 12:19:26 GMT 2014
Hi Rene,
At a glance, that will explode if there is a JOIN in use (when using
foreign keys). I also think it makes more sense to use a set of params
that could be passed to database->quick_select, so that quoting etc to
avoid SQL injection is handled for you.
One thing I'm not sure about - passing the where query as you have will
work, but I think you wanted it to use different queries for different
users - and I think that the query you pass in the simple_crud() call
will be used for all users, so wouldn't satisfy that requirement.
Will have another look and think soon though, as I'm about to head out
to play squash :)
Cheers
Dave P
On Sun, 9 Feb 2014 12:22:36 +0100 Rene Stoutjesdijk
<r.stoutjesdijk at gmail.com> wrote:
> 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
> >>
> >
> >
--
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
More information about the dancer-users
mailing list