Hi David, any news/updates on request/discussion above?
wkr rene On Sun, Feb 9, 2014 at 2:09 PM, Rene Stoutjesdijk <[email protected]>wrote: > Hi David, > > you're correct that it's applicable for all users :( too bad, it was to > good to be true... > > > On Sun, Feb 9, 2014 at 1:19 PM, David Precious <[email protected]>wrote: > >> >> 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 >> <[email protected]> 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 >> > <[email protected] >> > > 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 >> > > <[email protected]>wrote: >> > > >> > >> On Sun, 2 Feb 2014 11:43:05 +0100 >> > >> Rene Stoutjesdijk <[email protected]> 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") <[email protected]> >> > >> 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 >> > >> [email protected] >> > >> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users >> > >> >> > > >> > > >> >> >> >> -- >> David Precious ("bigpresh") <[email protected]> >> 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 >> [email protected] >> http://lists.preshweb.co.uk/mailman/listinfo/dancer-users >> > >
_______________________________________________ dancer-users mailing list [email protected] http://lists.preshweb.co.uk/mailman/listinfo/dancer-users
