EntryRepository.php

<?php

namespace App\Repository;

use App\Entity\Entry;
use App\Entity\Lexicon;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @extends ServiceEntityRepository<Entry>
 *
 * @method Entry|null find($id, $lockMode = null, $lockVersion = null)
 * @method Entry|null findOneBy(array $criteria, array $orderBy = null)
 * @method Entry[]    findAll()
 * @method Entry[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class EntryRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Entry::class);
    }

    public function search($filter)
    {
        $qb = $this->createQueryBuilder('e')
            ->leftJoin('e.headword', 'h')
            ->leftJoin('h.graphies', 'g')
            ->leftJoin('e.lexicon', 'lex')
        ;

        if ($filter['language'] ?? null) {
            $qb->andWhere('e.language = :language')
                ->setParameter('language', $filter['language'])
            ;
        }

        if ($filter['lexicons'] ?? null) {
            $qb->andWhere('e.lexicon IN (:lexicons)')
                ->setParameter('lexicons', $filter['lexicons'])
            ;
        }

        if ($filter['headword'] ?? null) {
            $qb->andWhere('h.value = :headword')
                ->setParameter('headword', $filter['headword'])
            ;
        }

        if ($filter['graphy'] ?? null) {
            $qb->andWhere('g.value = :graphy')
                ->setParameter('graphy', $filter['graphy'])
            ;
        }

        return $qb->getQuery()->getResult();

    }

    public function getByHeadwordValueInLexicons($value, $lexicons)
    {
        $qb = $this->createQueryBuilder('e')
            ->leftJoin('e.headword', 'h')
            ->leftJoin('e.lexicon', 'lex')
            ->andWhere('e.lexicon IN (:lexicons)')
            ->setParameter('lexicons', $lexicons)
            ->andWhere('h.value = :value')
            ->setParameter('value', $value)
        ;

        return $qb->getQuery()->getResult();

    }

    public function add(Entry $entity, bool $flush = false): void
    {
        $this->getEntityManager()->persist($entity);

        if ($flush) {
            $this->getEntityManager()->flush();
        }
    }

    public function remove(Entry $entity, bool $flush = false): void
    {
        $this->getEntityManager()->remove($entity);

        if ($flush) {
            $this->getEntityManager()->flush();
        }
    }

    /**
     * @return Entry[]
     */
    public function getLastEntries(Lexicon $lexicon): array
    {
        return $this->createQueryBuilder('e')
            ->andWhere('e.lexicon = :lexicon')
            ->setParameter('lexicon', $lexicon)
            ->setMaxResults(5)
            ->getQuery()
            ->getResult()
            ;
    }

    /**
     * @return Entry[] Returns an array of Entry objects
     */
    public function filter($filter, $sortingColumn = null, $sortingOrder = null): array
    {
        $qb = $this->createQueryBuilder('e')
            ->innerJoin('e.headword', 'h')
        ;

        if (!empty($filter['lexicon'])) {
            $qb ->andWhere('e.lexicon = :lexicon')
                ->setParameter('lexicon', $filter['lexicon']);
        }

//        if (!empty($filter['headword'])) {
//            $qb ->andWhere('e.headword = :headword')
//                ->setParameter('headword', $filter['headword']);
//        }

        if (!empty($filter['searchString'])) {
            $qb ->andWhere('h.value LIKE :searchString')
                ->setParameter('searchString', '%' . $filter['searchString'] . '%');
        }

        if ($sortingColumn) {
            $qb = $this->setSortingOrder($qb, $sortingColumn, $sortingOrder);
        } else {
            $qb->orderBy('e.createdAt', 'ASC');
        }

        return $qb
            ->getQuery()
            ->getResult()
            ;
    }

    private function setSortingOrder(QueryBuilder $qb, $sortingColumn, $sortingOrder = 'DESC')
    {
        switch ($sortingColumn) {
            case 'value':
//                $sortingColumn = 'value';
                $entity = 'h'; break;
            case 'createdAt':
            default:
                $entity = 'e';
        }

        return $qb->orderBy($entity . '.' . $sortingColumn, $sortingOrder);
    }
}