MantisBT - v2.0 Release (Closed)
View Issue Details
0000485v2.0 Release (Closed)[All Projects] Generalpublic2010-06-13 05:242010-06-21 21:06
Reporteregemme 
Assigned Tocaseydk 
PrioritynormalSeveritytrivialReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOSOS Version
Product Version 
Target VersionFixed in Versionv2.0.0 
Summary0000485: new contacts_methods table and zero to many relationships
DescriptionHi,

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.

TagsNo tags attached.
Attached Files

Notes
(0001007)
caseydk   
2010-06-13 12:00   
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.
(0001010)
caseydk   
2010-06-13 21:08   
I just did some major cleanup on this in r1183. I think that covers most of it.. more to come.
(0001045)
egemme   
2010-06-15 16:33   
As far as I see, core modules work fine. But, what did you do exactly with this relationship?
(0001049)
caseydk   
2010-06-15 18:13   
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.

Issue History
2010-06-13 05:24egemmeNew Issue
2010-06-13 12:00caseydkNote Added: 0001007
2010-06-13 21:08caseydkNote Added: 0001010
2010-06-13 21:08caseydkStatusnew => feedback
2010-06-15 16:33egemmeNote Added: 0001045
2010-06-15 18:13caseydkNote Added: 0001049
2010-06-15 18:13caseydkStatusfeedback => resolved
2010-06-15 18:13caseydkResolutionopen => fixed
2010-06-15 18:13caseydkAssigned To => caseydk
2010-06-21 21:06caseydkStatusresolved => closed
2010-06-21 21:06caseydkFixed in Version => v2.0