Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Module for substituting complex criteria into hand-written SQL queries

by Dallaylaen (Friar)
on Apr 16, 2017 at 16:44 UTC ( #1188052=perlquestion: print w/replies, xml ) Need Help??
Dallaylaen has asked for the wisdom of the Perl Monks concerning the following question:

Hello dear esteemed monks,

Sometimes I wish I could do something along the lines of

my $sth = $dbh->prepare("SELECT, f.baz FROM foo f WHERE f.???"); $sth->execute({ bar => 42});

I.e. pass a hash instead of individual values (thus ??? instead of just ? for 1 value). Also I want to be able to write

{ var => (value > 5) & (value <= 10) }

instead of a single value, specifying a complex criteria that would unfold into a proper WHERE statement (or part of it).

I was able to create a prototype module implementing (to some extent) the latter and minimally the former. However, maybe there's an already existing solution on CPAN? The size of DBIx:: namespace is far beyond my comprehension, so I seek help here.

Looks like SQL::Abstract does something similar... Are there more precise matches? And yes, I love the ability to write SQL queries by hand (or ask a DBA to), it's just substitution of criteria that I find inconvenient.

Thank you, and hope you have a nice day!

Replies are listed 'Best First'.
Re: Module for substituting complex criteria into hand-written SQL queries
by 1nickt (Parson) on Apr 16, 2017 at 17:31 UTC

    Check out SQL::Abstract::More, which extends the parent class with more flexible functionality (and uses named params, Blessed Be!)

    So you can do something like:

    my $sqla = SQL::Abstract::More->new; my $time = time; my $status = 'active'; my ( $sql, @bind ) = $sqla->select( # new users yesterday -columns => 'id, name', -from => 'users', -where => { status => $status, first_seen_time => { '>' => $time - 86400, '<=' => $time, }, }, );
    $sql: 'SELECT id, name FROM users WHERE ( ( ( first_seen_time <= ? AND + first_seen_time > ? ) AND status = ? ) )'; \@bind: [ 1492373509, 1492287109, 'active' ];
    As you can see this SQL builder often adds unnecessary parentheses; I don't worry about these since any decent RDBMS will optimize them away.

    update: added example and then example output

    Hope this helps!

    The way forward always starts with a minimal test.

      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...

        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, }, );

        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.
Re: Module for substituting complex criteria into hand-written SQL queries
by bart (Canon) on Apr 18, 2017 at 21:25 UTC
    From SQL::Abstract's docs:
    # Just generate the WHERE clause my($stmt, @bind) = $sql->where(\%where, $order);

    Just plug that into your own SQL.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1188052]
Approved by marto
Front-paged by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (6)
As of 2017-06-25 12:38 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (567 votes). Check out past polls.