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 . ')';
}
