PHP ActiveRecordModel::getDataBySQL方法代码示例

 public function index()
 {
     // get filter to select manufacturers of active products only
     $rootCat = Category::getRootNode();
     $f = new ARSelectFilter();
     $productFilter = new ProductFilter($rootCat, $f);
     $ids = $counts = array();
     foreach (ActiveRecordModel::getDataBySQL('SELECT DISTINCT(manufacturerID), COUNT(*) AS cnt FROM Product ' . $f->createString() . ' GROUP BY manufacturerID') as $row) {
         $ids[] = $row['manufacturerID'];
         $counts[$row['manufacturerID']] = $row['cnt'];
     }
     $f = new ARSelectFilter(new InCond(new ARFieldHandle('Manufacturer', 'ID'), $ids));
     $f->mergeCondition(new NotEqualsCond(new ARFieldHandle('Manufacturer', 'name'), ''));
     $f->setOrder(new ARFieldHandle('Manufacturer', 'name'));
     $manufacturers = ActiveRecordModel::getRecordSetArray('Manufacturer', $f);
     foreach ($manufacturers as &$manufacturer) {
         $manufacturer['url'] = $this->getManufacturerFilterUrl($manufacturer);
     }
     $this->addBreadCrumb($this->translate('_manufacturers'), '');
     $response = new ActionResponse();
     $response->setReference('manufacturers', $manufacturers);
     $response->set('counts', $counts);
     $response->set('rootCat', $rootCat->toArray());
     return $response;
 }

 protected function getDownloadCounts($itemIDs)
 {
     if (!$itemIDs) {
         return array();
     }
     $sql = 'SELECT orderedItemID, SUM(timesDownloaded) AS cnt FROM OrderedFile WHERE orderedItemID IN (' . implode(',', $itemIDs) . ') GROUP BY orderedItemID';
     $out = array();
     foreach (ActiveRecordModel::getDataBySQL($sql) as $item) {
         $out[$item['orderedItemID']] = $item['cnt'];
     }
     return $out;
 }

 private function getRecipientCount($data)
 {
     $query = $this->getRecipientQuery($data, false);
     if (!$query) {
         return 0;
     }
     $query = 'SELECT COUNT(*) FROM (' . $query . ') AS recipCount';
     return array_shift(array_shift(ActiveRecordModel::getDataBySQL($query)));
 }

 public function getCountByManufacturers($includeAppliedFilters)
 {
     $selectFilter = $this->productFilter->getSelectFilter(!$includeAppliedFilters);
     $selectFilter->removeFieldList();
     $selectFilter->setLimit(0);
     $selectFilter->setOrder(new ARExpressionHandle('cnt'), 'DESC');
     $selectFilter->setGrouping(new ARFieldHandle('Product', 'manufacturerID'));
     $selectFilter->mergeHavingCondition(new MoreThanCond(new ARExpressionHandle('cnt'), 0));
     $selectFilter->mergeHavingCondition(new NotEqualsCond(new ARFieldHandle('Manufacturer', 'name'), ''));
     $selectFilter->resetOrder();
     $selectFilter->setOrder(new ARFieldHandle('Manufacturer', 'name'));
     $query = new ARSelectQueryBuilder();
     $query->includeTable('Product');
     $query->joinTable('Manufacturer', 'Product', 'ID', 'manufacturerID');
     $query->joinTable('Category', 'Product', 'ID', 'categoryID');
     $query->addField('COUNT(Product.manufacturerID)', null, 'cnt');
     $query->addField('ID', 'Manufacturer');
     $query->addField('name', 'Manufacturer');
     $query->setFilter($selectFilter);
     ActiveRecordModel::getApplication()->processInstancePlugins('manufacturerCountQuery', $query);
     $data = ActiveRecordModel::getDataBySQL($query->getPreparedStatement(ActiveRecord::getDBConnection()));
     return $data;
 }

    private static function fetchRawSpecificationData($class, $objectIDs, $fullSpecification = false)
    {
        if (!$objectIDs) {
            return array();
        }
        $fieldClass = call_user_func(array($class, 'getFieldClass'));
        $groupClass = $fieldClass . 'Group';
        $fieldColumn = call_user_func(array($fieldClass, 'getFieldIDColumnName'));
        $objectColumn = call_user_func(array($fieldClass, 'getObjectIDColumnName'));
        $stringClass = call_user_func(array($fieldClass, 'getStringValueClass'));
        $numericClass = call_user_func(array($fieldClass, 'getNumericValueClass'));
        $dateClass = call_user_func(array($fieldClass, 'getDateValueClass'));
        $valueItemClass = call_user_func(array($fieldClass, 'getSelectValueClass'));
        $valueClass = call_user_func(array($valueItemClass, 'getValueClass'));
        $valueColumn = call_user_func(array($valueItemClass, 'getValueIDColumnName'));
        $groupColumn = strtolower(substr($groupClass, 0, 1)) . substr($groupClass, 1) . 'ID';
        $cond = '
		LEFT JOIN
			' . $fieldClass . ' ON ' . $fieldColumn . ' = ' . $fieldClass . '.ID
		LEFT JOIN
			' . $groupClass . ' ON ' . $fieldClass . '.' . $groupColumn . ' = ' . $groupClass . '.ID
		WHERE
			' . $objectColumn . ' IN (' . implode(', ', $objectIDs) . ')' . ($fullSpecification ? '' : ' AND ' . $fieldClass . '.isDisplayedInList = 1');
        $group = $groupClass . '.position AS SpecFieldGroupPosition, ' . $groupClass . '.name AS SpecFieldGroupName, ';
        $query = '
		SELECT ' . $dateClass . '.*, NULL AS valueID, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* /* as valueID */ FROM ' . $dateClass . ' ' . $cond . '
		UNION
		SELECT ' . $stringClass . '.*, NULL, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* /* as valueID */ FROM ' . $stringClass . ' ' . $cond . '
		UNION
		SELECT ' . $numericClass . '.*, NULL, NULL AS specFieldValuePosition, ' . $group . $fieldClass . '.* /* as valueID */ FROM ' . $numericClass . ' ' . $cond . '
		UNION
		SELECT ' . $valueItemClass . '.' . $objectColumn . ', ' . $valueItemClass . '.' . $fieldColumn . ', ' . $valueClass . '.value, ' . $valueClass . '.ID, ' . $valueClass . '.position, null, null, ' . $group . $fieldClass . '.*
				 FROM ' . $valueItemClass . '
				 	LEFT JOIN ' . $valueClass . ' ON ' . $valueItemClass . '.' . $valueColumn . ' = ' . $valueClass . '.ID
				 ' . str_replace('ON ' . $fieldColumn, 'ON ' . $valueItemClass . '.' . $fieldColumn, $cond) . ' ORDER BY ' . $objectColumn . ', SpecFieldGroupPosition, position, specFieldValuePosition';
        return ActiveRecordModel::getDataBySQL($query);
    }

 public function autoComplete()
 {
     $f = new ARSelectFilter();
     $f->setLimit(20);
     $resp = array();
     $field = $this->request->get('field');
     if (in_array($field, array('sku', 'URL', 'keywords'))) {
         $c = new LikeCond(new ARFieldHandle('Product', $field), $this->request->get($field) . '%');
         $f->setCondition($c);
         $f->setOrder(new ARFieldHandle('Product', $field), 'ASC');
         $query = new ARSelectQueryBuilder();
         $query->setFilter($f);
         $query->includeTable('Product');
         $query->addField('DISTINCT(Product.' . $field . ')');
         $results = ActiveRecordModel::getDataBySQL($query->createString());
         foreach ($results as $value) {
             $resp[] = $value[$field];
         }
     } else {
         if ('name' == $field) {
             $c = new LikeCond(new ARFieldHandle('Product', $field), '%:"' . $this->request->get($field) . '%');
             $f->setCondition($c);
             $locale = $this->locale->getLocaleCode();
             $langCond = new LikeCond(Product::getLangSearchHandle(new ARFieldHandle('Product', 'name'), $locale), $this->request->get($field) . '%');
             $c->addAND($langCond);
             $f->setOrder(Product::getLangSearchHandle(new ARFieldHandle('Product', 'name'), $locale), 'ASC');
             $results = ActiveRecordModel::getRecordSet('Product', $f);
             foreach ($results as $value) {
                 $resp[$value->getValueByLang('name', $locale, Product::NO_DEFAULT_VALUE)] = true;
             }
             $resp = array_keys($resp);
         } else {
             if ('specField_' == substr($field, 0, 10)) {
                 list($foo, $id) = explode('_', $field);
                 $handle = new ARFieldHandle('SpecificationStringValue', 'value');
                 $locale = $this->locale->getLocaleCode();
                 $searchHandle = MultiLingualObject::getLangSearchHandle($handle, $locale);
                 $f->setCondition(new EqualsCond(new ARFieldHandle('SpecificationStringValue', 'specFieldID'), $id));
                 $f->mergeCondition(new LikeCond($handle, '%:"' . $this->request->get($field) . '%'));
                 $f->mergeCondition(new LikeCond($searchHandle, $this->request->get($field) . '%'));
                 $f->setOrder($searchHandle, 'ASC');
                 $results = ActiveRecordModel::getRecordSet('SpecificationStringValue', $f);
                 foreach ($results as $value) {
                     $resp[$value->getValueByLang('value', $locale, Product::NO_DEFAULT_VALUE)] = true;
                 }
                 $resp = array_keys($resp);
             }
         }
     }
     return new AutoCompleteResponse($resp);
 }

 private function getOrderArray(ARSet $orders)
 {
     $orderArray = $orders->toArray();
     $ids = array();
     foreach ($orderArray as $key => $order) {
         $ids[$order['ID']] = $key;
     }
     ClassLoader::import('application.model.order.OrderNote');
     $f = new ARSelectFilter(new INCond(new ARFieldHandle('OrderNote', 'orderID'), empty($ids) ? array(-1) : array_keys($ids)));
     $f->mergeCondition(new EqualsCond(new ARFieldHandle('OrderNote', 'isAdmin'), 1));
     $f->mergeCondition(new EqualsCond(new ARFieldHandle('OrderNote', 'isRead'), 0));
     $f->setGrouping(new ARFieldHandle('OrderNote', 'orderID'));
     $query = new ARSelectQueryBuilder();
     $query->setFilter($f);
     $query->includeTable('OrderNote');
     $query->removeFieldList();
     $query->addField('COUNT(*)', null, 'cnt');
     $query->addField('orderID');
     foreach (ActiveRecordModel::getDataBySQL($query->getPreparedStatement(ActiveRecord::getDBConnection())) as $res) {
         $orderArray[$ids[$res['orderID']]]['unreadMessageCount'] = $res['cnt'];
     }
     return $orderArray;
 }

 public function executeTextReplaceInSnapshot(ClonedStore $store)
 {
     $replace = array();
     $f = select(eq(f('ClonedStoreRule.type'), ClonedStoreRule::TYPE_TEXT));
     $f->setOrder(f('ClonedStoreRule.ID'));
     foreach ($store->getRelatedRecordSet('ClonedStoreRule', $f) as $rule) {
         list($entity, $field) = explode('.', $rule->field->get());
         $ruleData = $rule->toArray();
         $ruleData['entity'] = $entity;
         $ruleData['field'] = $field;
         $replace[$entity][] = $ruleData;
     }
     echo '<span style="font-size: 2px;">';
     foreach ($replace as $table => $rules) {
         echo '<!--';
         var_dump($rules);
         echo '-->';
         flush();
         //exit;
         $this->flush($table);
         $this->flush($rules);
         if ('specField' == $table) {
             $table = 'SpecificationStringValue';
         }
         $extraFields = '';
         if (in_array($table, array('SpecificationStringValue', 'Product'))) {
             foreach (ActiveRecordModel::getDataBySQL('SELECT * FROM ' . $this->importDatabase . '.SpecField') as $field) {
                 $extraFields .= ',(SELECT SpecificationStringValue.value FROM ' . $this->importDatabase . '.SpecificationStringValue WHERE productID=Product.ID AND SpecificationStringValue.specFieldID=' . $field['ID'] . ') AS field_' . $field['handle'];
             }
         }
         foreach (array('name', 'longDescription', 'shortDescription') as $parentField) {
             $extraFields .= ', IF(Product.parentID IS NOT NULL, ParentProduct.' . $parentField . ', Product.' . $parentField . ') AS ' . $parentField;
         }
         if ('SpecificationStringValue' == $table) {
             $sql = 'SELECT Product.*, Product.ID AS productID, Product.parentID AS parentProductID, SpecField.ID AS specFieldID, (SELECT value FROM SpecificationStringValue WHERE SpecificationStringValue.productID=Product.ID AND SpecificationStringValue.specFieldID=SpecField.ID) AS value, (SELECT productID FROM SpecificationStringValue WHERE SpecificationStringValue.productID=Product.ID AND SpecificationStringValue.specFieldID=SpecField.ID) AS fieldValueExists' . $extraFields . ' FROM ' . $this->importDatabase . '.Product INNER JOIN ' . $this->importDatabase . '.SpecField LEFT JOIN ' . $this->importDatabase . '.Product AS ParentProduct ON (Product.parentID=ParentProduct.ID)';
             //$sql = 'SELECT *' . $extraFields . ' FROM ' . $this->importDatabase . '.SpecField LEFT JOIN ' . $this->importDatabase . '.' . $table . ' LEFT JOIN Product ON productID=Product.ID';
         } else {
             if ('Product' == $table) {
                 $sql = 'SELECT Product.*' . $extraFields . ' FROM ' . $this->importDatabase . '.' . $table . ' LEFT JOIN Product AS ParentProduct ON (Product.parentID=ParentProduct.ID)';
             } else {
                 $sql = 'SELECT * FROM ' . $this->importDatabase . '.' . $table;
             }
         }
         $offset = 0;
         $batch = 5000;
         $modified = array();
         do {
             $select = $sql . ' LIMIT ' . $batch * $offset . ',' . $batch;
             $offset++;
             $rows = ActiveRecordModel::getDataBySQL($select);
             foreach ($rows as $row) {
                 $original = $row;
                 $id = array();
                 $values = array();
                 foreach ($row as $key => $val) {
                     if (@unserialize($val)) {
                         $data = unserialize($val);
                         $value = array_shift($data);
                     } else {
                         $value = $val;
                     }
                     $values[$key] = $value;
                 }
                 if (!empty($modified[$values['ID']])) {
                     $values = array_merge($values, $modified[$values['ID']]);
                 }
                 $hasQuery = false;
                 foreach ($rules as $rule) {
                     $f = $rule['field'];
                     $identifier = $f;
                     //						var_dump($f);
                     if ($rule['query']) {
                         $productID = 'SpecificationStringValue' == $table ? $original['productID'] : $original['ID'];
                         $count = ActiveRecordModel::getDataBySQL('SELECT COUNT(*) AS cnt FROM ' . $this->importDatabase . '.Product WHERE ID=' . $productID . ' AND ' . $rule['query']);
                         $count = array_pop($count);
                         if (!$count['cnt']) {
                             continue;
                         }
                         $hasQuery = true;
                     }
                     if ('SpecificationStringValue' == $table) {
                         if ($row['specFieldID'] != $f) {
                             continue;
                         } else {
                             $f = 'value';
                         }
                         if ($rule['field']) {
                             try {
                                 $spFld = SpecField::getInstanceByID($rule['field'], true);
                                 if ($spFld) {
                                     $identifier = 'field_' . $spFld->handle->get();
                                 }
                             } catch (Exception $e) {
                                 $identifier = '';
                             }
                         }
                     }
                     if (@unserialize($row[$f])) {
                         $data = unserialize($row[$f]);
                         foreach ($data as $key => $value) {
//.........这里部分代码省略.........

 public static function getActiveProductManufacturerFirstLetters()
 {
     $letters = $ids = array();
     $f = new ARSelectFilter();
     $sql = !self::getApplication()->getConfig()->get('MANUFACTURER_PAGE_DISPLAY_ACTIVE') ? 'SELECT DISTINCT(ID) as manufacturerID FROM Manufacturer ' . $f->createString() . ' GROUP BY manufacturerID' : 'SELECT DISTINCT(manufacturerID) FROM Product ' . $f->createString() . ' GROUP BY manufacturerID';
     foreach (ActiveRecordModel::getDataBySQL($sql) as $row) {
         $ids[] = $row['manufacturerID'];
     }
     $f = new ARSelectFilter(new InCond(new ARFieldHandle('Manufacturer', 'ID'), $ids));
     $f->addField('UPPER(LEFT(TRIM(Manufacturer.name),1))', '', 'FirstLetter');
     $f->mergeCondition(new NotEqualsCond(new ARFieldHandle('Manufacturer', 'name'), ''));
     $f->setOrder(new ARFieldHandle('Manufacturer', 'name'));
     $manufacturers = ActiveRecordModel::getRecordSetArray('Manufacturer', $f);
     foreach ($manufacturers as $item) {
         $letters[$item['FirstLetter']] = $item['FirstLetter'];
     }
     return $letters;
 }