web2project: web-based project management
Main | My View | View Issues | Change Log | Roadmap | Docs
  

Viewing Issue Simple Details [ Jump to Notes ] [ View Advanced ] [ Issue History ] [ Print ]
ID Category Severity Reproducibility Date Submitted Last Update
0000485 [v2.0 Release (Current)] Core Infrastructure trivial have not tried 2010-06-13 07:24 2010-06-21 23:06
Reporter egemme View Status public  
Assigned To caseydk
Priority normal Resolution fixed  
Status closed   Product Version
Summary 0000485: new contacts_methods table and zero to many relationships
Description Hi,

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.
Additional Information
Tags No tags attached.
Attached Files

- Relationships

-  Notes
(0001007)
caseydk (administrator)
2010-06-13 14: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 (administrator)
2010-06-13 23:08

 I just did some major cleanup on this in r1183. I think that covers most of it.. more to come.
(0001045)
egemme (reporter)
2010-06-15 18:33

 As far as I see, core modules work fine. But, what did you do exactly with this relationship?
(0001049)
caseydk (administrator)
2010-06-15 20: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
Date Modified Username Field Change
2010-06-13 07:24 egemme New Issue
2010-06-13 14:00 caseydk Note Added: 0001007
2010-06-13 23:08 caseydk Note Added: 0001010
2010-06-13 23:08 caseydk Status new => feedback
2010-06-15 18:33 egemme Note Added: 0001045
2010-06-15 20:13 caseydk Note Added: 0001049
2010-06-15 20:13 caseydk Status feedback => resolved
2010-06-15 20:13 caseydk Resolution open => fixed
2010-06-15 20:13 caseydk Assigned To => caseydk
2010-06-21 23:06 caseydk Status resolved => closed
2010-06-21 23:06 caseydk Fixed in Version => v2.0

Mantis 1.1.8[^]
Copyright © 2000 - 2009 Mantis Group
Powered by Mantis Bugtracker