|View Issue Details|
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0000485||v2.0 Release (Closed)||[All Projects] General||public||2010-06-13 05:24||2010-06-21 21:06|
|Priority||normal||Severity||trivial||Reproducibility||have not tried|
|Target Version||Fixed in Version||v2.0.0|
|Summary||0000485: new contacts_methods table and zero to many relationships|
I'm concerned about how the contacts_methods have been implemented. At least in helpdesk because this is where I'm focused on at this time.
In zero to many relationships, using a where clause to segregate the appropriate leaf record e.g. contact_method = 'primary_phone', will turn any left join into an inner join if there is no primary phone for a contact. Therefore, either the query misses many entries or returns nothing.
In such case, I use to add a condition to the join to make it join only when all the conditions are met, otherwise fill the query fields with nulls. This is an ackward approach for SQL purists, but it delivers more performance with large leaf table since it doesn't have to join all the leaves for all branches but only the one needed:
left join contacts_methods cm on cm.contact_id = c.contact_id and cm.method_name = 'primary_phone'
It is ackward because people don't use to put litteral predicates in join conditions, and it doesn't work will all DB engines anyway. But it does with DB2 and MySQL.
If you don't like it, you must then turn around and make the where clause look like this:
where cm.contact_method = 'primary_phone' or cm.contact_method is null
Otherwise, everytime a contact doesn't have a primary phone, the queried record won't be selected since the join didn't fill any of contact_method values and left them null. The left join turns into an inner join.
I don't know the impact of this in all w2p, but in helpdesk, this caused a major break.
|Tags||No tags attached.|
|I've been exploring the same issue.. it turns out that my test data is *much* cleaner and more complete than I expected. I'm working on a solution now.|
|I just did some major cleanup on this in r1183. I think that covers most of it.. more to come.|
|As far as I see, core modules work fine. But, what did you do exactly with this relationship?|
I broke it into two steps.. sort of like a Lazy Loader pattern.
First, it does the query to get the contact itself. Then, once it needs specific contact methods - like primary email or primary phone - it tries to get those. If they're not set, it returns a zero length string. This should be marginally faster too.
Now I *did* leave the assumption that any User would have a valid email address in their email_primary. Since it's required for user creation, it seemed like a safe assumption.
|2010-06-13 05:24||egemme||New Issue|
|2010-06-13 12:00||caseydk||Note Added: 0001007|
|2010-06-13 21:08||caseydk||Note Added: 0001010|
|2010-06-13 21:08||caseydk||Status||new => feedback|
|2010-06-15 16:33||egemme||Note Added: 0001045|
|2010-06-15 18:13||caseydk||Note Added: 0001049|
|2010-06-15 18:13||caseydk||Status||feedback => resolved|
|2010-06-15 18:13||caseydk||Resolution||open => fixed|
|2010-06-15 18:13||caseydk||Assigned To||=> caseydk|
|2010-06-21 21:06||caseydk||Status||resolved => closed|
|2010-06-21 21:06||caseydk||Fixed in Version||=> v2.0|