today i did some tests and took an approach which is maybe a bit different then you suggested.
the rule basic_where is used as a boolean identifier to specify or there's a basic where clause which must always be used.
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 :)
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
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.
Please let me know or this is working correctly according to you. And if yes can this be somehow integrated within SimpleCRUD.