CiviCRM Community Forums (archive)

In CiviCRM 3.4.8, if one clicks on the 'State' column header in the search results, production of a sorted version of the results fails with a DB error because the generated SQL query has an ORDER BY clause referencing a column named state_province - however the name of the State or Province is actually found in the 'name' column of the civicrm_state_province table.

I believe the problem originates in CRM/Contact/Selector.php but have been unable to confirm this as I haven't been able to get useful debugging output from that file. However it's been a very long day and it could well be that I'm logging into quite the wrong server.

The problem is reproducible on the 3.4.8 demo site... I'm very surprised no-one has encountered it before.

To reproduce, from the menu select Search->Advanced Search
there is no need to enter criteria, just click 'Search'
when the list of results is displayed, click on the 'State' column header
BOOM - fizz.

The error message, in all its glory, is:

Database Error Code: Unknown column 'state_province' in 'order clause', 1054
Additional Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] =>
INSERT INTO civicrm_prevnext_cache ( entity_table, entity_id1, entity_id2, cacheKey, data )
SELECT 'civicrm_contact', contact_a.id, contact_a.id, 'civicrm search 44d8f2ace5e4a4ad8ed2567ff896f444_8479', contact_a.display_name
  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  WHERE (contact_a.is_deleted = 0)   GROUP BY contact_a.id  ORDER BY state_province asc  [nativecode=1054 ** Unknown column 'state_province' in 'order clause']
    [type] => DB_Error
    [user_info] =>
INSERT INTO civicrm_prevnext_cache ( entity_table, entity_id1, entity_id2, cacheKey, data )
SELECT 'civicrm_contact', contact_a.id, contact_a.id, 'civicrm search 44d8f2ace5e4a4ad8ed2567ff896f444_8479', contact_a.display_name
  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  WHERE (contact_a.is_deleted = 0)   GROUP BY contact_a.id  ORDER BY state_province asc  [nativecode=1054 ** Unknown column 'state_province' in 'order clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="
INSERT INTO civicrm_prevnext_cache ( entity_table, entity_id1, entity_id2, cacheKey, data )
SELECT 'civicrm_contact', contact_a.id, contact_a.id, 'civicrm search 44d8f2ace5e4a4ad8ed2567ff896f444_8479', contact_a.display_name
  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  WHERE (contact_a.is_deleted = 0)   GROUP BY contact_a.id  ORDER BY state_province asc  [nativecode=1054 ** Unknown column 'state_province' in 'order clause']"]
)


Thanks lobo, that's good to know - however we are still on Drupal 6 for the next few months at least.

If you can point me to the fix in the 4.x code, I'd be willing to try and apply it in the 3.4 codebase if that would make sense.

That;s excellent news... standing ovation for Eileen & Tim!