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