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