/** * 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; }