Re: [Dbix-class] Adding an additional custom join parameter
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
Re: [Dbix-class] Adding an additional custom join parameter
On Tue, Dec 10, 2013 at 11:36:38AM +, Andrew Beverley wrote: On Tue, 2013-12-10 at 19:42 +0900, Hailin Hu wrote: Can I ask why you need site_task table? In relationship view, it is the same as issue table. That's a good question. The reason is that issues will often be generated that are not related to a task, and likewise there will be tasks that are related to sites but that do not have any issue to link them (in which case the site_task table would be used). It is something like that you defined one many-to-many relation (task - site) through two bridge tables (site_task and issue). You raise a valid point though, in that this is probably a poor way to design such a database. Maybe I should only have one site_task table to link the sites and tasks tables, and then have appropriate fields within that single table to signify whether the entry is linking the 2 tables for reason of an issue or a task. http://files.andybev.com/schema.png I can't see anything different in this png. But I get what you're talking about... I have a very basic example of this. In my many_to_many table I have boolean values for PRIMARY_GROUP and AFFILIATION_GROUP. https://github.com/cessna-cowboy/UserDatabase/blob/master/password_propagation_schema.pdf It works ok. When I update_or_create that table, I just have to ensure that the data structure has one or both of the fields set (not a problem in perl). In fact I should probable add that to my tests. Feel free to check out my GroupMembership.pm to see how it was set up. For the record, mst correctly advised against this. In my situation the better design would be to have 2 more fields in User, which are related directly to a Group (while still having the vanilla many_to_many in place). The only problem with that is you have to ensure that anyone using your application remembers to write code that creates/updates both the many_to_many relationship AND the extra direct relationships. In the end I was told (in meatspace) to write it the way the PDF shows it. It doesn't work too badly... just think of using the linking table to colour a relationship red or issue or task. Or in my case, primary or affiliation. This might not be sound DB theory but it works fine with DBIC. Gavin 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
Re: [Dbix-class] Adding an additional custom join parameter
Well, can you try this way?
Have join started from site_task, like this
$site_task-search(
...,
join = ['site', 'task', 'issue'],
)
The relationships may be like this?
_SITE_TASK_-belongs_to('site' = 'Site');
_SITE_TASK_-belongs_to('task' = 'Task');
_SITE_TASK_-might_have('issue' = 'Issue', {'foreign.site_id' =
'me.site_id', 'foreign.task_id' = 'me.task_id'}, {'join_type' =
'LEFT'});
Reference:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/Class/Relationship/Base.pm#add_relationship
I'm not sure if it works since relationship between site_task and
issue is ambiguous.
Good luck :)
On Tue, Dec 10, 2013 at 8:36 PM, Andrew Beverley a...@andybev.com wrote:
On Tue, 2013-12-10 at 19:42 +0900, Hailin Hu wrote:
Can I ask why you need site_task table?
In relationship view, it is the same as issue table.
That's a good question. The reason is that issues will often be
generated that are not related to a task, and likewise there will be
tasks that are related to sites but that do not have any issue to
link them (in which case the site_task table would be used).
It is something like that you defined one many-to-many relation (task
- site) through two bridge tables (site_task and issue).
You raise a valid point though, in that this is probably a poor way to
design such a database. Maybe I should only have one site_task table
to link the sites and tasks tables, and then have appropriate fields
within that single table to signify whether the entry is linking the 2
tables for reason of an issue or a task.
http://files.andybev.com/schema.png
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
Re: [Dbix-class] Adding an additional custom join parameter
On Wed, 2013-12-11 at 00:23 +0900, Hailin Hu wrote:
Well, can you try this way?
Have join started from site_task, like this
$site_task-search(
...,
join = ['site', 'task', 'issue'],
)
The relationships may be like this?
_SITE_TASK_-belongs_to('site' = 'Site');
_SITE_TASK_-belongs_to('task' = 'Task');
_SITE_TASK_-might_have('issue' = 'Issue', {'foreign.site_id' =
'me.site_id', 'foreign.task_id' = 'me.task_id'}, {'join_type' =
'LEFT'});
Reference:
http://search.cpan.org/~ribasushi/DBIx-Class-0.08250/lib/DBIx/Class/Relationship/Base.pm#add_relationship
Thanks. I did actually try something like that, but I ran into other
problems (I forget exactly what, but when I was running the queries it
wasn't generating quite the SQL I was expecting. I was going to post a
question about that, but posted the other one instead).
I'd be interested to know other people's opinions, but I'm beginning to
think that a database design with 2 different tables providing a
many-to-many relationship is a bad idea anyway, so I think I'll see if I
can design it differently.
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
Re: [Dbix-class] Adding an additional custom join parameter
On Tue, 2013-12-10 at 14:13 +, Gavin Rogers wrote: You raise a valid point though, in that this is probably a poor way to design such a database. Maybe I should only have one site_task table to link the sites and tasks tables, and then have appropriate fields within that single table to signify whether the entry is linking the 2 tables for reason of an issue or a task. http://files.andybev.com/schema.png I can't see anything different in this png. Sorry, I just reposted the same link for context, as it disappeared when I trimmed the top-posting ;-) But I get what you're talking about... I have a very basic example of this. In my many_to_many table I have boolean values for PRIMARY_GROUP and AFFILIATION_GROUP. https://github.com/cessna-cowboy/UserDatabase/blob/master/password_propagation_schema.pdf [...] Great, thanks for that, good to see someone else's implementation of something similar. I'll revisit the design and see what I can come up with. 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
Re: [Dbix-class] Adding an additional custom join parameter
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
Re: [Dbix-class] Adding an additional custom join parameter
On Wed, 2013-12-11 at 05:54 +1100, neil.lunn wrote: 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. Believe me, I spent several hours doing exactly that, and my questions were based on what I read. So if I'm asking questions that are already answered, then it's not clear to me. I can only apologise for my misunderstanding. I post to mailing lists as a last resort only. As a result, I've had some good suggestions for different ways of doing it, which I'll be looking at - thanks to all those folks. 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. As per my previous posts, I'll redesign my database, based on the previous helpful suggestions. Thank you, 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
