[Dbix-class] Re: [OT][ANNOUNCE] SQL Generation with SQL::DB

[A. Pagaltzis]

* Emanuele Zeppieri <ema_zep at libero.it> [2007-09-07 11:15]:
> Mark Lawrence wrote:
> >If that is so, then you are asking how the following is
> >evaluated?
> >
> >    ($track->length > 248) & ! ($cd->year < 1997)
> 
> No, I'm asking how it could be *built* by code without string
> concatenations/interpolations.

Uh. They’re Perl expressions. Don’t you know how to write
conditionally executed Perl expressions? What are you doing on
this list? :-)

    use List::Util qw( reduce );

    my @cond;

    # in real code the following stuff would be more systematic;
    # this is just to demonstrate the principle

    if ( $min_track_length ) {
        push @cond, $track->length > $min_track_length;
    }

    if ( $max_year ) {
        push @cond, $cd->year < $max_year;
    }

    my @where = @cond
        ? ( where => reduce { $a & $b } @cond )
        : ();

    $schema->query(
        select   => [$track->title, $cd->year],
        from     => [$track, $cd],
        distinct => 1,
        union    => $query2,
        @where,
    );

This could be a bit simpler if SQL::DB provided an interface
shortcut so I didn’t have to do the fiddly bit with the `@where`
array there.

At minimum it would consider `where => undef` to be an empty
constraint, so `where => reduce { $a & $b } @cond` would work
(since `reduce` returns undef when the input list is empty). This
would allow maximum control, since I can use a specific operator
of my choosing in the `reduce` block.

For simpler cases maybe it could take multiple separate `where`
clauses that get ANDed, in which case I could just say

    if ( $min_track_length ) {
        push @cond, where => $track->length > $min_track_length;
    }

    if ( $max_year ) {
        push @cond, where => $cd->year < $max_year;
    }

    $schema->query(
        select   => [$track->title, $cd->year],
        from     => [$track, $cd],
        distinct => 1,
        union    => $query2,
        @cond,
    );

Mark, are you listening?

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>