[Dbix-class] Passing values to a custom join condition

[Gerhard Jungwirth]

> My question: is it possible for the value "100" in my code above to be a
> variable, with the value passed from ->search (or otherwise). I can't
> see anything in the args that are passed to the function that could be
> used.
>

Hello,

I had the same problem like you had, and as far as I know, this is not
supported in custom relationships. You have a few options though.


1) Arbitrary SQL with a ResultSource::View

see
https://metacpan.org/pod/release/RIBASUSHI/DBIx-Class-0.08250/lib/DBIx/Class/Manual/Cookbook.pod#Arbitrary-SQL-through-a-custom-ResultSource

__PACKAGE__->result_source_instance->view_definition(q[
  SELECT t.* FROM yourtable t
  LEFT JOIN single_tasks s ON t.task_id = s.id
  AND s.site_id = ?
]);

And then something like

$rs->search( {},{ bind  => [ 100 ] });

2) The unsupported, deprecated and probably DANGEROUS "from"-attribute

see
https://metacpan.org/pod/release/RIBASUSHI/DBIx-Class-0.08111/lib/DBIx/Class/ResultSet.pm#from

$rs->search({},{
        alias => 'yourtable',
        from => [
            { yourtable => 'yourtable' },
            [
                { single_tasks => 'single_tasks', -join_type => 'left'},
                { 'yourtable.task_id' => { '=' => \'single_tasks.id'},
'single_tasks.site_id' => 100,
            ],
        ],
    });

You can probably also use UNIONs
(DBIx::Class::Helper::ResultSet::SetOperations)
but may also be very nasty.

Disclaimer: untested code

Greetings