Jump to:
| Project: | Sunlight Congressional Districts (with optional CiviCRM integration) |
| Version: | 6.x-1.x-dev |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | closed (fixed) |
Issue Summary
I've installed cd_sunlight and all seemed well. Whenever I go in & update a zip code it comes back with the CD just fine.
However I have a few thousand contacts already in the database. Almost all have lat & long set and none have CD.
I thought cd_sunlight would pick up these contacts & gradually fill in the CDs as the cron jobs run. But it never did seem to pick them up.
Looking at the code, the problem is that a key database query needs to be a right join rather than inner join.
The trouble in our case was that all the civicrm_address table has like 10,000 entries, all with lat/long fields populated. But our CD_SUNLIGHT_CIVICRM_CUSTOM_TABLE only has a few relatively new custom fields in it and the result is that the entire table has a few dozen entries.
So inner join of those two tables on "c.entity_id = a.contact_id" only yielded a few dozen entries.
What we really want is a right join--every entity_id from the civicrm_address table to be there whether or not it has a corresponding entry in the CD_SUNLIGHT_CIVICRM_CUSTOM_TABLE already.
At any rate, to make a long story short, the following code starting at about line 1953 of cd_sunlight.module does the trick:
// Hooks don't fire during CiviCRM cron scripts. Enqueue all contacts that have lat/long,
// but not a CD.
db_set_active('civicrm');
$res = db_query('SELECT a.contact_id '.
'FROM '. CD_SUNLIGHT_CIVICRM_CUSTOM_TABLE .' c '.
'RIGHT JOIN civicrm_address a '. //NOTE **RIGHT JOIN** HERE
'ON c.entity_id = a.contact_id '.
'WHERE ('.
'c.'. CD_SUNLIGHT_CIVICRM_CUSTOM_FIELD_CD .' IS NULL OR '.
'c.'. CD_SUNLIGHT_CIVICRM_CUSTOM_FIELD_CD .' = "" '.
') '.
'AND ('.
'a.geo_code_1 IS NOT NULL AND '.
'a.geo_code_1 <> ""'.
') AND '.
'a.country_id = '. CD_SUNLIGHT_CIVICRM_US_COUNTRY_ID .' AND '.
'a.state_province_id <> '. CD_SUNLIGHT_CIVICRM_NON_US_STATE_PROVINCE_ID .' AND ('.
'a.postal_code IS NOT NULL OR '.
'a.street_address IS NOT NULL '.
')'
);
db_set_active();
while ($contact = db_fetch_object($res)) {
cd_sunlight_contact_enqueue($contact->contact_id);
}
}
Comments
#1
OK, now a slightly different but related issue came up.
For some reason my civicrm_address table has a couple of entries with a blank contact_id. When cd_sunlight picks those up (via the new "right join" method, above) it ends up adding a contact_id with value "0" to the cd_sunlight_cron table.
When cd_sunlight tries to process that of course it doesn't work and so we get that "CiviCRM is doing strange things" message.
The solution is to cut out the blank, null, or zero contact_id entries from our right join. All together it looks like this (again around line 1953 of cd_sunlight.module):
// Hooks don't fire during CiviCRM cron scripts. Enqueue all contacts that have lat/long,
// but not a CD.
//bhugh, 1/18/2009, changed from inner join to right join below;
//fixes bug with not
//picking up contacts with lat/long but no CD entry at all.
db_set_active('civicrm');
$res = db_query('SELECT a.contact_id '.
'FROM '. CD_SUNLIGHT_CIVICRM_CUSTOM_TABLE .' c '.
'RIGHT JOIN civicrm_address a '.
'ON c.entity_id = a.contact_id '.
'WHERE ('.
'c.'. CD_SUNLIGHT_CIVICRM_CUSTOM_FIELD_CD .' IS NULL OR '.
'c.'. CD_SUNLIGHT_CIVICRM_CUSTOM_FIELD_CD .' = "" '.
') '.
'AND ('.
'a.geo_code_1 IS NOT NULL AND '.
'a.geo_code_1 <> ""'.
') AND '.
'a.country_id = '. CD_SUNLIGHT_CIVICRM_US_COUNTRY_ID .' AND '.
'a.state_province_id <> '. CD_SUNLIGHT_CIVICRM_NON_US_STATE_PROVINCE_ID .' AND ('.
'a.postal_code IS NOT NULL OR '.
'a.street_address IS NOT NULL )'.
//added to prevent those stray blank addresses from messing things up
'AND a.contact_id IS NOT NULL ' .
'AND a.contact_id <> "" ' .
'AND a.contact_id <> 0 '
);
db_set_active();
while ($contact = db_fetch_object($res)) {
cd_sunlight_contact_enqueue($contact->contact_id);
}
}
#2
Also, poking around a little further, it looks like those civicrm_address entries with blank contact_id are a feature of CiviCRM, not a bug. All those entries in my system seem to be from things like contributions made by someone not logged in as a user. So they're things like billing addresses that are associated with a contribution but not a particular contact. (At least so it appears to me--maybe there is a bug in that code as well! But I have about 97 of those in the civicrm_address table now, almost all billing addresses from payments.
#3
Good catch on the outer join. I've rearranged to use a left join to conform to coding standards. I'm pretty sure that your addresses without a contact_id indicate a bug, but I'll add the extra WHERE clause anyway.
P.S. This definitely didn't qualify as 'critical'
#4
Automatically closed -- issue fixed for 2 weeks with no activity.