gistfile1.txt

=head3 condition

The condition argument describes the C<ON> clause of the C<JOIN>
expression used to connect the two sources when creating SQL queries.

To create simple equality joins, supply a hashref containing the
remote table column name as the key(s), and the local table column
name as the value(s), for example:

  { 'foreign.author_id' => 'self.id' }

will result in the C<JOIN> clause (assuming the relationship is named
C<books>):

  author me LEFT JOIN book books ON books.author_id = me.id

This describes a relationship between the C<Author> table and the
C<Book> table where the C<Book> table has a column C<author_id>
containing the ID value of the C<Author>.

C<foreign> and C<self> are pseudo aliases and must be entered
literally. They will be replaced with the actual correct table alias
when the SQL is produced.

Similarly invoking the relationship C<editions> with the following
condition:

  {
    'foreign.publisher_id' => 'self.publisher_id',
    'foreign.type_id'      => 'self.type_id',
  }

will result in the C<JOIN> clause:

  book me LEFT JOIN edition editions ON
        editions.publisher_id = me.publisher_id
    AND editions.type_id = me.type_id

This describes the relationship from C<Book> to C<Edition>, where the
C<Edition> table refers to a publisher and a type (e.g. "paperback"):

As is the default in L<SQL::Abstract>, the key-value pairs will be
C<AND>ed in the result. C<OR> can be achieved with an arrayref, for
example:

  [
    { 'foreign.left_itemid'  => 'self.id' },
    { 'foreign.right_itemid' => 'self.id' },
  ]

which results in the C<JOIN> clause:

  items me JOIN related_items rel_link ON rel_link.left_itemid = me.id
    OR rel_link.right_itemid = me.id

This describes the relationship from C<Items> to C<RelatedItems>,
where C<RelatedItems> is a many-to-many linking table, linking Items
back to themselves.

To create joins which describe more than a simple equality of column
values, the custom join condition coderef syntax can be used. For
example a relationship named C<cds_80s> with a condition of:

  sub {
    my $args = shift;

    return {
      "$args->{foreign_alias}.artist" => { -ident => "$args->{self_alias}.artistid" },
      "$args->{foreign_alias}.year"   => { '>', "1979", '<', "1990" },
    };
  }

will result in the C<JOIN> clause:

  artist me LEFT JOIN cd cds_80s ON
        cds_80s.artist = me.artistid
    AND ( cds_80s.year < ? AND cds_80s.year > ? )

with the bind values:

   '1990', '1979'

C<< $args->{foreign_alias} >> and C<< $args->{self_alias} >> are supplied the
same values that would be otherwise substituted for C<foreign> and C<self>
in the simple hashref syntax case.

The coderef is expected to return a valid L<SQL::Abstract> query-structure, just
like what one would supply as the first argument to
L<DBIx::Class::ResultSet/search>. The return value will be passed directly to
L<SQL::Abstract> and The resulting SQL will be used verbatim as the C<ON>
clause of the C<JOIN> statement associated with this relationship.

While every coderef-based condition must return a valid C<ON> clause, it may
elect to additionally return a simplified condition hashref when invoked as
C<< $row_object->relationship >>, as opposed to
C<< $rs->related_resultset('relationship') >>. In this case C<$row_object> is
passed to the coderef as C<< $args->{self_rowobj} >>, so a user can do the
following:

  sub {
    my $args = shift;

    return (
      {
        "$args->{foreign_alias}.artist" => { -ident => "$args->{self_alias}.artistid" },
        "$args->{foreign_alias}.year"   => { '>', "1979", '<', "1990" },
      },
      $args->{self_rowobj} && {
        "$args->{foreign_alias}.artist" => $args->{self_rowobj}->artistid,
        "$args->{foreign_alias}.year"   => { '>', "1979", '<', "1990" },
      },
    );
  }

Now this code:

    my $artist = $schema->resultset("Artist")->find({ id => 4 });
    $artist->cds_80s->all;

Can skip a C<JOIN> altogether and instead produce:

    SELECT cds_80s.cdid, cds_80s.artist, cds_80s.title, cds_80s.year, cds_80s.genreid, cds_80s.single_track
      FROM cd cds_80s
      WHERE ( ( cds_80s.artist = ? AND ( cds_80s.year < ? AND cds_80s.year > ? ) ) )

With the bind values:

    '4', '1990', '1979'

Note that in order to be able to use
L<< $row->create_related|DBIx::Class::Relationship::Base/create_related >>,
the coderef must not only return a "simple condition" hashref as its second
return value, but the hashref must contain only plain values/deflatable
objects, such that the result can be passed directly to
L<DBIx::Class::Relationship::Base/set_from_related>. For instance the C<year>
constraint in the above example prevents the relationship from being used to
to create related objects (an exception will be thrown).

In order to allow the user to go truly crazy when generating a custom C<ON>
clause, the C<$args> hashref passed to the subroutine contains some extra
metadata. Currently the coderef is executed as:

  $condition_coderef->({
    self_alias        => The alias of the invoking resultset ('me' in case of a row object),
    foreign_alias     => The alias of the to-be-joined resultset (often matches relname),
    self_resultsource => The invocant's resultsource,
    foreign_relname   => The relationship name (does *not* always match foreign_alias),
    self_rowobj       => The invocant itself in case of $row_obj->relationship
  });