Re^2: Module for substituting complex criteria into hand-written SQL queries

in reply to Re: Module for substituting complex criteria into hand-written SQL queries
in thread Module for substituting complex criteria into hand-written SQL queries

Thanks for your reply! SQL::Abstract::More looks powerful, but that is a slightly different approach from what I would like. It seems like it's about generating complex SQL queries from a data structure.

I'm fine with using SQL itself as a DSL to describe SQL. However, sometimes a condition more complex than just parameter = ? is not known beforehand. E.g. we don't know whether user wants dates after, before, or between certain point(s), but the rest of the query is still the same. In such cases pre-written SQL is not enough. I usually end up concatenating generated and pre-written parts of query by hand. Possibly not the most efficient way, but that's what I'm comfortable with.

So I think there may be a tool that automates it by patching a pre-written query with some condition group(s). Hard to figure out what I'm really looking for... If I knew for sure, I'd just google instead of asking...

Comment on Re^2: Module for substituting complex criteria into hand-written SQL queries

Replies are listed 'Best First'.
Re^3: Module for substituting complex criteria into hand-written SQL queries by 1nickt (Canon) on Apr 16, 2017 at 21:14 UTC
There is no reason you can't build your parameters based on runtime conditions: `my $cutoff = time - 86400; my $first_seen_time = {}; if ( $user_wants_dates_before_today ) { $first_seen_time = { '<' => $cutoff, }; } else { $first_seen_time = { '>=' => $cutoff, }; ); my ( $sql, @bind ) = $sqla->select( -columns => 'id, name', -from => 'users', -where => { status => $status, first_seen_time => $first_seen_time, }, );` [download] Also, for another approach, remember that you can insert actual SQL into the parameters to a SQLA call, and you could generate that SQL snippet based on your run-time conditions. Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l]

Replies are listed 'Best First'.

Re^3: Module for substituting complex criteria into hand-written SQL queries
by 1nickt (Canon) on Apr 16, 2017 at 21:14 UTC

There is no reason you can't build your parameters based on runtime conditions:

my $cutoff = time - 86400;
my $first_seen_time = {};

if ( $user_wants_dates_before_today ) {
    $first_seen_time = {
        '<' => $cutoff,
    };
} else {
    $first_seen_time = {
        '>=' => $cutoff,
    };
);

my ( $sql, @bind ) = $sqla->select(
    -columns => 'id, name',
    -from    => 'users',
    -where   => {
        status          => $status,
        first_seen_time => $first_seen_time,
    },
);
[download]

Also, for another approach, remember that you can insert actual SQL into the parameters to a SQLA call, and you could generate *that* SQL snippet based on your run-time conditions.

Hope this helps!

The way forward always starts with a minimal test.

[reply]
[d/l]

In Section Seekers of Perl Wisdom