Conditional many to many relationships with Class::DBI

BigLug has asked for the wisdom of the Perl Monks concerning the following question:

I'm using Class::DBI for the first time for a project I'm working on at the moment. I've tinkered in the past but I have a new question for you. One that I can't find in the docs.

I have a many-to-many relationship. The docs explain how to do this and it makes some sense. However, when I'm not using Class::DBI it's easy to created a conditional many-to-many relationship. One that has an expiry. Consider this data:

 User | Group | Expires
------+-------+------------
 1    | 1     | 2004-11-11
 2    | 1     | 2005-01-01
 3    | 1     | 2003-12-31
 3    | 2     | 2005-01-01

SELECT User FROM Subscription WHERE Group = 1 AND Expires > now();
# 1, 2

SELECT Group FROM Subscription WHERE User = 3 AND Expires > now();
# 2
[download]

In an ordinary many-to-many, user 1 is in group 1, user 2 is in group 1 and user 3 is in both 1 and 2. However in this case user 3 should not be in group 1. His membership in that group has expired.

Wise monks, using Class::DBI how would I make that happen?

Note 1: There's also a live date -- the date at which point the subscription starts. However I felt that once I get the expiry worked out, the start date will be easy.

Note 2: When creating a new subscription, the old one should not be overwritten. Should User 3 become a member of group 1 at some later stage, then it would be a new record in the subscription table. This will allow historic queries like "Who was in group 1 on 2004-01-01?"

Cheers!
Rick
If this is a root node: Before responding, please ensure your clue bit is set.
If this is a reply: This is a discussion group, not a helpdesk ... If the discussion happens to answer a question you've asked, that's incidental.

Comment on Conditional many to many relationships with Class::DBI Download Code

Replies are listed 'Best First'.
Re: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 01, 2004 at 13:49 UTC
I suspect this started as a standard XREF table between User and Group. Then, you had the new requirement that users can change groups and that change needs to be tracked. There are a few ways to solve the problem. The first is to maintain a history table that tracks all the changes and your main table only has the most current data. This, however, doesn't really allow you to do reports backwards in time. No good. The second solution is to change your thinking. This isn't a many-to-many relationship - it's a many-to-many-to-many relationship. Your primary key has shifted from (User, Group) to (User, Group, LegalDateRange), and you need to change your thinking based on that shift. I don't know how to solve the CDBI issue as I've never really used it. However, that is the root cause of the problem you're having. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^2: Conditional many to many relationships with Class::DBI by CountZero (Bishop) on Nov 01, 2004 at 19:41 UTC
The problem with Class:DBI is that it only supports single field keys and cannot work with multiple field keys. You would have to further normalize your tables so they all have single field keys to get it to work. I don't think it is worth the bother and it is better to add in your class some custom-written sql (`__PACKAGE__->set_sql(your sql here)`) which you can access by using the search_your_sql statement. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l]
Re: Conditional many to many relationships with Class::DBI by perrin (Chancellor) on Nov 01, 2004 at 14:19 UTC
It's right there in the docs: has_many with limiting.	[reply]
Re^2: Conditional many to many relationships with Class::DBI by CountZero (Bishop) on Nov 01, 2004 at 19:09 UTC
I don't think that will work: it only checks on equality and not on any other conditions. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re^3: Conditional many to many relationships with Class::DBI by perrin (Chancellor) on Nov 01, 2004 at 20:06 UTC
Oh, good point. I would probably just use a custom constructor with actual SQL then. Much more efficient than the many-to-many approach shown in the Class::DBI docs.	[reply]
Re: Conditional many to many relationships with Class::DBI by TedPride (Priest) on Nov 01, 2004 at 08:37 UTC
Well, you could theoretically return the expires field as well and test for expiration from your script. This should be reasonably efficient as long as you remove expired records from the table every now and then (which you should be doing anyway), but it's rather a klooge and there's hopefully a better way to do it.	[reply]
Re: Conditional many to many relationships with Class::DBI by Anonymous Monk on Nov 01, 2004 at 10:14 UTC
I'm not familiar with the concept of a 'conditional relationship', but it sounds like you want joins between your users, groups and subscriptions tables to exclude rows in subscriptions that have expires < now() by default. I know this isn't possible in Postgres, and I suspect it is incompatible with SQL in general. It would be an interesting feature though! Maybe you could update your data model and have two 'subscription' tables, one for current subs and one for expired subs? Back to Class::DBI though - you could: 1) create a new method User->current_groups which fiddles with the SQL in a way that is appropriate (hacky but easy) 2) create a new Class::DBI::Relationship subclass (HasManyConditional) to implement the behaviour you want (clean but tricky)	[reply]
Re^2: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 01, 2004 at 13:45 UTC
I know this isn't possible in Postgres, and I suspect it is incompatible with SQL in general. SQL has no problem with it, and neither does Postgres. However, the basic structure of most RDBMSes have issues with data that is versioned over time. I run into this all the time with data warehousing. The solution isn't an SQL solution - it's a database engine solution, but those aren't going to be easy to have done. The best solution that I've thought up is to modify the InnoDB engine to do three things: shift from versioning rows using a version number to using a date keep all old copies of a row around add a MySQL-specific SQL extension to allow queries to access old versions of rows I talked with Jeremy Zawodny back in June when I took his MySQL class and he said that, theoretically, it should work. But, he said that the issue was tuits and that it would come a lot faster if I had a few tuits to get it started. shrugs Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^3: Conditional many to many relationships with Class::DBI by cbraga (Pilgrim) on Nov 01, 2004 at 14:50 UTC
It isn't difficult to keep the versioning in the application level, and I believe that's the rigth thing to do. It isn't a database's job to version your data, only to store and retrieve it. It's extra work of course, but not much extra work. And you should use a real, transactioned RDMS of course to avoid inconsistencies ;) `ESC[78;89;13p ESC[110;121;13p`	[reply]
Re^4: Conditional many to many relationships with Class::DBI by dragonchild (Archbishop) on Nov 01, 2004 at 15:34 UTC
Re^5: Conditional many to many relationships with Class::DBI by cbraga (Pilgrim) on Nov 01, 2004 at 16:05 UTC
Some notes below your chosen depth have not been shown here
Re^5: Conditional many to many relationships with Class::DBI by mpeppler (Vicar) on Nov 02, 2004 at 07:28 UTC
Some notes below your chosen depth have not been shown here
Re^4: Conditional many to many relationships with Class::DBI by poqui (Deacon) on Nov 01, 2004 at 22:15 UTC
Re: Conditional many to many relationships with Class::DBI by demerphq (Chancellor) on Nov 02, 2004 at 08:48 UTC
I have to deal with large volumes of data of exactly this nature. In my shop we call them assignment records or dated records. Our records typically have a ($id,$keyfield,@other_data,$start_date,$end_date) structure where start_date and end_date are constrained to be in order (start_date<end_date), non null and via trigger based constraints records with the same $keyfield are guaranteed not to overlap in the DB (any update that results in an overlap is rejected by the trigger). We use a long distant date to represent the end_date of "open" assignments, and we state the rule that the start_date is inclusive and the end_date is exclusive (ie the start_date is part of the assignment, the end_date is not, it represents the first second after the assignment.) This structure allows a much more flexible way of working with this type of data than most of the alternatives I've seen (such as using null to represent open assignments, or to using only a single date column). For instance IMO the query to extract all assignments valid for the month of 2004-11 is not easy to write with the table you have, and probably wont be particularly fast if you do work it out. (This is an operation that is very common for the systems i work on.) With the start_date/end_date mechanism such a query is really very easy to write. I know this doesnt address your direct question: I'm not a fan or user of Class::DBI at all. I prefer hand rolling my own tools for things like this as I find for the type of database work I do that its both easier to maintain and easier to understand. OTOH I hope my experience with this type of database table is useful to you, especially as I think youll find Note 2 quite difficult to do, and I also wonder what youll do if a user is a member of a group for one month, not a member for the next and then member the following month (ie one/off/on). It seems to me like youll always have a time period where your assignments are wrong. --- demerphq	[reply]