/**
* Return select statement to load product data including multilingual fields
* @param array an array of columns
* @return string
*/
protected static function buildFindQuery(array $arrOptions)
{
$objBase = new \DcaExtractor($arrOptions['table']);
$arrJoins = array();
$arrFields = array($arrOptions['table'] . ".*", "IF(" . $arrOptions['table'] . ".pid>0, parent.type, " . $arrOptions['table'] . ".type) AS type", "'" . str_replace('-', '_', $GLOBALS['TL_LANGUAGE']) . "' AS language");
foreach (Attribute::getMultilingualFields() as $attribute) {
$arrFields[] = "IFNULL(translation.{$attribute}, " . $arrOptions['table'] . ".{$attribute}) AS {$attribute}";
}
foreach (Attribute::getFetchFallbackFields() as $attribute) {
$arrFields[] = "{$arrOptions['table']}.{$attribute} AS {$attribute}_fallback";
}
$arrFields[] = "c.sorting";
$arrJoins[] = " LEFT OUTER JOIN " . \Isotope\Model\ProductCategory::getTable() . " c ON {$arrOptions['table']}.id=c.pid";
$arrJoins[] = " LEFT OUTER JOIN " . $arrOptions['table'] . " translation ON " . $arrOptions['table'] . ".id=translation.pid AND translation.language='" . str_replace('-', '_', $GLOBALS['TL_LANGUAGE']) . "'";
$arrJoins[] = " LEFT OUTER JOIN " . $arrOptions['table'] . " parent ON " . $arrOptions['table'] . ".pid=parent.id";
if ($objBase->hasRelations()) {
$intCount = 0;
foreach ($objBase->getRelations() as $strKey => $arrConfig) {
// Automatically join the single-relation records
if ($arrConfig['load'] == 'eager' || $arrOptions['eager']) {
if ($arrConfig['type'] == 'hasOne' || $arrConfig['type'] == 'belongsTo') {
if (is_array($arrOptions['joinAliases']) && ($key = array_search($arrConfig['table'], $arrOptions['joinAliases'])) !== false) {
$strJoinAlias = $key;
unset($arrOptions['joinAliases'][$key]);
} else {
++$intCount;
$strJoinAlias = 'j' . $intCount;
}
$objRelated = new \DcaExtractor($arrConfig['table']);
foreach (array_keys($objRelated->getFields()) as $strField) {
$arrFields[] = $strJoinAlias . '.' . $strField . ' AS ' . $strKey . '__' . $strField;
}
$arrJoins[] = " LEFT JOIN " . $arrConfig['table'] . " {$strJoinAlias} ON " . $arrOptions['table'] . "." . $strKey . "={$strJoinAlias}.id";
}
}
}
}
// Generate the query
$strQuery = "SELECT " . implode(', ', $arrFields) . " FROM " . $arrOptions['table'] . implode("", $arrJoins);
// Where condition
if (!is_array($arrOptions['column'])) {
$arrOptions['column'] = array($arrOptions['table'] . '.' . $arrOptions['column'] . '=?');
}
// The model must never find a language record
$strQuery .= " WHERE {$arrOptions['table']}.language='' AND " . implode(" AND ", $arrOptions['column']);
// Group by
if ($arrOptions['group'] !== null) {
$strQuery .= " GROUP BY " . $arrOptions['group'];
}
// Order by
if ($arrOptions['order'] !== null) {
$strQuery .= " ORDER BY " . $arrOptions['order'];
}
return $strQuery;
}
/**
* Check if filter attribute is dynamic (can't use SQL filter then)
* @return bool
*/
public function isMultilingualAttribute()
{
return in_array($this->arrConfig['attribute'], Attribute::getMultilingualFields());
}
/**
* 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;
}
