/** * Join customers * * @return $this */ protected function _joinCustomers() { /** @var $adapter \Magento\Framework\DB\Adapter\AdapterInterface */ $adapter = $this->getConnection(); /** @var $firstnameAttr \Magento\Eav\Model\Entity\Attribute */ $firstnameAttr = $this->_customerResource->getAttribute('firstname'); /** @var $lastnameAttr \Magento\Eav\Model\Entity\Attribute */ $lastnameAttr = $this->_customerResource->getAttribute('lastname'); $firstnameCondition = array('table_customer_firstname.entity_id = detail.customer_id'); if ($firstnameAttr->getBackend()->isStatic()) { $firstnameField = 'firstname'; } else { $firstnameField = 'value'; $firstnameCondition[] = $adapter->quoteInto('table_customer_firstname.attribute_id = ?', (int) $firstnameAttr->getAttributeId()); } $this->getSelect()->joinInner(array('table_customer_firstname' => $firstnameAttr->getBackend()->getTable()), implode(' AND ', $firstnameCondition), array()); $lastnameCondition = array('table_customer_lastname.entity_id = detail.customer_id'); if ($lastnameAttr->getBackend()->isStatic()) { $lastnameField = 'lastname'; } else { $lastnameField = 'value'; $lastnameCondition[] = $adapter->quoteInto('table_customer_lastname.attribute_id = ?', (int) $lastnameAttr->getAttributeId()); } //Prepare fullname field result $customerFullname = $adapter->getConcatSql(array("table_customer_firstname.{$firstnameField}", "table_customer_lastname.{$lastnameField}"), ' '); $this->getSelect()->reset(\Zend_Db_Select::COLUMNS)->joinInner(array('table_customer_lastname' => $lastnameAttr->getBackend()->getTable()), implode(' AND ', $lastnameCondition), array())->columns(array('customer_id' => 'detail.customer_id', 'customer_name' => $customerFullname, 'review_cnt' => 'COUNT(main_table.review_id)'))->group('detail.customer_id'); return $this; }
/** * Add customer data * * @param unknown_type $filter * @return $this */ public function addCustomerData($filter = null) { $attrFirstname = $this->_customerResource->getAttribute('firstname'); $attrFirstnameId = (int) $attrFirstname->getAttributeId(); $attrFirstnameTableName = $attrFirstname->getBackend()->getTable(); $attrLastname = $this->_customerResource->getAttribute('lastname'); $attrLastnameId = (int) $attrLastname->getAttributeId(); $attrLastnameTableName = $attrLastname->getBackend()->getTable(); $attrEmail = $this->_customerResource->getAttribute('email'); $attrEmailTableName = $attrEmail->getBackend()->getTable(); $adapter = $this->getSelect()->getAdapter(); $customerName = $adapter->getConcatSql(array('cust_fname.value', 'cust_lname.value'), ' '); $this->getSelect()->joinInner(array('cust_email' => $attrEmailTableName), 'cust_email.entity_id = main_table.customer_id', array('email' => 'cust_email.email'))->joinInner(array('cust_fname' => $attrFirstnameTableName), implode(' AND ', array('cust_fname.entity_id = main_table.customer_id', $adapter->quoteInto('cust_fname.attribute_id = ?', (int) $attrFirstnameId))), array('firstname' => 'cust_fname.value'))->joinInner(array('cust_lname' => $attrLastnameTableName), implode(' AND ', array('cust_lname.entity_id = main_table.customer_id', $adapter->quoteInto('cust_lname.attribute_id = ?', (int) $attrLastnameId))), array('lastname' => 'cust_lname.value', 'customer_name' => $customerName)); $this->_joinedFields['customer_name'] = $customerName; $this->_joinedFields['email'] = 'cust_email.email'; if ($filter) { if (isset($filter['customer_name'])) { $likeExpr = '%' . $filter['customer_name'] . '%'; $this->getSelect()->where($this->_joinedFields['customer_name'] . ' LIKE ?', $likeExpr); } if (isset($filter['email'])) { $likeExpr = '%' . $filter['email'] . '%'; $this->getSelect()->where($this->_joinedFields['email'] . ' LIKE ?', $likeExpr); } } return $this; }
/** * @param \Magento\Framework\DB\Select $select * @return \Magento\Framework\DB\Select */ protected function getCustomerNames($select) { $attrFirstname = $this->_customerResource->getAttribute('firstname'); $attrFirstnameId = (int) $attrFirstname->getAttributeId(); $attrFirstnameTableName = $attrFirstname->getBackend()->getTable(); $attrLastname = $this->_customerResource->getAttribute('lastname'); $attrLastnameId = (int) $attrLastname->getAttributeId(); $attrLastnameTableName = $attrLastname->getBackend()->getTable(); $select->joinInner(['cust_fname' => $attrFirstnameTableName], 'customer.entity_id = cust_fname.entity_id', ['firstname' => 'cust_fname.value'])->joinInner(['cust_lname' => $attrLastnameTableName], 'customer.entity_id = cust_lname.entity_id', ['lastname' => 'cust_lname.value'])->where('cust_fname.attribute_id = ?', (int) $attrFirstnameId)->where('cust_lname.attribute_id = ?', (int) $attrLastnameId); return $select; }