name => [ 'Billy Joel', 'Brittany Spears' ],
});
- my $rs = $schema->resulset('CD')->search({
+ my $rs = $schema->resultset('CD')->search({
artist_id => { 'IN' => $inside_rs->get_column('id')->as_query },
});
B<NOTE>: You have to explicitly use '=' when doing an equality comparison.
The following will B<not> work:
- my $rs = $schema->resulset('CD')->search({
+ my $rs = $schema->resultset('CD')->search({
artist_id => $inside_rs->get_column('id')->as_query,
});
+=head3 Support
+
+Subqueries are supported in the where clause (first hashref), and in the
+from, select, and +select attributes.
+
+=head3 Correlated subqueries
+
+ my $cdrs = $schema->resultset('CD');
+ my $rs = $cdrs->search({
+ year => {
+ '=' => $cdrs->search(
+ { artistid => { '=' => \'me.artistid' } },
+ { alias => 'inner' }
+ )->get_column('year')->max_rs->as_query,
+ },
+ });
+
+That creates the following SQL:
+
+ SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
+ FROM cd me
+ WHERE year = (
+ SELECT MAX(inner.year)
+ FROM cd inner
+ WHERE artistid = me.artistid
+ )
+
=head2 Predefined searches
You can write your own L<DBIx::Class::ResultSet> class by inheriting from it
$resultset->search(
{
- numbers => \[ '= ?', [1, 2, 3] ]
+ numbers => \[ '= ?', [numbers => [1, 2, 3]] ]
}
);
See L<SQL::Abstract/array_datatypes> and L<SQL::Abstract/Literal SQL with
-placeholders and bind values (subqueries)> for more explanation.
+placeholders and bind values (subqueries)> for more explanation. Note that
+L<DBIx::Class> sets L<SQL::Abstract/bindtype> to C<columns>, so you must pass
+the bind values (the C<[1, 2, 3]> arrayref in the above example) wrapped in
+arrayrefs together with the column name, like this: C<< [column_name => value]
+>>.
=head1 BOOTSTRAPPING/MIGRATING