/** * Add Join with option value for collection select * * @param \Magento\Eav\Model\Entity\Collection\AbstractCollection $collection * @param \Magento\Eav\Model\Entity\Attribute $attribute * @param \Zend_Db_Expr $valueExpr * @return $this */ public function addOptionValueToCollection($collection, $attribute, $valueExpr) { $adapter = $this->_getReadAdapter(); $attributeCode = $attribute->getAttributeCode(); $optionTable1 = $attributeCode . '_option_value_t1'; $optionTable2 = $attributeCode . '_option_value_t2'; $tableJoinCond1 = "{$optionTable1}.option_id={$valueExpr} AND {$optionTable1}.store_id=0"; $tableJoinCond2 = $adapter->quoteInto("{$optionTable2}.option_id={$valueExpr} AND {$optionTable2}.store_id=?", $collection->getStoreId()); $valueExpr = $adapter->getCheckSql("{$optionTable2}.value_id IS NULL", "{$optionTable1}.value", "{$optionTable2}.value"); $collection->getSelect()->joinLeft([$optionTable1 => $this->getTable('eav_attribute_option_value')], $tableJoinCond1, [])->joinLeft([$optionTable2 => $this->getTable('eav_attribute_option_value')], $tableJoinCond2, [$attributeCode => $valueExpr]); return $this; }
/** * Add Value Sort To Collection Select * * @param \Magento\Eav\Model\Entity\Collection\AbstractCollection $collection * @param string $dir * * @return $this */ public function addValueSortToCollection($collection, $dir = \Magento\Framework\DB\Select::SQL_ASC) { $valueTable1 = $this->getAttribute()->getAttributeCode() . '_t1'; $valueTable2 = $this->getAttribute()->getAttributeCode() . '_t2'; $collection->getSelect()->joinLeft([$valueTable1 => $this->getAttribute()->getBackend()->getTable()], "e.entity_id={$valueTable1}.entity_id" . " AND {$valueTable1}.attribute_id='{$this->getAttribute()->getId()}'" . " AND {$valueTable1}.store_id=0", [])->joinLeft([$valueTable2 => $this->getAttribute()->getBackend()->getTable()], "e.entity_id={$valueTable2}.entity_id" . " AND {$valueTable2}.attribute_id='{$this->getAttribute()->getId()}'" . " AND {$valueTable2}.store_id='{$collection->getStoreId()}'", []); $valueExpr = $collection->getSelect()->getConnection()->getCheckSql("{$valueTable2}.value_id > 0", "{$valueTable2}.value", "{$valueTable1}.value"); $this->_attrOptionFactory->create()->addOptionValueToCollection($collection, $this->getAttribute(), $valueExpr); $collection->getSelect()->order("{$this->getAttribute()->getAttributeCode()} {$dir}"); return $this; }
/** * Add Value Sort To Collection Select * * @param \Magento\Eav\Model\Entity\Collection\AbstractCollection $collection * @param string $dir direction * @return $this */ public function addValueSortToCollection($collection, $dir = 'asc') { $attributeCode = $this->getAttribute()->getAttributeCode(); $attributeId = $this->getAttribute()->getId(); $attributeTable = $this->getAttribute()->getBackend()->getTable(); $linkField = $this->getAttribute()->getEntity()->getLinkField(); if ($this->getAttribute()->isScopeGlobal()) { $tableName = $attributeCode . '_t'; $collection->getSelect()->joinLeft([$tableName => $attributeTable], "e.{$linkField}={$tableName}.{$linkField}" . " AND {$tableName}.attribute_id='{$attributeId}'" . " AND {$tableName}.store_id='0'", []); $valueExpr = $tableName . '.value'; } else { $valueTable1 = $attributeCode . '_t1'; $valueTable2 = $attributeCode . '_t2'; $collection->getSelect()->joinLeft([$valueTable1 => $attributeTable], "e.{$linkField}={$valueTable1}.{$linkField}" . " AND {$valueTable1}.attribute_id='{$attributeId}'" . " AND {$valueTable1}.store_id='0'", [])->joinLeft([$valueTable2 => $attributeTable], "e.{$linkField}={$valueTable2}.{$linkField}" . " AND {$valueTable2}.attribute_id='{$attributeId}'" . " AND {$valueTable2}.store_id='{$collection->getStoreId()}'", []); $valueExpr = $collection->getConnection()->getCheckSql($valueTable2 . '.value_id > 0', $valueTable2 . '.value', $valueTable1 . '.value'); } $collection->getSelect()->order($valueExpr . ' ' . $dir); return $this; }
/** * Add Value Sort To Collection Select * * @param \Magento\Eav\Model\Entity\Collection\AbstractCollection $collection * @param string $dir * * @return \Magento\Eav\Model\Entity\Attribute\Source\Boolean */ public function addValueSortToCollection($collection, $dir = \Magento\Framework\DB\Select::SQL_ASC) { $attributeCode = $this->getAttribute()->getAttributeCode(); $attributeId = $this->getAttribute()->getId(); $attributeTable = $this->getAttribute()->getBackend()->getTable(); if ($this->getAttribute()->isScopeGlobal()) { $tableName = $attributeCode . '_t'; $collection->getSelect()->joinLeft(array($tableName => $attributeTable), "e.entity_id={$tableName}.entity_id" . " AND {$tableName}.attribute_id='{$attributeId}'" . " AND {$tableName}.store_id='0'", array()); $valueExpr = $tableName . '.value'; } else { $valueTable1 = $attributeCode . '_t1'; $valueTable2 = $attributeCode . '_t2'; $collection->getSelect()->joinLeft(array($valueTable1 => $attributeTable), "e.entity_id={$valueTable1}.entity_id" . " AND {$valueTable1}.attribute_id='{$attributeId}'" . " AND {$valueTable1}.store_id='0'", array())->joinLeft(array($valueTable2 => $attributeTable), "e.entity_id={$valueTable2}.entity_id" . " AND {$valueTable2}.attribute_id='{$attributeId}'" . " AND {$valueTable2}.store_id='{$collection->getStoreId()}'", array()); $valueExpr = $collection->getConnection()->getCheckSql($valueTable2 . '.value_id > 0', $valueTable2 . '.value', $valueTable1 . '.value'); } $collection->getSelect()->order($valueExpr . ' ' . $dir); return $this; }