Is it possible to combine two DQL queries from one table so that pagination works correctly?

user_16099087 0
Viewed 76

There is a table with next fields: 'is_international', 'category', 'countries' and others, but they are not important.

I want to optimize a DQL query to get data:

/**
 * @param   bool         $international
 * @param   int[]|null   $category
 * @param   string|null  $country
 *
 * @return Query
 */
public function getItem(
    bool $international,
    ?array $category,
    ?string $country
): Query {
    $qb = $this->createQueryBuilder('c')
        ->orderBy('c.createdAt', 'DESC')
    ;

    if (empty($category) && !isset($country)) {
        return $qb
            ->andWhere('c.isInternational = true')
            ->getQuery()
        ;
    }

     if (!empty($category)) {
        $qb
            ->innerJoin('c.category', 'category')
            ->andWhere('c.category IN (:category) AND c.isInternational = false')
            ->setParameter('category', $category)
        ;
    }

    if (isset($country)) {
        $qb
            ->innerJoin('c.countries', 'cc', 'WITH', 'cc.name = :country')
            ->setParameter('country', $country)
        ;
    }

    return $qb->getQuery();
}

There is a boolean value $international and its essence is that if it true and passed any of parameters $category or $country, I'm want to add international items from this table to query:

if ($international) {
    $qb
        ->addSelect('int')
        ->join(Coupon::class, 'int')
        ->andWhere('int.isInternational = true')
    ;
}

Actually adding them is not difficult, but the difficulty is that Knp\Component\Pager\PaginatorInterface incorrectly displays limits and as far as I understand, this is due to the new addSelect('int') he gives the right items and all international items... how to get the right selection?

symfony doctrine-orm dql php
1 Answers

Actually, as always, the answer lay on the surface, it was only necessary to apply it correctly...

if (!empty($category)) {
    $qb
        ->andWhere('c.category IN (:category) AND c.isInternational = false')
        ->setParameter('category', $category)
    ;
}

if (isset($country)) {
    $qb
        ->leftJoin('c.countries', 'cc')
        ->andWhere('cc.name IN (:country)')
        ->setParameter('country', $country)
    ;
}

if ($international) {
    $qb
        ->orWhere('c.isInternational = true')
        ->addOrderBy('c.isInternational', 'ASC')
    ;
}
user_160990870
Related