<div dir="ltr">Hi David,<div><br></div><div>today i did some tests and took an approach which is maybe a bit different then you suggested.</div><div>Please see below:</div><div><br></div><div>i've added two keywords in my dancer app, see example below:</div>
<div><br></div><div><div> basic_where => 1,</div><div> basic_where_clause => " WHERE client.`client_name`LIKE'%%rene%%'",<br></div></div><div><br></div><div>the rule basic_where is used as a boolean identifier to specify or there's a basic where clause which must always be used.</div>
<div>the second line identifies the where clause :)</div><div><br></div><div><br></div><div>in the SimpleCRUD.pm i've added a few lines of code starting at line 910/911 (just after you define the $query)</div><div>see snapshot below, the lines between rene start and rene stop have been added :)</div>
<div><br></div><div><br></div><div><br></div><div>QUOTE</div><div><br></div><div><div> my $query = "SELECT $col_list $add_actions FROM $table_name";</div><div>### rene start</div><div> if ($args->{basic_where}) {</div>
<div> $query .= $args->{basic_where_clause};</div><div> }</div><div>### rene stop</div><div><br></div><div><br></div><div> # If we have foreign key relationship info, we need to join on those tables:</div>
<div> if ($args->{foreign_keys}) {</div><div> while (my ($col, $foreign_key) = each %{ $args->{foreign_keys} }) {</div><div> my $ftable = $dbh->quote_identifier($foreign_key->{table});</div>
<div> my $lkey = $dbh->quote_identifier($col);</div><div> my $rkey = $dbh->quote_identifier($foreign_key->{key_column});</div><div><br></div><div> # Identifiers quoted above, and $table_name quoted further up, so</div>
<div> # all safe to interpolate</div><div> my $what_to_join = $ftable;</div><div> my $join_reference = $ftable;</div><div> if (my $alias = $fk_alias{$col}) {</div><div> $what_to_join = " $ftable AS $alias ";</div>
<div> $join_reference = $alias;</div><div> }</div><div> # If this join is not a left join, the list view only shows rows where the</div><div> # foreign key is defined and matching a row</div>
<div> $query .= " LEFT JOIN $what_to_join ON $table_name.$lkey = $join_reference.$rkey ";</div><div> }</div><div> }</div><div><br></div><div> # If we have a query, we need to assemble a WHERE clause...</div>
<div> if (params->{'q'}) {</div><div> my ($column_data)</div><div> = grep { lc $_->{COLUMN_NAME} eq lc params->{searchfield} }</div><div> @{$columns};</div><div> debug(</div>
<div> "Searching on $column_data->{COLUMN_NAME} which is a "</div><div> . "$column_data->{TYPE_NAME}"</div><div> );</div><div><br></div><div> if ($column_data) {</div>
<div> my $search_value = params->{'q'};</div><div> if (params->{searchtype} eq 'c') {</div><div> $search_value = '%' . $search_value . '%';</div><div>
}</div><div>### rene start</div><div> <span class="Apple-tab-span" style="white-space:pre"> </span> if ($args->{basic_where}) {</div><div> $query</div><div> .= " AND $table_name."</div>
<div> . $dbh->quote_identifier(params->{searchfield})</div><div> . (params->{searchtype} eq 'c' ? 'LIKE' : '=')</div><div> . $dbh->quote($search_value);</div>
<div> }</div><div> else {</div><div>### rene stop</div><div> $query</div><div> .= " WHERE $table_name."</div><div> . $dbh->quote_identifier(params->{searchfield})</div>
<div> . (params->{searchtype} eq 'c' ? 'LIKE' : '=')</div><div> . $dbh->quote($search_value);</div><div><br></div><div>### rene start</div><div> }</div>
<div>### rene stop</div><div> $html</div></div><div><br></div><div><br></div><div>UNQUOTE</div><div><br></div><div>I think this is helping me with the functionality for having a specific where clause which i can set from within the dancer app.</div>
<div>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.</div><div>(didn't do any tests with joins or other db engines besides MySQL or ......)</div>
<div><br></div><div>Please let me know or this is working correctly according to you. And if yes can this be somehow integrated within SimpleCRUD.</div><div><br></div><div>Thx in advance for your response and support</div>
<div><br></div><div><br></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Sat, Feb 8, 2014 at 10:19 AM, Rene Stoutjesdijk <span dir="ltr"><<a href="mailto:r.stoutjesdijk@gmail.com" target="_blank">r.stoutjesdijk@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi David,<div>thx for the answers and explanations.</div><div><br></div><div>Looking forward to hear from you.</div>
<div><br></div><div>I'll start looking into the option you proposed for the custom_callbacks option as it looks the best option for now.</div>
<div><br></div><div>thx</div><span class="HOEnZb"><font color="#888888"><div>rene</div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On Mon, Feb 3, 2014 at 11:29 AM, 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"><div>On Sun, 2 Feb 2014 11:43:05 +0100<br>
Rene Stoutjesdijk <<a href="mailto:r.stoutjesdijk@gmail.com" target="_blank">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 very<br>
> 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 to<br>
> remove as it's a configurable parameter).<br>
<br>
</div>OTTOMH, I think adding and editing are lumped together at the moment;<br>
I'll see how difficult it would be to add a config parameter to<br>
disallow adding, too.<br>
<div><br>
> 2) can i have somewhere the option to filter (eg by a where clause in<br>
> mysql) based upon the login credentials? I would like to limit the<br>
> table view by a where clause based upon the login name.<br>
<br>
</div>Hmm, there's nothing like that, currently, but it may well be possible<br>
to add a hook in the right place that would let you modify the query<br>
about to run, or pass extra criteria to go in the WHERE clause.<br>
<br>
What you could do, is use the custom_callbacks option to pass callbacks<br>
to HTML::Table::FromDatabase to modify/skip rows upon output - that'd<br>
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 would<br>
throw the pagination out<br>
<br>
So, a way to supply additional criteria for the where clause via a hook<br>
may be the way to go. I'll take a look at that when I have a chance,<br>
but I'm afraid I can't make any guesses as to when that'll be - $work,<br>
and life in general at the moment, is keeping me rather busy!<br>
<br>
Cheers<br>
<br>
Dave P<br>
<span><font color="#888888"><br>
--<br>
David Precious ("bigpresh") <<a href="mailto:davidp@preshweb.co.uk" target="_blank">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" target="_blank">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>
</font></span></blockquote></div><br></div>
</div></div></blockquote></div><br></div>