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 <a...@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 _______________________________________________ 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