public function getQueryBuilderBySearchData($searchData)
{
$qb = $this->createQueryBuilder('p')->andWhere('p.Status = 1');
// category
$categoryJoin = false;
if (!empty($searchData['category_id']) && $searchData['category_id']) {
$Categories = $searchData['category_id']->getSelfAndDescendants();
if ($Categories) {
$qb->innerJoin('p.ProductCategories', 'pct')->innerJoin('pct.Category', 'c')->andWhere($qb->expr()->in('pct.Category', ':Categories'))->setParameter('Categories', $Categories);
$categoryJoin = true;
}
}
// name
if (isset($searchData['name']) && Str::isNotBlank($searchData['name'])) {
$keywords = preg_split('/[\\s ]+/u', $searchData['name'], -1, PREG_SPLIT_NO_EMPTY);
foreach ($keywords as $index => $keyword) {
$key = sprintf('keyword%s', $index);
$qb->andWhere(sprintf('p.name LIKE :%s OR p.search_word LIKE :%s', $key, $key))->setParameter($key, '%' . $keyword . '%');
}
}
// Order By
// 価格低い順
$config = $this->app['config'];
if (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['product_order_price_lower']) {
//@see http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
$qb->addSelect('MIN(pc.price02) as HIDDEN price02_min');
$qb->innerJoin('p.ProductClasses', 'pc');
$qb->groupBy('p');
// postgres9.0以下は, groupBy('p.id')が利用できない
// mysqlおよびpostgresql9.1以上であればgroupBy('p.id')にすることで性能向上が期待できる.
// @see https://github.com/EC-CUBE/ec-cube/issues/1904
// $qb->groupBy('p.id');
$qb->orderBy('price02_min', 'ASC');
$qb->addOrderBy('p.id', 'DESC');
// 価格高い順
} else {
if (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['product_order_price_higher']) {
$qb->addSelect('MAX(pc.price02) as HIDDEN price02_max');
$qb->innerJoin('p.ProductClasses', 'pc');
$qb->groupBy('p');
$qb->orderBy('price02_max', 'DESC');
$qb->addOrderBy('p.id', 'DESC');
// 新着順
} else {
if (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['product_order_newer']) {
$qb->orderBy('p.create_date', 'DESC');
} else {
if ($categoryJoin === false) {
$qb->leftJoin('p.ProductCategories', 'pct')->leftJoin('pct.Category', 'c');
}
$qb->addOrderBy('p.id', 'DESC');
}
}
}
return $qb;
}