On 9/12/2013 12:41 PM, Andrew Beverley 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?
Argh. I Promised to be less angry next year and not be an older version
of MST. <Less hair you know>
http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/Class/Relationship.pm
<http://search.cpan.org/%7Eribasushi/DBIx-Class-0.08250/lib/DBIx/Class/Relationship.pm>
and of course:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/Class/Manual/Joining.pod
<http://search.cpan.org/%7Eribasushi/DBIx-Class-0.08250/lib/DBIx/Class/Manual/Joining.pod>
So RFTM. You need to define your joins in you classes. Schema loader
stuff will only do what you reasonably, intelligently put in your
initial DDL on the SQL side. And you probably didn't. So * DO IT RIGHT
IN YOUR CODE *
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
---
This email is free from viruses and malware because avast! Antivirus protection
is active.
http://www.avast.com
_______________________________________________
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
