DB Error 'No such field' when sorting Civi advanced search results by country
Open, LowestPublic
Actions

Assigned To

None

Authored By

	Ejegg
	Dec 2 2022, 2:03 AM

Description

Go to advanced search
search by name ('adam' gives plenty of results in the example data)
try to sort by country while tailing Civi log

When no address field is part of the search parameters and you try to sort the results by country, this shows up in the log:

$Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => 
INSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)
SELECT DISTINCT 'civicrm search CRMContactControllerSearch17zo40ot8i74g4o4gko0sk0wc0osw040owossgk008wc484s4w_9435', contact_a.id, contact_a.sort_name  FROM civicrm_contact contact_a   LEFT JOIN civicrm_email ON (contact_a.id 
= civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  WHERE  (  (  (  ( contact_a.sort_name LIKE '%adam%' ) OR  ( civicrm_email.email LIKE 
'%adam%' )  )  )  )  AND ( 1 ) AND (contact_a.is_deleted = 0)    ORDER BY `country` asc, `contact_a`.`id`  LIMIT 0, 500 [nativecode=1054 ** Unknown column 'civicrm_address.country_id' in 'on clause']
    [type] => DB_Error
   ...
)

Dec 02 01:56:07  [debug] $backTrace = #0 /srv/civi-sites/wmff/drupal/sites/all/modules/civicrm/CRM/Core/Error.php(954): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /srv/civi-sites/wmff/drupal/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(944): CRM_Core_Error::exceptionHandler(Object(DB_Error))

...

#14 /srv/civi-sites/wmff/drupal/sites/all/modules/civicrm/CRM/Core/PrevNextCache/Sql.php(43): CRM_Core_DAO::executeQuery("\nINSERT INTO civicrm_prevnext_cache (cachekey, entity_id1, data)\nSELECT DIS...", (Array:0), FALSE)
#15 /srv/civi-sites/wmff/drupal/sites/all/modules/civicrm/CRM/Contact/Selector.php(1057): CRM_Core_PrevNextCache_Sql->fillWithSql("civicrm search CRMContactControllerSearch17zo40ot8i74g4o4gko0sk0wc0osw040owos...", "SELECT DISTINCT 'civicrm search CRMContactControllerSearch17zo40ot8i74g4o4gko...")

Note that the sort appears to change in the UI anyway, but that may just be sorting the current page in JS.

Looks like it's missing a join on the address table. There is a similar-sounding issue filed against upstream ( https://lab.civicrm.org/dev/core/-/issues/3836 ) but the proposed fix for that one doesn't work here.

Event Timeline

Ejegg created this task.Dec 2 2022, 2:03 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptDec 2 2022, 2:03 AM

XenoRyet moved this task from Triage to DRI Backlog on the Fundraising-Backlog board.Dec 12 2022, 8:51 PM

XenoRyet added a project: Fundraising Tech - Chaos Crew.

@Ejegg it's possible we should decline this - there is some error handling in CiviCRM core for this - which means the only downside is log noise - but how frequent is is?

try {
  Civi::service('prevnext')->fillWithSql($cacheKey, $sql);
}
catch (\Exception $e) {
  if ($coreSearch) {
    // in the case of error, try rebuilding cache using full sql which is used for search selector display
    // this fixes the bugs reported in CRM-13996 & CRM-14438
    $this->rebuildPreNextCache($start, $end, $sort, $cacheKey);
  }
  else {
    CRM_Core_Error::deprecatedFunctionWarning('Custom searches should return sql capable of filling the prevnext cache.');
    // This will always show for CiviRules :-( as a) it orders by 'rule_label'
    // which is not available in the query & b) it uses contact not contact_a
    // as an alias.
    // CRM_Core_Session::setStatus(ts('Query Failed'));
    return;
  }
}

Looks like it's around once a day (28 times in the log file begun on Dec 1). Our users haven't mentioned this, it's just annoying log noise.

(Debating the need for this, setting prio appropriately)

Although I argued for low priority here I've been digging into this today - 2 reasons

as an upstream fix I don't need anyone on our side to review and
I found an earlier phab (hopefully I can find again & link) - which suggests that it has taken our time before this even if it is ultimately just a logging noise issue

XenoRyet removed a project: Fundraising Tech - Chaos Crew.Mar 21 2023, 1:22 AM

greg added a project: Fundraising Tech - Chaos Crew.Mar 31 2023, 6:54 PM

XenoRyet removed a project: Fundraising Tech - Chaos Crew.Mar 31 2023, 8:29 PM

Eileenmcnaughton moved this task from Backlog to UserRequests (misc) on the Wikimedia-Fundraising-CiviCRM board.May 2 2023, 12:22 AM

AKanji-WMF added a project: Fundraising Tech - Chaos Crew.Oct 3 2023, 7:50 PM

Just flagging this is a really tough one to solve in code that is hopefully gonna be replace with SearchKit in time

moving to holding pen based on Eileen's last comment

DB Error 'No such field' when sorting Civi advanced search results by countryOpen, LowestPublicActions

Description

Event Timeline

DB Error 'No such field' when sorting Civi advanced search results by country
Open, LowestPublic
Actions