[dancer-users] SimpleCRUD question
Rene Stoutjesdijk
r.stoutjesdijk at gmail.com
Sat Apr 12 08:59:41 BST 2014
Hi David,
any news/updates on request/discussion above?
wkr
rene
On Sun, Feb 9, 2014 at 2:09 PM, Rene Stoutjesdijk
<r.stoutjesdijk at gmail.com>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 <davidp at preshweb.co.uk>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
>> <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
>>
>>
>> _______________________________________________
>> 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/20140412/e0e83f5a/attachment.html>
More information about the dancer-users
mailing list