2017-11-20 05:27 PST

View Issue Details Jump to Notes ]
IDProjectCategoryView StatusLast Update
0000485v2.0 Release (Closed)[All Projects] Generalpublic2010-06-21 21:06
Reporteregemme 
Assigned Tocaseydk 
PrioritynormalSeveritytrivialReproducibilityhave not tried
StatusclosedResolutionfixed 
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

-Relationships
+Relationships

-Notes

~0001007

caseydk (administrator)

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 (administrator)

I just did some major cleanup on this in r1183. I think that covers most of it.. more to come.

~0001045

egemme (reporter)

As far as I see, core modules work fine. But, what did you do exactly with this relationship?

~0001049

caseydk (administrator)

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.
+Notes

-Issue History
Date Modified Username Field Change
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
+Issue History