1: <?php
  2: /**
  3:  * Magento
  4:  *
  5:  * NOTICE OF LICENSE
  6:  *
  7:  * This source file is subject to the Open Software License (OSL 3.0)
  8:  * that is bundled with this package in the file LICENSE.txt.
  9:  * It is also available through the world-wide-web at this URL:
 10:  * http://opensource.org/licenses/osl-3.0.php
 11:  * If you did not receive a copy of the license and are unable to
 12:  * obtain it through the world-wide-web, please send an email
 13:  * to license@magentocommerce.com so we can send you a copy immediately.
 14:  *
 15:  * DISCLAIMER
 16:  *
 17:  * Do not edit or add to this file if you wish to upgrade Magento to newer
 18:  * versions in the future. If you wish to customize Magento for your
 19:  * needs please refer to http://www.magentocommerce.com for more information.
 20:  *
 21:  * @category    Mage
 22:  * @package     Mage_CatalogSearch
 23:  * @copyright   Copyright (c) 2012 Magento Inc. (http://www.magentocommerce.com)
 24:  * @license     http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 25:  */
 26: 
 27: 
 28: /**
 29:  * Search collection
 30:  *
 31:  * @category    Mage
 32:  * @package     Mage_CatalogSearch
 33:  * @author      Magento Core Team <core@magentocommerce.com>
 34:  */
 35: class Mage_CatalogSearch_Model_Resource_Search_Collection extends Mage_Catalog_Model_Resource_Product_Collection
 36: {
 37:     /**
 38:      * Attribute collection
 39:      *
 40:      * @var array
 41:      */
 42:     protected $_attributesCollection;
 43: 
 44:     /**
 45:      * Search query
 46:      *
 47:      * @var string
 48:      */
 49:     protected $_searchQuery;
 50: 
 51:     /**
 52:      * Add search query filter
 53:      *
 54:      * @param string $query
 55:      * @return Mage_CatalogSearch_Model_Resource_Search_Collection
 56:      */
 57:     public function addSearchFilter($query)
 58:     {
 59:         $this->_searchQuery = $query;
 60:         $this->addFieldToFilter('entity_id', array('in'=>new Zend_Db_Expr($this->_getSearchEntityIdsSql($query))));
 61:         return $this;
 62:     }
 63: 
 64:     /**
 65:      * Retrieve collection of all attributes
 66:      *
 67:      * @return Varien_Data_Collection_Db
 68:      */
 69:     protected function _getAttributesCollection()
 70:     {
 71:         if (!$this->_attributesCollection) {
 72:             $this->_attributesCollection = Mage::getResourceModel('catalog/product_attribute_collection')
 73:                 ->load();
 74: 
 75:             foreach ($this->_attributesCollection as $attribute) {
 76:                 $attribute->setEntity($this->getEntity());
 77:             }
 78:         }
 79:         return $this->_attributesCollection;
 80:     }
 81: 
 82:     /**
 83:      * Check attribute is Text and is Searchable
 84:      *
 85:      * @param Mage_Catalog_Model_Entity_Attribute $attribute
 86:      * @return boolean
 87:      */
 88:     protected function _isAttributeTextAndSearchable($attribute)
 89:     {
 90:         if (($attribute->getIsSearchable()
 91:             && !in_array($attribute->getFrontendInput(), array('select', 'multiselect')))
 92:             && (in_array($attribute->getBackendType(), array('varchar', 'text'))
 93:                 || $attribute->getBackendType() == 'static')) {
 94:             return true;
 95:         }
 96:         return false;
 97:     }
 98: 
 99:     /**
100:      * Check attributes has options and searchable
101:      *
102:      * @param Mage_Catalog_Model_Entity_Attribute $attribute
103:      * @return boolean
104:      */
105:     protected function _hasAttributeOptionsAndSearchable($attribute)
106:     {
107:         if ($attribute->getIsSearchable()
108:             && in_array($attribute->getFrontendInput(), array('select', 'multiselect'))) {
109:             return true;
110:         }
111: 
112:         return false;
113:     }
114: 
115:     /**
116:      * Retrieve SQL for search entities
117:      *
118:      * @param unknown_type $query
119:      * @return string
120:      */
121:     protected function _getSearchEntityIdsSql($query)
122:     {
123:         $tables = array();
124:         $selects = array();
125: 
126:         /* @var $resHelper Mage_Core_Model_Resource_Helper_Abstract */
127:         $resHelper = Mage::getResourceHelper('core');
128:         $likeOptions = array('position' => 'any');
129: 
130:         /**
131:          * Collect tables and attribute ids of attributes with string values
132:          */
133:         foreach ($this->_getAttributesCollection() as $attribute) {
134:             /** @var Mage_Catalog_Model_Entity_Attribute $attribute */
135:             $attributeCode = $attribute->getAttributeCode();
136:             if ($this->_isAttributeTextAndSearchable($attribute)) {
137:                 $table = $attribute->getBackendTable();
138:                 if (!isset($tables[$table]) && $attribute->getBackendType() != 'static') {
139:                     $tables[$table] = array();
140:                 }
141: 
142:                 if ($attribute->getBackendType() == 'static') {
143:                     $selects[] = $this->getConnection()->select()
144:                         ->from($table, 'entity_id')
145:                         ->where($resHelper->getCILike($attributeCode, $this->_searchQuery, $likeOptions));
146:                 } else {
147:                     $tables[$table][] = $attribute->getId();
148:                 }
149:             }
150:         }
151: 
152:         $ifValueId = $this->getConnection()->getCheckSql('t2.value_id > 0', 't2.value', 't1.value');
153:         foreach ($tables as $table => $attributeIds) {
154:             $selects[] = $this->getConnection()->select()
155:                 ->from(array('t1' => $table), 'entity_id')
156:                 ->joinLeft(
157:                     array('t2' => $table),
158:                     $this->getConnection()->quoteInto(
159:                         't1.entity_id = t2.entity_id AND t1.attribute_id = t2.attribute_id AND t2.store_id = ?',
160:                         $this->getStoreId()),
161:                     array()
162:                 )
163:                 ->where('t1.attribute_id IN (?)', $attributeIds)
164:                 ->where('t1.store_id = ?', 0)
165:                 ->where($resHelper->getCILike($ifValueId, $this->_searchQuery, $likeOptions));
166:         }
167: 
168:         $sql = $this->_getSearchInOptionSql($query);
169:         if ($sql) {
170:             $selects[] = "SELECT * FROM ({$sql}) AS inoptionsql"; // inheritant unions may be inside
171:         }
172: 
173:         $sql = $this->getConnection()->select()->union($selects, Zend_Db_Select::SQL_UNION_ALL);
174:         return $sql;
175:     }
176: 
177:     /**
178:      * Retrieve SQL for search entities by option
179:      *
180:      * @param unknown_type $query
181:      * @return string
182:      */
183:     protected function _getSearchInOptionSql($query)
184:     {
185:         $attributeIds    = array();
186:         $attributeTables = array();
187:         $storeId = (int)$this->getStoreId();
188: 
189:         /**
190:          * Collect attributes with options
191:          */
192:         foreach ($this->_getAttributesCollection() as $attribute) {
193:             if ($this->_hasAttributeOptionsAndSearchable($attribute)) {
194:                 $attributeTables[$attribute->getFrontendInput()] = $attribute->getBackend()->getTable();
195:                 $attributeIds[] = $attribute->getId();
196:             }
197:         }
198:         if (empty($attributeIds)) {
199:             return false;
200:         }
201: 
202:         $resource = Mage::getSingleton('core/resource');
203:         $optionTable      = $resource->getTableName('eav/attribute_option');
204:         $optionValueTable = $resource->getTableName('eav/attribute_option_value');
205:         $attributesTable  = $resource->getTableName('eav/attribute');
206: 
207:         /**
208:          * Select option Ids
209:          */
210:         $resHelper = Mage::getResourceHelper('core');
211:         $ifStoreId = $this->getConnection()->getIfNullSql('s.store_id', 'd.store_id');
212:         $ifValue   = $this->getConnection()->getCheckSql('s.value_id > 0', 's.value', 'd.value');
213:         $select = $this->getConnection()->select()
214:             ->from(array('d'=>$optionValueTable),
215:                    array('option_id',
216:                          'o.attribute_id',
217:                          'store_id' => $ifStoreId,
218:                          'a.frontend_input'))
219:             ->joinLeft(array('s'=>$optionValueTable),
220:                 $this->getConnection()->quoteInto('s.option_id = d.option_id AND s.store_id=?', $storeId),
221:                 array())
222:             ->join(array('o'=>$optionTable),
223:                 'o.option_id=d.option_id',
224:                 array())
225:             ->join(array('a' => $attributesTable), 'o.attribute_id=a.attribute_id', array())
226:             ->where('d.store_id=0')
227:             ->where('o.attribute_id IN (?)', $attributeIds)
228:             ->where($resHelper->getCILike($ifValue, $this->_searchQuery, array('position' => 'any')));
229: 
230:         $options = $this->getConnection()->fetchAll($select);
231:         if (empty($options)) {
232:             return false;
233:         }
234: 
235:         // build selects of entity ids for specified options ids by frontend input
236:         $selects = array();
237:         foreach (array(
238:             'select'      => 'eq',
239:             'multiselect' => 'finset')
240:             as $frontendInput => $condition) {
241:             if (isset($attributeTables[$frontendInput])) {
242:                 $where = array();
243:                 foreach ($options as $option) {
244:                     if ($frontendInput === $option['frontend_input']) {
245:                         $findSet = $this->getConnection()
246:                             ->prepareSqlCondition('value', array($condition => $option['option_id']));
247:                         $whereCond = "(attribute_id=%d AND store_id=%d AND {$findSet})";
248:                         $where[] = sprintf($whereCond, $option['attribute_id'], $option['store_id']);
249:                     }
250:                 }
251:                 if ($where) {
252:                     $selects[$frontendInput] = (string)$this->getConnection()->select()
253:                         ->from($attributeTables[$frontendInput], 'entity_id')
254:                         ->where(implode(' OR ', $where));
255:                 }
256:             }
257:         }
258: 
259:         // search in catalogindex for products as part of configurable/grouped/bundle products (current store)
260:         $where = array();
261:         foreach ($options as $option) {
262:             $where[] = sprintf('(attribute_id=%d AND value=%d)', $option['attribute_id'], $option['option_id']);
263:         }
264:         if ($where) {
265:             $selects[] = (string)$this->getConnection()->select()
266:                 ->from($resource->getTableName('catalogindex/eav'), 'entity_id')
267:                 ->where(implode(' OR ', $where))
268:                 ->where("store_id={$storeId}");
269:         }
270:         $sql = $this->getConnection()->select()->union($selects, Zend_Db_Select::SQL_UNION_ALL);
271:         return $sql;
272:     }
273: }
274: