static function getFilteredSearch(&$filter, $value, $return_sql = true) { $db = JFactory::getDBO(); // Check if field type supports advanced search $support = FlexicontentFields::getPropertySupport($filter->field_type, $filter->iscore); if (!$support->supportadvsearch && !$support->supportadvfilter) { return null; } $valueswhere = FlexicontentFields::createFilterValueMatchSQL($filter, $value, $is_full_text = 1, $is_search = 1); if (!$valueswhere) { return; } // Decide to require all values $display_filter_as = $filter->parameters->get('display_filter_as_s', 0); $require_all = count($value) > 1 && !in_array($display_filter_as, array(1, 2, 3)) ? $filter->parameters->get('filter_values_require_all', 0) : 0; $istext_input = $display_filter_as == 1 || $display_filter_as == 3; //$colname = $istext_input ? 'fs.search_index' : 'fs.value_id'; $colname = @$filter->isindexed && !$istext_input ? 'fs.value_id' : 'fs.search_index'; $valueswhere = str_replace('_v_', $colname, $valueswhere); // Get ALL items that have such values for the given field $query = "SELECT " . ($require_all ? 'fs.item_id' : 'DISTINCT fs.item_id') . " FROM #__flexicontent_advsearch_index AS fs" . " WHERE fs.field_id='" . $filter->id . "' " . $valueswhere; if ($require_all) { // Do not use distinct on column, it makes it is very slow, despite column having an index !! // e.g. HAVING COUNT(DISTINCT colname) = ... // Instead the field code should make sure that no duplicate values are saved in the DB !! $query .= ' GROUP BY fs.item_id ' . ' HAVING COUNT(*) >= ' . count($value); } if (!$return_sql) { //echo "<br>FlexicontentFields::getFiltered() ".$filter->name." appying query :<br>". $query."<br>\n"; $db->setQuery($query); $filtered = FLEXI_J16GE ? $db->loadColumn() : $db->loadResultArray(); if ($db->getErrorNum()) { JFactory::getApplication()->enqueueMessage(__FUNCTION__ . '(): SQL QUERY ERROR:<br/>' . nl2br($db->getErrorMsg()), 'error'); } return $filtered; } else { return ' AND i.id IN (' . $query . ')'; } }
static function getFilteredSearch(&$filter, $value, $return_sql = true) { $app = JFactory::getApplication(); $db = JFactory::getDBO(); // Check if field type supports advanced search $support = FlexicontentFields::getPropertySupport($filter->field_type, $filter->iscore); if (!$support->supportadvsearch && !$support->supportadvfilter) { return null; } // Decide to require all values $display_filter_as = $filter->parameters->get('display_filter_as_s', 0); $isDate = in_array($filter->field_type, array('date', 'created', 'modified')) || $filter->parameters->get('isdate', 0); $isRange = in_array($display_filter_as, array(2, 3, 8)); $require_all_param = $filter->parameters->get('filter_values_require_all', 0); $require_all = count($value) > 1 && !$isRange ? $require_all_param : 0; $istext_input = $display_filter_as == 1 || $display_filter_as == 3; $colname = @$filter->isindexed && !$istext_input || $isDate ? 'fs.value_id' : 'fs.search_index'; // Create where clause for matching the filter's values $valueswhere = FlexicontentFields::createFilterValueMatchSQL($filter, $value, $is_full_text = 1, $is_search = 1, $colname); if (!$valueswhere) { return; } $valueswhere = str_replace('_v_', $colname, $valueswhere); $field_tbl = 'flexicontent_advsearch_index_field_' . $filter->id; $query = 'SHOW TABLES LIKE "' . $app->getCfg('dbprefix') . $field_tbl . '"'; $db->setQuery($query); $tbl_exists = (bool) count($db->loadObjectList()); $field_tbl = $tbl_exists ? $field_tbl : 'flexicontent_advsearch_index'; // Get ALL items that have such values for the given field $query = 'SELECT ' . ($require_all ? 'fs.item_id' : 'DISTINCT fs.item_id') . ' FROM #__' . $field_tbl . ' AS fs' . ' WHERE fs.field_id=' . $filter->id . $valueswhere; if ($require_all) { // Do not use distinct on column, it makes it is very slow, despite column having an index !! // e.g. HAVING COUNT(DISTINCT colname) = ... // Instead the field code should make sure that no duplicate values are saved in the DB !! $query .= ' GROUP BY fs.item_id ' . ' HAVING COUNT(*) >= ' . count($value) . ' ORDER BY NULL'; // THIS should remove filesort in MySQL, and improve performance issue of REQUIRE ALL } //echo 'Filter ['. $filter->label .']: '. $query."<br/><br/>\n"; if (!$return_sql) { //echo "<br>GET FILTERED Items (helper func) -- [".$filter->name."] using in-query ids : ". $query."<br>\n"; $db->setQuery($query); $filtered = $db->loadColumn(); return $filtered; } else { if ($return_sql === 2) { static $iids_tblname = array(); if (!isset($iids_tblname[$filter->id])) { $iids_tblname[$filter->id] = 'fc_filter_iids_' . $filter->id; } $tmp_tbl = $iids_tblname[$filter->id]; try { // Use sub-query on temporary table $db->setQuery('CREATE TEMPORARY TABLE IF NOT EXISTS ' . $tmp_tbl . ' (id INT, KEY(`id`))'); $db->query(); $db->setQuery('TRUNCATE TABLE ' . $tmp_tbl); $db->query(); $db->setQuery('INSERT INTO ' . $tmp_tbl . ' ' . $query); $db->query(); $_query = $query; $query = 'SELECT id FROM ' . $tmp_tbl; //echo "<br/><br/>GET FILTERED Items (helper func) -- [".$filter->name."] using temporary table: ".$query." for :".$_query ." <br/><br/>"; /*$db->setQuery($query); $data = $db->loadObjectList(); echo "<pre>"; print_r($data); exit;*/ } catch (Exception $e) { // Ignore table creation error //if ($db->getErrorNum()) echo 'SQL QUERY ERROR:<br/>'.nl2br($db->getErrorMsg()); //echo "<br/><br/>GET FILTERED Items (helper func) -- [".$filter->name."] using subquery: ".$query." <br/><br/>"; } } else { //echo "<br/><br/>GET FILTERED Items (helper func) -- [".$filter->name."] using subquery: ".$query." <br/><br/>"; } } return ' AND i.id IN (' . $query . ')'; }