[Dbix-class] Adding an additional custom join parameter

[Hailin Hu]

Can I ask why you need site_task table?
In relationship view, it is the same as issue table.

It is something like that you defined one many-to-many relation (task
<-> site) through two bridge tables (site_task and issue).

On Mon, Dec 9, 2013 at 10:41 AM, Andrew Beverley <andy at andybev.com> wrote:
> 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
>
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk