[Dbix-class] Adding an additional custom join parameter

[Andrew Beverley]

My database has a table that has 2 other "has many" tables related to
it, best described by this diagram:

http://files.andybev.com/schema.png

I want to select from the "task" table, joining both other tables at the
same time. In order for the joins to work correctly, in raw SQL I would
use 2 conditions for the join of the "issue" table:

"issues.task_id = me.id AND issues.site_id = site_tasks.site_id"

However, by default, DBIx::Class only uses the first condition, as per
my relationship definitions. How can I add the second condition?

I have tried adding a second join condition to the relationship
definition, but as far as I can tell only the 2 immediate tables can be
specified.

FWIW, the full SQL statement I am trying to execute is as follows:

   SELECT MAX( issue.completed ) AS max, period_qty
     FROM task me
LEFT JOIN site_task ON site_task.task_id = me.id
LEFT JOIN site      ON site.id = site_task.site_id
LEFT JOIN issue     ON issue.task_id = me.id
                   AND issue.site_id = site_task.site_id   <== Need this
    WHERE period_unit = 'week'
 GROUP BY site_task.id
   HAVING max < DATE_SUB(NOW(), INTERVAL period_qty week)

The values selected are not always correct without the second join
condition.

Thanks,

Andy