Since updating my CiviCRM 4.7.30 on WordPress 4.9.5, I am getting "DB Error: No such field" when doing queries through Advanced Search or Search Builder. Some of the problems where when I was trying to use a Profile to view an Advanced Search and others were related to fields that I was trying to search by (my assumption is that they are fields that need lookups, but I haven't been able to narrow this down yet). Below is an example Search Builder query that I tried and the resulting error that was in the CiviCRM log file located in the /wp-content/uploads/civicrm/ConfigAndLog directory.
The query was to find Organizations that were Not In two designated groups shown in the image below:
Not sure if it helps but when I used the Back Key to get back to review my previous query the query screen looked like the following:
The related error in the CiviCRM log file was:
> Apr 27 10:25:32 [info] $Fatal Error Details = Array (
> [callback] => Array
> (
> [0] => CRM_Core_Error
> [1] => handle
> )
>
> [code] => -19
> [message] => DB Error: no such field
> [mode] => 16
> [debug_info] => SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type
> as `contact_sub_type`, contact_a.sort_name as `sort_name`,
> CONCAT_WS(',',
> GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
> GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
> )
> as groups FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact `civicrm_group_contact-43,47` ON (contact_a.id =
> `civicrm_group_contact-43,47`.contact_id AND
> `civicrm_group_contact-43,47`.status IN ('Added')) WHERE ( ( ( (
> `civicrm_group_contact-43,47`.group_id NOT IN ( 43,47 ) ) ) AND
> contact_a.contact_type = 'organization' ) ) AND
> (contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY
> UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown
> column 'civicrm_group_contact.status' in 'field list']
> [type] => DB_Error
> [user_info] => SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, contact_a.contact_sub_type
> as `contact_sub_type`, contact_a.sort_name as `sort_name`,
> CONCAT_WS(',',
> GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
> GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
> )
> as groups FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact `civicrm_group_contact-43,47` ON (contact_a.id =
> `civicrm_group_contact-43,47`.contact_id AND
> `civicrm_group_contact-43,47`.status IN ('Added')) WHERE ( ( ( (
> `civicrm_group_contact-43,47`.group_id NOT IN ( 43,47 ) ) ) AND
> contact_a.contact_type = 'organization' ) ) AND
> (contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY
> UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown
> column 'civicrm_group_contact.status' in 'field list']
> [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix=""
> info="SELECT contact_a.id as contact_id, contact_a.contact_type as
> `contact_type`, contact_a.contact_sub_type as `contact_sub_type`,
> contact_a.sort_name as `sort_name`,
> CONCAT_WS(',',
> GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')),
> GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id)
> )
> as groups FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact `civicrm_group_contact-43,47` ON (contact_a.id =
> `civicrm_group_contact-43,47`.contact_id AND
> `civicrm_group_contact-43,47`.status IN ('Added')) WHERE ( ( ( (
> `civicrm_group_contact-43,47`.group_id NOT IN ( 43,47 ) ) ) AND
> contact_a.contact_type = 'organization' ) ) AND
> (contact_a.is_deleted = 0) GROUP BY contact_a.id ORDER BY
> UPPER(LEFT(contact_a.sort_name, 1)) asc [nativecode=1054 ** Unknown
> column 'civicrm_group_contact.status' in 'field list']"] )
>
>
> Apr 27 10:25:32 [info] $backTrace = #0
> /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(232):
> CRM_Core_Error::backtrace("backTrace", TRUE)
> #1 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921):
> CRM_Core_Error::handle(Object(DB_Error))
> #2 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB.php(985):
> PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2),
> "SELECT contact_a.id as contact_id, contact_a.contact_type as
> `contact_type`, ...")
> #3 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575):
> DB_Error->__construct(-19, 16, (Array:2), "SELECT contact_a.id as
> contact_id, contact_a.contact_type as `contact_type`, ...")
> #4 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223):
> PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), "SELECT
> contact_a.id as contact_id, contact_a.contact_type as `contact_type`,
> ...", "DB_Error", TRUE)
> #5 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905):
> PEAR->__call("raiseError", (Array:7))
> #6 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933):
> DB_common->raiseError(-19, NULL, NULL, "SELECT contact_a.id as
> contact_id, contact_a.contact_type as `contact_type`, ...", "1054 **
> Unknown column 'civicrm_group_contact.status' in 'field list'")
> #7 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403):
> DB_mysqli->mysqliRaiseError()
> #8 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216):
> DB_mysqli->simpleQuery("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #9 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2446):
> DB_common->query("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #10 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1635):
> DB_DataObject->_query("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #11 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(392):
> DB_DataObject->query("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #12 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1348):
> CRM_Core_DAO->query("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...", TRUE)
> #13 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4942):
> CRM_Core_DAO::executeQuery("SELECT contact_a.id as contact_id,
> contact_a.contact_type as `contact_type`, ...")
> #14 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Contact/Selector.php(1181):
> CRM_Contact_BAO_Query->searchQuery(NULL, NULL, NULL, FALSE, FALSE,
> TRUE)
> #15 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(108):
> CRM_Contact_Selector->alphabetQuery()
> #16 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(136):
> CRM_Utils_PagerAToZ::getDynamicCharacters(Object(CRM_Contact_Selector),
> FALSE)
> #17 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(52):
> CRM_Utils_PagerAToZ::createLinks(Object(CRM_Contact_Selector), NULL,
> FALSE)
> #18 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Contact/Form/Search.php(839):
> CRM_Utils_PagerAToZ::getAToZBar(Object(CRM_Contact_Selector), NULL)
> #19 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Contact/Form/Search/Builder.php(414):
> CRM_Contact_Form_Search->postProcess()
> #20 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(447):
> CRM_Contact_Form_Search_Builder->postProcess()
> #21 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75):
> CRM_Core_Form->mainProcess()
> #22 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203):
> CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Contact_Form_Search_Builder),
> "refresh")
> #23 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103):
> HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Search_Builder), "refresh")
> #24 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(351):
> HTML_QuickForm_Page->handle("refresh")
> #25 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(309):
> CRM_Core_Controller->run((Array:4), (Array:0))
> #26 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(84):
> CRM_Core_Invoke::runItem((Array:13))
> #27 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52):
> CRM_Core_Invoke::_invoke((Array:4))
> #28 /home/coloradomaker/www/www/wp-content/plugins/civicrm/civicrm.php(1246):
> CRM_Core_Invoke::invoke((Array:4))
> #29 /home/coloradomaker/www/www/wp-includes/class-wp-hook.php(286): CiviCRM_For_WordPress->invoke("")
> #30 /home/coloradomaker/www/www/wp-includes/class-wp-hook.php(310): WP_Hook->apply_filters("", (Array:1))
> #31 /home/coloradomaker/www/www/wp-includes/plugin.php(453): WP_Hook->do_action((Array:1))
> #32 /home/coloradomaker/www/www/wp-admin/admin.php(224): do_action("toplevel_page_CiviCRM")