[Dbix-class] Adding an additional custom join parameter

[neil.lunn]

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