/**
* Build a query based on the given options to count the number of products.
*
* @param array $arrOptions The options array
*
* @return string
*/
protected static function buildCountQuery(array $arrOptions)
{
$hasTranslations = static::countTranslatedProducts() > 0;
$hasVariants = ProductType::countByVariants() > 0;
$arrJoins = array();
$arrFields = array($arrOptions['table'] . ".*", "'" . str_replace('-', '_', $GLOBALS['TL_LANGUAGE']) . "' AS language");
if ($hasVariants) {
$arrFields[] = sprintf("IF(%s.pid>0, parent.type, %s.type) AS type", $arrOptions['table'], $arrOptions['table']);
}
if ($hasTranslations) {
foreach (Attribute::getMultilingualFields() as $attribute) {
$arrFields[] = "IFNULL(translation.{$attribute}, " . $arrOptions['table'] . ".{$attribute}) AS {$attribute}";
}
}
$arrJoins[] = sprintf(" LEFT OUTER JOIN %s c ON %s.id=c.pid", ProductCategory::getTable(), $arrOptions['table']);
if ($hasTranslations) {
$arrJoins[] = sprintf(" LEFT OUTER JOIN %s translation ON %s.id=translation.pid AND translation.language='%s'", $arrOptions['table'], $arrOptions['table'], str_replace('-', '_', $GLOBALS['TL_LANGUAGE']));
}
if ($hasVariants) {
$arrJoins[] = sprintf(" LEFT OUTER JOIN %s parent ON %s.pid=parent.id", $arrOptions['table'], $arrOptions['table']);
}
// Generate the query
$strWhere = '';
$strQuery = "\n SELECT\n " . implode(', ', $arrFields) . ",\n COUNT(DISTINCT " . $arrOptions['table'] . ".id) AS count\n FROM " . $arrOptions['table'] . implode("", $arrJoins);
// Where condition
if (!empty($arrOptions['column'])) {
if (!is_array($arrOptions['column'])) {
$arrOptions['column'] = array($arrOptions['table'] . '.' . $arrOptions['column'] . '=?');
}
$strWhere = " AND " . implode(" AND ", $arrOptions['column']);
}
// The model must never find a language record
$strQuery .= " WHERE {$arrOptions['table']}.language=''" . $strWhere;
// Group by
if ($arrOptions['group'] !== null) {
$strQuery .= " GROUP BY " . $arrOptions['group'];
}
return $strQuery;
}
/**
* Generate query string for native filters
*
* @param array $arrFilters
*/
private function buildSqlFilters(array $arrFilters)
{
$strWhere = '';
$arrWhere = array();
$arrValues = array();
$arrGroups = array();
// Initiate native SQL filtering
/** @var \Isotope\RequestCache\Filter $objFilter */
foreach ($arrFilters as $k => $objFilter) {
if ($objFilter->hasGroup() && $arrGroups[$objFilter->getGroup()] !== false) {
if ($objFilter->isDynamicAttribute()) {
$arrGroups[$objFilter->getGroup()] = false;
} else {
$arrGroups[$objFilter->getGroup()][] = $k;
}
} elseif (!$objFilter->hasGroup() && !$objFilter->isDynamicAttribute()) {
$arrWhere[] = $objFilter->sqlWhere();
$arrValues[] = $objFilter->sqlValue();
unset($arrFilters[$k]);
}
}
if (!empty($arrGroups)) {
foreach ($arrGroups as $arrGroup) {
$arrGroupWhere = array();
// Skip dynamic attributes
if (false === $arrGroup) {
continue;
}
foreach ($arrGroup as $k) {
$objFilter = $arrFilters[$k];
$arrGroupWhere[] = $objFilter->sqlWhere();
$arrValues[] = $objFilter->sqlValue();
unset($arrFilters[$k]);
}
$arrWhere[] = '(' . implode(' OR ', $arrGroupWhere) . ')';
}
}
if (!empty($arrWhere)) {
$strWhere = implode(' AND ', $arrWhere);
if (ProductType::countByVariants() > 0) {
$time = \Date::floorToMinute();
$t = Product::getTable();
$protected = '';
if (BE_USER_LOGGED_IN === true) {
$protected = "\n AND {$t}.published='1'\n AND ({$t}.start='' OR {$t}.start<'{$time}')\n AND ({$t}.stop='' OR {$t}.stop>'" . ($time + 60) . "')";
}
$strWhere = "\n (\n ({$strWhere})\n OR {$t}.id IN (\n SELECT {$t}.pid\n FROM tl_iso_product AS {$t}\n WHERE {$t}.language='' AND " . implode(' AND ', $arrWhere) . "\n {$protected}\n )\n OR {$t}.pid IN (\n SELECT {$t}.id\n FROM tl_iso_product AS {$t}\n WHERE {$t}.language='' AND " . implode(' AND ', $arrWhere) . "\n {$protected}\n )\n )\n ";
$arrValues = array_merge($arrValues, $arrValues, $arrValues);
}
}
$this->filters = $arrFilters;
$this->sqlWhere = $strWhere;
$this->sqlValues = $arrValues;
}
