<?php
namespace App\Repository;
use App\Entity\Category;
use Doctrine\ORM\EntityRepository;
class CategoryProductRepository extends EntityRepository
{
public function searchProducts($query, $category, $filters=[],$sort=[],$page=1,$limit=25)
{
$q = $this->createQueryBuilder('cp')
->innerJoin('cp.product','p')
->where('cp.category = :category')
// ->andWhere('p.parent IS NULL')
->setParameter('category', $category);
if(!empty($query)){
if(ctype_digit($query)){
$q->andWhere(
$q->expr()->eq('p.id',$query)
);
}else{
$q->andWhere(
'p.model LIKE \'%'.$query.'%\''
);
}
}
if(empty($sort)){
$q->orderBy('p.id', 'desc');
}else{
// foreach($sort as $s){
// $q->addOrderBy('p.'.$s->property, $s->direction);
// }
}
$q->setMaxResults($limit);
$q->setFirstResult(($page-1)*$limit);
$q = $q->getQuery();
$products = [];
$cps = $q->getResult();
foreach($cps as $cp){
$products[] = $cp->getProduct();
}
return $products;
}
public function searchProductsCount($query, $category, $filters=[])
{
$q = $this->createQueryBuilder('cp')
->select('count(p.id)')
->innerJoin('cp.product','p')
->where('cp.category = :category')
// ->andWhere('p.parent IS NULL')
->setParameter('category', $category);
if(!empty($query)){
if(ctype_digit($query)){
$q->andWhere(
$q->expr()->eq('p.id',$query)
);
}else{
$q->andWhere(
'p.model LIKE \'%'.$query.'%\''
);
}
}
$q = $q->getQuery();
return $q->getSingleScalarResult();
}
public function getCategoriesByProduct(\App\Entity\Product $product, $onlyActive=false, $limit=null, $page=1){
$q = $this->createQueryBuilder('cp')
->join('cp.product', 'p')
->where('cp.product = :product')
->setParameter('product', $product);
if($onlyActive){
$q->andWhere('p.active = 1');
}
if($limit){
$q->setMaxResults($limit);
$q->setFirstResult(($page-1)*$limit);
}
$q = $q->getQuery();
$results = $q->getResult();
// dump($results);
$output = [];
foreach($results as $result){
$output[] = $result->getCategory();
}
return $output;
}
/**
* Returns one representative Product per category listing entry: products that belong to
* a ProductModel are collapsed into a single entry for their top-level model, so a category
* page never lists the same model's variants (colors, sizes, ...) as separate rows.
*
* $filters maps an Attribute code to a value (or an array of values). A model/product only
* matches if one of its variants carries all of the requested attribute values.
*/
/**
* @param \App\Entity\Category|int|array $category a single category (or id), or a list of
* categories/ids to list products from (e.g. a
* category and all of its descendants).
*/
public function getProductsByCategory($category, $onlyActive = false, $limit = null, $page = 1, $sorts = [], $filters = []){
$representatives = $this->getGroupedRepresentatives($category, $onlyActive, $filters, $sorts);
if($limit){
$representatives = array_slice($representatives, ($page - 1) * $limit, $limit);
}
$ids = array_column($representatives, 'id');
if(empty($ids)){
return [];
}
$products = $this->getEntityManager()->getRepository(\App\Entity\Product::class)
->createQueryBuilder('p')
->where('p.id IN (:ids)')
->setParameter('ids', $ids)
->getQuery()
->getResult();
$productsById = [];
foreach($products as $product){
$productsById[$product->getId()] = $product;
}
$output = [];
foreach($ids as $id){
if(isset($productsById[$id])){
$output[] = $productsById[$id];
}
}
return $output;
}
/**
* @param \App\Entity\Category|int|array $category see getProductsByCategory()
*/
public function getProductCountByCategory($category, $onlyActive = false, $filters = []){
return count($this->getGroupedRepresentatives($category, $onlyActive, $filters));
}
/**
* Fetches every (product, model) pair matching the category/filters, groups them by their
* top-level ProductModel (or by product id when there is no model), and picks one
* representative per group: the default variant if one matches, otherwise the first match.
*
* @return array<int, array{id:int, isDefault:bool}> ordered list of representatives
*/
private function getGroupedRepresentatives($category, bool $onlyActive, array $filters, array $sorts = []): array {
$categories = is_iterable($category) ? (is_array($category) ? array_values($category) : iterator_to_array($category)) : [$category];
if(empty($categories)){
return [];
}
$qb = $this->createQueryBuilder('cp')
->select('p.id AS productId', 'pm.id AS modelId', 'p.isDefaultVariant AS isDefault')
->innerJoin('cp.product', 'p')
->leftJoin('p.productModel', 'pm')
->where('cp.category IN (:categories)')
->setParameter('categories', $categories);
if($onlyActive){
$qb->andWhere('p.status = 1');
}
$i = 0;
foreach($filters as $code => $values){
if($values === null || $values === '' || $values === []){
continue;
}
$values = is_array($values) ? array_values($values) : [$values];
$paAlias = 'pa'.$i;
$attrAlias = 'attr'.$i;
$qb->innerJoin('p.attributes', $paAlias)
->innerJoin($paAlias.'.attribute', $attrAlias)
->andWhere($qb->expr()->eq($attrAlias.'.code', ':attrCode'.$i))
->andWhere($qb->expr()->in($paAlias.'.value', ':attrValues'.$i))
->setParameter('attrCode'.$i, $code)
->setParameter('attrValues'.$i, $values);
$i++;
}
if(!empty($sorts)){
foreach($sorts as $field => $direction){
$qb->addOrderBy('p.'.$field, $direction);
}
}else{
$qb->addOrderBy('p.id', 'ASC');
}
$rows = $qb->getQuery()->getScalarResult();
$modelIds = array_values(array_unique(array_filter(array_column($rows, 'modelId'), fn($id) => $id !== null)));
$topModelIds = $this->resolveTopModelIds($modelIds);
$groups = [];
$order = [];
foreach($rows as $row){
$productId = (int) $row['productId'];
$modelId = $row['modelId'] !== null ? (int) $row['modelId'] : null;
$groupKey = $modelId !== null ? 'm'.$topModelIds[$modelId] : 'p'.$productId;
if(!isset($groups[$groupKey])){
$groups[$groupKey] = [];
$order[] = $groupKey;
}
$groups[$groupKey][] = ['id' => $productId, 'isDefault' => (bool) $row['isDefault']];
}
$representatives = [];
foreach($order as $groupKey){
$chosen = null;
foreach($groups[$groupKey] as $variant){
if($variant['isDefault']){
$chosen = $variant;
break;
}
}
$representatives[] = $chosen ?? $groups[$groupKey][0];
}
return $representatives;
}
/**
* Resolves, for each given ProductModel id, the id of its top-most ancestor
* (a model can itself belong to another model, e.g. a color model under a top model).
*
* @return array<int, int> map of modelId => topModelId
*/
private function resolveTopModelIds(array $modelIds): array {
if(empty($modelIds)){
return [];
}
$parentOf = [];
$toFetch = $modelIds;
$conn = $this->getEntityManager()->getConnection();
while(!empty($toFetch)){
$rows = $conn->fetchAllAssociative(
'SELECT id, parent_id FROM products_models WHERE id IN (?)',
[$toFetch],
[\Doctrine\DBAL\Connection::PARAM_INT_ARRAY]
);
$toFetch = [];
foreach($rows as $row){
$id = (int) $row['id'];
$parentId = $row['parent_id'] !== null ? (int) $row['parent_id'] : null;
$parentOf[$id] = $parentId;
if($parentId !== null && !array_key_exists($parentId, $parentOf)){
$toFetch[] = $parentId;
}
}
}
$top = [];
foreach($modelIds as $id){
$current = $id;
while(!empty($parentOf[$current])){
$current = $parentOf[$current];
}
$top[$id] = $current;
}
return $top;
}
}