[Dbix-class] How about a reverse parser script/utility? (Was: Query Building Help)

[Sam Vilain]

Matt S Trout wrote:
> Yes, it would be possible. But I'd much rather provide a query syntax for DBIC 
> that more closely mirrors the SQL syntax. My two current thoughts on this are 
> via operator overloading -
>
> my $this_millenium = FROM $cds_rs WHERE { $_->year >= 2000 & $_->tracks->count 
>  > 10 };
>
> or via some hybrid syntax -
>
> my $this_millenium = $cds_rs->search( AND( 'year' GTE 2000, 'count(tracks)' GT 
> 10 ) );
>   

This is one of the things I hoped to touch on during my withdrawn
YAPC::Eu talk. You already have a "ResultSet" abstraction, which is
similar in many ways to a Tangram "Remote" object.

Tangram:

my ($r_cd, $r_track) = $storage->remote( "CD", "Track" );

my $this_millenium = $storage->cursor
( $r_cd,
filter => ( ( $r_cd->{year} >= 2000 ) &
$r_cd->{tracks}->includes($r_track) &
( $r_track->count() > 10 ) ),
group => [ $r_cd ],
retrieve => [ $r_track->count() ],
);

(or something like that, anyway)

So, the "interesting" part here is that's happening with those $r_cd
and $r_track objects. What do they represent? Well, the abjunction
of all the rows that exist in the table, of course. So, we can port
the first line to DBIx::Class easily:

my $r_cd = $schema->resultset('CD');
my $r_track = $schema->resultset('Track');

Let's make this a slightly simpler problem for the moment by not using
aggregates. Let's say we're looking for a CD released after 2000 with
a track called "I Neva Dared". Let's first filter $r_track some more.

my $nevadared = $r_track->search({ name => "I Neva Dared" });
my $millenium = $r_cd->search({ year => { '>=' => 2000 });

We still haven't actually performed a query, right? But we've got
these two ResultSet objects. Set theory tells us that we can join any
two ResultSets (unless you're on MySQL).

What might that look like?

# note: speculative
my $joined_rs = $millenium->search
({ 'tracks' => { '∋' => $nevadared } } );

$joined_rs->order_by('year'); # we want to use remotes here too!
print $joined_rs->first->name; # prints "Remarkable Engines"

Note that "∋" is considered a Collection operation (aka 'includes'),
and that the Array that drives the "tracks" relation is considered
to be a sub-class of Collection.

Just to give a heads-up of a problem which I expect you won't need to
deal with for some time, consider the case where you join two sets of
tables together with an INNER JOIN into a resultset, then OUTER join
those two resultsets. The query would look something like this:

SELECT
t1.id,
t1.type,
t1.colour,
t2.thought,
[...]
t2.birthDate,
t4.firstName,
t3.colour
FROM
Person t1,
NaturalPerson t2
LEFT JOIN
(Person t3
INNER JOIN
NaturalPerson t4
ON
t4.id = t3.id AND
t4.firstName = 'Marge')
ON
t2.partner = t3.id AND
t3.type IN (2)
WHERE
t1.id = t2.id
ORDER BY
t2.firstName

(generated by a fragment in t/springfield/03-queries.t).

Of course, this is quite an unusual query - unusual enough that SQLite
refuses to do it and MySQL returns incorrect results. But when you
start dealing with vertically mapped inheritance (either the Tangram
or David Wheeler's approach), this comes up whenever you outer join
two sub-classes of something and filter on something not in the base
class of the outer thing.

Sam.