Symfony Profiler

<?php
/*
* This file is part of EC-CUBE
*
* Copyright(c) EC-CUBE CO.,LTD. All Rights Reserved.
*
* http://www.ec-cube.co.jp/
*
* For the full copyright and license information, please view the LICENSE
* file that was distributed with this source code.
*/
namespace Customize\Repository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Persistence\ManagerRegistry as RegistryInterface;
use Eccube\Repository\AbstractRepository;
use Eccube\Common\EccubeConfig;
use Eccube\Doctrine\Query\Queries;
use Eccube\Entity\Category;
use Eccube\Entity\Master\ProductListMax;
use Eccube\Entity\Master\ProductListOrderBy;
use Eccube\Entity\Master\ProductStatus;
use Eccube\Entity\Product;
use Eccube\Entity\ProductStock;
use Eccube\Entity\Tag;
use Eccube\Util\StringUtil;
use Eccube\Repository\QueryKey;
/**
* ProductRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class ProductRepository extends AbstractRepository
{
/**
* @var Queries
*/
protected $queries;
/**
* @var EccubeConfig
*/
protected $eccubeConfig;
public const COLUMNS = [
'product_id' => 'p.id', 'name' => 'p.name', 'product_code' => 'pc.code', 'stock' => 'pc.stock', 'status' => 'p.Status', 'create_date' => 'p.create_date', 'update_date' => 'p.update_date',
'series' => 'pfc_sort.meta_content',
];
/**
* ProductRepository constructor.
*
* @param RegistryInterface $registry
* @param Queries $queries
* @param EccubeConfig $eccubeConfig
*/
public function __construct(
RegistryInterface $registry,
Queries $queries,
EccubeConfig $eccubeConfig
) {
parent::__construct($registry, Product::class);
$this->queries = $queries;
$this->eccubeConfig = $eccubeConfig;
}
/**
* Find the Product with sorted ClassCategories.
*
* @param integer $productId
*
* @return Product
*/
public function findWithSortedClassCategories($productId)
{
$qb = $this->createQueryBuilder('p');
$qb->addSelect(['pc', 'cc1', 'cc2', 'pi', 'pt'])
->innerJoin('p.ProductClasses', 'pc')
->leftJoin('pc.ClassCategory1', 'cc1')
->leftJoin('pc.ClassCategory2', 'cc2')
->leftJoin('p.ProductImage', 'pi')
->leftJoin('p.ProductTag', 'pt')
->where('p.id = :id')
->andWhere('pc.visible = :visible')
->setParameter('id', $productId)
->setParameter('visible', true)
->orderBy('cc1.sort_no', 'DESC')
->addOrderBy('cc2.sort_no', 'DESC');
$product = $qb
->getQuery()
->getSingleResult();
return $product;
}
/**
* Find the Products with sorted ClassCategories.
*
* @param array $ids Product in ids
* @param string $indexBy The index for the from.
*
* @return ArrayCollection|array
*/
public function findProductsWithSortedClassCategories(array $ids, $indexBy = null)
{
if (count($ids) < 1) {
return [];
}
$qb = $this->createQueryBuilder('p', $indexBy);
$qb->addSelect(['pc', 'cc1', 'cc2', 'pi', 'pt', 'tr', 'ps'])
->innerJoin('p.ProductClasses', 'pc')
// XXX Joined 'TaxRule' and 'ProductStock' to prevent lazy loading
->leftJoin('pc.TaxRule', 'tr')
->innerJoin('pc.ProductStock', 'ps')
->leftJoin('pc.ClassCategory1', 'cc1')
->leftJoin('pc.ClassCategory2', 'cc2')
->leftJoin('p.ProductImage', 'pi')
->leftJoin('p.ProductTag', 'pt')
->where($qb->expr()->in('p.id', $ids))
->andWhere('pc.visible = :visible')
->setParameter('visible', true)
->orderBy('cc1.sort_no', 'DESC')
->addOrderBy('cc2.sort_no', 'DESC');
$products = $qb
->getQuery()
->useResultCache(true, $this->eccubeConfig['eccube_result_cache_lifetime_short'])
->getResult();
return $products;
}
/**
* get query builder.
*
* @param array{
* category_id?:Category,
* name?:string,
* pageno?:string,
* disp_number?:ProductListMax,
* orderby?:ProductListOrderBy
* } $searchData
*
* @return \Doctrine\ORM\QueryBuilder
*/
public function getQueryBuilderBySearchData($searchData)
{
$productFieldContentClass = \Plugin\ProductField\Entity\ProductFieldContent::class;
$productClassClass = \Eccube\Entity\ProductClass::class;
// ===== Phase 1: カテゴリ・名前・メーカーに合致する公開商品IDを収集 =====
$preQb = $this->createQueryBuilder('p_pre')
->select('DISTINCT p_pre.id')
->andWhere('p_pre.Status = 1');
if (!empty($searchData['category_id']) && $searchData['category_id']) {
$Categories = $searchData['category_id']->getSelfAndDescendants();
if ($Categories) {
$preQb
->innerJoin('p_pre.ProductCategories', 'pct_pre')
->andWhere($preQb->expr()->in('pct_pre.Category', ':cat_pre'))
->setParameter('cat_pre', $Categories);
}
}
if (!empty($searchData['maker_id']) && $searchData['maker_id']) {
$preQb
->innerJoin('p_pre.Maker', 'pm_pre')
->andWhere('pm_pre.id = :maker_id_pre')
->setParameter('maker_id_pre', $searchData['maker_id']);
}
if (isset($searchData['name']) && StringUtil::isNotBlank($searchData['name'])) {
$keywords = preg_split('/[\s　]+/u', str_replace(['%', '_'], ['\\%', '\\_'], $searchData['name']), -1, PREG_SPLIT_NO_EMPTY);
foreach ($keywords as $index => $keyword) {
$key = sprintf('kw_pre_%s', $index);
$preQb
->andWhere(sprintf('NORMALIZE(p_pre.name) LIKE NORMALIZE(:%s) OR NORMALIZE(p_pre.search_word) LIKE NORMALIZE(:%s) OR EXISTS (SELECT wpc_pre%d FROM \Eccube\Entity\ProductClass wpc_pre%d WHERE p_pre = wpc_pre%d.Product AND NORMALIZE(wpc_pre%d.code) LIKE NORMALIZE(:%s))',
$key, $key, $index, $index, $index, $index, $key))
->setParameter($key, '%'.$keyword.'%');
}
}
$preRows = $preQb->getQuery()->getArrayResult();
$matchingIds = array_column($preRows, 'id');
if (empty($matchingIds)) {
$qb = $this->createQueryBuilder('p')
->andWhere('1 = 0');
return $this->queries->customize(QueryKey::PRODUCT_SEARCH, $qb, $searchData);
}
// ===== Phase 2: matchingIds の中で related_keyword グループの代表を選ぶ =====
// matchingIds 内の各商品の related_keyword を取得
$kwQb = $this->getEntityManager()->createQueryBuilder();
$kwQb
->select('pfc_m.product_id, pfc_m.meta_content AS keyword')
->from($productFieldContentClass, 'pfc_m')
->where('pfc_m.meta_key = :mk_m')
->andWhere($kwQb->expr()->in('pfc_m.product_id', ':matching_ids'))
->setParameter('mk_m', 'related_keyword')
->setParameter('matching_ids', $matchingIds);
$kwRows = $kwQb->getQuery()->getArrayResult();
// product_id => keyword のマップ
$productKeywordMap = [];
foreach ($kwRows as $row) {
$productKeywordMap[$row['product_id']] = $row['keyword'];
}
$idsWithKeyword = array_keys($productKeywordMap);
$idsWithoutKeyword = array_values(array_diff($matchingIds, $idsWithKeyword));
// keyword => [product_ids] のグループ
$keywordGroups = [];
foreach ($productKeywordMap as $pid => $kw) {
$keywordGroups[$kw][] = $pid;
}
// 各グループから最安値・最小IDの代表を選ぶ
$representativeIds = [];
if (!empty($idsWithKeyword)) {
$priceQb = $this->getEntityManager()->createQueryBuilder();
$priceQb
->select('IDENTITY(pc_p.Product) as product_id, MIN(pc_p.price02) as min_price')
->from($productClassClass, 'pc_p')
->where($priceQb->expr()->in('pc_p.Product', ':ids_kw'))
->andWhere('pc_p.visible = true')
->groupBy('pc_p.Product')
->setParameter('ids_kw', $idsWithKeyword);
$priceRows = $priceQb->getQuery()->getArrayResult();
$productPriceMap = [];
foreach ($priceRows as $row) {
$productPriceMap[$row['product_id']] = (int) $row['min_price'];
}
foreach ($keywordGroups as $kw => $pids) {
$minPrice = PHP_INT_MAX;
$repId = null;
foreach ($pids as $pid) {
$price = $productPriceMap[$pid] ?? PHP_INT_MAX;
if ($price < $minPrice || ($price === $minPrice && ($repId === null || $pid < $repId))) {
$minPrice = $price;
$repId = $pid;
}
}
if ($repId !== null) {
$representativeIds[] = $repId;
}
}
}
// グループ代表 + グループ未所属 = 表示対象
$visibleIds = array_merge($representativeIds, $idsWithoutKeyword);
if (empty($visibleIds)) {
$qb = $this->createQueryBuilder('p')
->andWhere('1 = 0');
return $this->queries->customize(QueryKey::PRODUCT_SEARCH, $qb, $searchData);
}
// ===== Phase 3: 最終クエリを構築（visibleIds + 並び順 + 価格帯） =====
$qb = $this->createQueryBuilder('p')
->andWhere('p.Status = 1');
$qb->andWhere($qb->expr()->in('p.id', ':visible_ids'))
->setParameter('visible_ids', $visibleIds);
// category（並び順のJOIN用）
$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;
}
}
// maker
if (!empty($searchData['maker_id']) && $searchData['maker_id']) {
$qb
->innerJoin('p.Maker', 'pm')
->andWhere('pm.id = :maker_id')
->setParameter('maker_id', $searchData['maker_id']);
}
// name
if (isset($searchData['name']) && StringUtil::isNotBlank($searchData['name'])) {
$keywords = preg_split('/[\s　]+/u', str_replace(['%', '_'], ['\\%', '\\_'], $searchData['name']), -1, PREG_SPLIT_NO_EMPTY);
foreach ($keywords as $index => $keyword) {
$key = sprintf('keyword%s', $index);
$qb
->andWhere(sprintf('NORMALIZE(p.name) LIKE NORMALIZE(:%s) OR
NORMALIZE(p.search_word) LIKE NORMALIZE(:%s) OR
EXISTS (SELECT wpc%d FROM \Eccube\Entity\ProductClass wpc%d WHERE p = wpc%d.Product AND NORMALIZE(wpc%d.code) LIKE NORMALIZE(:%s))',
$key, $key, $index, $index, $index, $index, $key))
->setParameter($key, '%'.$keyword.'%');
}
}
// 価格低い順
$config = $this->eccubeConfig;
if (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_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->andWhere('pc.visible = true');
$qb->groupBy('p.id');
$qb->orderBy('price02_min', 'ASC');
$qb->addOrderBy('p.id', 'DESC');
// 価格高い順
} elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_price_higher']) {
$qb->addSelect('MAX(pc.price02) as HIDDEN price02_max');
$qb->innerJoin('p.ProductClasses', 'pc');
$qb->andWhere('pc.visible = true');
$qb->groupBy('p.id');
$qb->orderBy('price02_max', 'DESC');
$qb->addOrderBy('p.id', 'DESC');
// 新着順
} elseif (!empty($searchData['orderby']) && $searchData['orderby']->getId() == $config['eccube_product_order_newer']) {
// 在庫切れ商品非表示の設定が有効時対応
// @see https://github.com/EC-CUBE/ec-cube/issues/1998
if ($this->getEntityManager()->getFilters()->isEnabled('option_nostock_hidden') == true) {
$qb->innerJoin('p.ProductClasses', 'pc');
$qb->andWhere('pc.visible = true');
}
$qb->orderBy('p.create_date', 'DESC');
$qb->addOrderBy('p.id', 'DESC');
} else {
if ($categoryJoin === false) {
$qb
->leftJoin('p.ProductCategories', 'pct')
->leftJoin('pct.Category', 'c');
}
$qb
->addOrderBy('p.id', 'ASC');
}
// 価格帯の絞り込み
// price_min / price_max はコントローラから $searchData に追加される
// pc（ProductClasses）がまだJOINされていない場合はここでJOINする
$pcJoined = false;
foreach ($qb->getDQLPart('join') as $joins) {
foreach ($joins as $join) {
if ($join->getAlias() === 'pc') {
$pcJoined = true;
break 2;
}
}
}
if (!empty($searchData['price_min']) || !empty($searchData['price_max'])) {
if (!$pcJoined) {
$qb->innerJoin('p.ProductClasses', 'pc')
->andWhere('pc.visible = true');
}
// price02 は税抜価格カラム（DBの実カラム名）
// 税込換算: price02 * (1 + tax_rate/100)
// ただし税率はTaxRuleに依存するため、サイトの消費税率(10%)を固定で使用
// 税込入力値 → 税抜換算して比較（÷1.1）
if (!empty($searchData['price_min'])) {
$priceMinExTax = (int) round((int) $searchData['price_min'] / 1.1);
$qb->andWhere('pc.price02 >= :price_min')
->setParameter('price_min', $priceMinExTax);
}
if (!empty($searchData['price_max'])) {
$priceMaxExTax = (int) round((int) $searchData['price_max'] / 1.1);
$qb->andWhere('pc.price02 <= :price_max')
->setParameter('price_max', $priceMaxExTax);
}
// GROUP BYが未設定の場合は追加
$groupBy = $qb->getDQLPart('groupBy');
if (empty($groupBy)) {
$qb->groupBy('p.id');
}
}
return $this->queries->customize(QueryKey::PRODUCT_SEARCH, $qb, $searchData);
}
/**
* get query builder.
*
* @param array{
* id?:string|int|null,
* category_id?:Category,
* status?:ProductStatus[],
* link_status?:ProductStatus[],
* stock_status?:int,
* stock?:ProductStock::IN_STOCK|ProductStock::OUT_OF_STOCK,
* tag_id?:Tag,
* create_datetime_start?:\DateTime,
* create_datetime_end?:\DateTime,
* create_date_start?:\DateTime,
* create_date_end?:\DateTime,
* update_datetime_start?:\DateTime,
* update_datetime_end?:\DateTime,
* update_date_start?:\DateTime,
* update_date_end?:\DateTime,
* sortkey?:string,
* sorttype?:string
* } $searchData
*
* @return \Doctrine\ORM\QueryBuilder
*/
public function getQueryBuilderBySearchDataForAdmin($searchData)
{
$qb = $this->createQueryBuilder('p')
->addSelect('pc', 'pi', 'tr', 'ps')
->innerJoin('p.ProductClasses', 'pc')
->leftJoin('p.ProductImage', 'pi')
->leftJoin('pc.TaxRule', 'tr')
->leftJoin('pc.ProductStock', 'ps')
->andWhere('pc.visible = :visible')
->setParameter('visible', true);
// id
if (isset($searchData['id']) && StringUtil::isNotBlank($searchData['id'])) {
$id = preg_match('/^\d{0,10}$/', $searchData['id']) ? $searchData['id'] : null;
if ($id && $id > '2147483647' && $this->isPostgreSQL()) {
$id = null;
}
$qb
->andWhere('p.id = :id OR p.name LIKE :likeid OR p.note LIKE :likeid OR pc.code LIKE :likeid')
->setParameter('id', $id)
->setParameter('likeid', '%'.str_replace(['%', '_'], ['\\%', '\\_'], $searchData['id']).'%');
}
// code
/*
if (!empty($searchData['code']) && $searchData['code']) {
$qb
->innerJoin('p.ProductClasses', 'pc')
->andWhere('pc.code LIKE :code')
->setParameter('code', '%' . $searchData['code'] . '%');
}
// name
if (!empty($searchData['name']) && $searchData['name']) {
$keywords = preg_split('/[\s　]+/u', $searchData['name'], -1, PREG_SPLIT_NO_EMPTY);
foreach ($keywords as $keyword) {
$qb
->andWhere('p.name LIKE :name')
->setParameter('name', '%' . $keyword . '%');
}
}
*/
// category
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);
}
}
// status
if (!empty($searchData['status']) && $searchData['status']) {
$qb
->andWhere($qb->expr()->in('p.Status', ':Status'))
->setParameter('Status', $searchData['status']);
}
// link_status
if (isset($searchData['link_status']) && !empty($searchData['link_status'])) {
$qb
->andWhere($qb->expr()->in('p.Status', ':Status'))
->setParameter('Status', $searchData['link_status']);
}
// stock status
if (isset($searchData['stock_status'])) {
$qb
->andWhere('pc.stock_unlimited = :StockUnlimited AND pc.stock = 0')
->setParameter('StockUnlimited', $searchData['stock_status']);
}
// stock status
if (isset($searchData['stock']) && !empty($searchData['stock'])) {
switch ($searchData['stock']) {
case [ProductStock::IN_STOCK]:
$qb->andWhere('pc.stock_unlimited = true OR pc.stock > 0');
break;
case [ProductStock::OUT_OF_STOCK]:
$qb->andWhere('pc.stock_unlimited = false AND pc.stock <= 0');
break;
default:
// 共に選択された場合は全権該当するので検索条件に含めない
}
}
// tag
if (!empty($searchData['tag_id']) && $searchData['tag_id']) {
$qb
->innerJoin('p.ProductTag', 'pt')
->andWhere('pt.Tag = :tag_id')
->setParameter('tag_id', $searchData['tag_id']);
}
// maker
if (!empty($searchData['maker_id']) && $searchData['maker_id']) {
$qb
->innerJoin('p.Maker', 'pm')
->andWhere('pm.id = :maker_id')
->setParameter('maker_id', $searchData['maker_id']);
}
// group (plg_product_field_content meta_key='related_keyword' partial match)
if (isset($searchData['group_keyword']) && StringUtil::isNotBlank($searchData['group_keyword'])) {
$qb
->andWhere('EXISTS (SELECT pfc FROM \Plugin\ProductField\Entity\ProductFieldContent pfc '
.'WHERE pfc.product_id = p.id '
.'AND pfc.meta_key = :group_meta_key '
.'AND pfc.meta_content LIKE :group_keyword)')
->setParameter('group_meta_key', 'related_keyword')
->setParameter('group_keyword', '%'.str_replace(['%', '_'], ['\\%', '\\_'], $searchData['group_keyword']).'%');
}
// crate_date
if (!empty($searchData['create_datetime_start']) && $searchData['create_datetime_start']) {
$date = $searchData['create_datetime_start'];
$qb
->andWhere('p.create_date >= :create_date_start')
->setParameter('create_date_start', $date);
} elseif (!empty($searchData['create_date_start']) && $searchData['create_date_start']) {
$date = $searchData['create_date_start'];
$qb
->andWhere('p.create_date >= :create_date_start')
->setParameter('create_date_start', $date);
}
if (!empty($searchData['create_datetime_end']) && $searchData['create_datetime_end']) {
$date = $searchData['create_datetime_end'];
$qb
->andWhere('p.create_date < :create_date_end')
->setParameter('create_date_end', $date);
} elseif (!empty($searchData['create_date_end']) && $searchData['create_date_end']) {
$date = clone $searchData['create_date_end'];
$date = $date
->modify('+1 days');
$qb
->andWhere('p.create_date < :create_date_end')
->setParameter('create_date_end', $date);
}
// update_date
if (!empty($searchData['update_datetime_start']) && $searchData['update_datetime_start']) {
$date = $searchData['update_datetime_start'];
$qb
->andWhere('p.update_date >= :update_date_start')
->setParameter('update_date_start', $date);
} elseif (!empty($searchData['update_date_start']) && $searchData['update_date_start']) {
$date = $searchData['update_date_start'];
$qb
->andWhere('p.update_date >= :update_date_start')
->setParameter('update_date_start', $date);
}
if (!empty($searchData['update_datetime_end']) && $searchData['update_datetime_end']) {
$date = $searchData['update_datetime_end'];
$qb
->andWhere('p.update_date < :update_date_end')
->setParameter('update_date_end', $date);
} elseif (!empty($searchData['update_date_end']) && $searchData['update_date_end']) {
$date = clone $searchData['update_date_end'];
$date = $date
->modify('+1 days');
$qb
->andWhere('p.update_date < :update_date_end')
->setParameter('update_date_end', $date);
}
// Order By
if (isset($searchData['sortkey']) && !empty($searchData['sortkey'])) {
$sortOrder = (isset($searchData['sorttype']) && $searchData['sorttype'] == 'a') ? 'ASC' : 'DESC';
// Series sort needs a LEFT JOIN to plg_product_field_content
if ($searchData['sortkey'] === 'series') {
$qb->leftJoin(
\Plugin\ProductField\Entity\ProductFieldContent::class,
'pfc_sort',
'WITH',
'pfc_sort.product_id = p.id AND pfc_sort.meta_key = :pfc_sort_key'
)->setParameter('pfc_sort_key', 'related_keyword');
}
$qb->orderBy(self::COLUMNS[$searchData['sortkey']], $sortOrder);
$qb->addOrderBy('p.update_date', 'DESC');
$qb->addOrderBy('p.id', 'DESC');
} else {
$qb->orderBy('p.update_date', 'DESC');
$qb->addOrderBy('p.id', 'DESC');
}
return $this->queries->customize(QueryKey::PRODUCT_SEARCH_ADMIN, $qb, $searchData);
}
public function findByLikeName($value, $categories)
{
$builder = $this->createQueryBuilder('p');
return $builder
->innerJoin('p.ProductCategories', 'pct')
->innerJoin('pct.Category', 'c')
->where($builder->expr()->like('p.name', ':name')) // LIKE検索条件
->andWhere($builder->expr()->in('pct.Category', ':Categories'))
->setParameter('name', '%' . $value . '%') // ワイルドカードを含める
->setParameter('Categories', $categories)
->getQuery()
->getResult();
}
}

app/Customize/Repository/ProductRepository.php line 151