Use subquery in join #5912

New Issue

admin · 2026-01-22T15:21:53+01:00

admin commented

2026-01-22 15:21:53 +01:00

Originally created by @kleinpetr on GitHub (Mar 6, 2018).

Originally assigned to: @Ocramius on GitHub.

is here some way how can I use SubQuery in innerJoin via doctrin queryBuilder() ?
Something like this

$subQuery = $this->em->createQueryBuilder()
                    ->select('MIN(t.final_price) AS final_price, 
                      h.id')
                    ->from(TourEntity::class, 't')
                    ->leftJoin('t.hotel', 'h')
                    ->leftJoin('t.tour_types', 'tt')
                    ->grouoBy('h.id');

$qb = $this->em->createQueryBuilder()
                ->select('MIN(t.id) AS id_tour')
                ->from(TourEntity::class, 't')
                ->innerJoin($subQuery, 'helpTour', Join::ON, 'helpTour.id_hotel = t.id_hotel AND helpTour.final_price = t.final_price')
                ->leftJoin('t.hotel', 'h')
                ->groupBy('h.id')

Thank you so much.

Originally created by @kleinpetr on GitHub (Mar 6, 2018). Originally assigned to: @Ocramius on GitHub. is here some way how can I use SubQuery in innerJoin via doctrin queryBuilder() ? Something like this ``` $subQuery = $this->em->createQueryBuilder() ->select('MIN(t.final_price) AS final_price, h.id') ->from(TourEntity::class, 't') ->leftJoin('t.hotel', 'h') ->leftJoin('t.tour_types', 'tt') ->grouoBy('h.id'); $qb = $this->em->createQueryBuilder() ->select('MIN(t.id) AS id_tour') ->from(TourEntity::class, 't') ->innerJoin($subQuery, 'helpTour', Join::ON, 'helpTour.id_hotel = t.id_hotel AND helpTour.final_price = t.final_price') ->leftJoin('t.hotel', 'h') ->groupBy('h.id') ``` Thank you so much.

admin added the Question label 2026-01-22 15:21:53 +01:00

admin closed this issue

2026-01-22 15:21:54 +01:00

admin commented

2026-01-22 15:21:54 +01:00

@Ocramius commented on GitHub (Mar 6, 2018):

@kleinpetr sub-queries cannot be joined with in DQL, because the type of the subquery is not necessarily well-defined.

@Ocramius commented on GitHub (Mar 6, 2018): @kleinpetr sub-queries cannot be joined with in DQL, because the type of the subquery is not necessarily well-defined.

admin commented

2026-01-22 15:21:55 +01:00

@kleinpetr commented on GitHub (Mar 9, 2018):

So how can I use this example ?

@kleinpetr commented on GitHub (Mar 9, 2018): So how can I use this example ?

admin commented

2026-01-22 15:21:56 +01:00

@Ocramius commented on GitHub (Mar 9, 2018):

You'd need to either make two subsequent queries with an IN() condition on the second one that restricts the search over identifiers, or an SQL query.

@Ocramius commented on GitHub (Mar 9, 2018): You'd need to either make two subsequent queries with an `IN()` condition on the second one that restricts the search over identifiers, or an SQL query.

admin commented

2026-01-22 15:21:56 +01:00

@kleinpetr commented on GitHub (Mar 9, 2018):

can you show on this example please ? If I understand, INNER JOIN work as WHERE condition, so I must get array of minimal prices and use this array as IN

@kleinpetr commented on GitHub (Mar 9, 2018): can you show on this example please ? If I understand, INNER JOIN work as WHERE condition, so I must get array of minimal prices and use this array as IN

admin commented

2026-01-22 15:21:56 +01:00

@Ocramius commented on GitHub (Mar 9, 2018):

Something like:

$ids = $em
    ->createQuery(
<<<'DQL'
SELECT
    MIN(t.final_price) AS final_price, 
    h.id AS id
FROM
    \Your\App\TourEntity t
JOIN
    t.hotel h
GROUP BY
    h.id
DQL
)
    ->setMaxResults(100)
    ->getResult();

Then run a second query against it.

@Ocramius commented on GitHub (Mar 9, 2018): Something like: ```php $ids = $em ->createQuery( <<<'DQL' SELECT MIN(t.final_price) AS final_price, h.id AS id FROM \Your\App\TourEntity t JOIN t.hotel h GROUP BY h.id DQL ) ->setMaxResults(100) ->getResult(); ``` Then run a second query against it.

admin commented

2026-01-22 15:21:57 +01:00

@kleinpetr commented on GitHub (Mar 9, 2018):

yes or maybe I can use something like this.

I have array, where I have h.id and his minimalPrice

And then I use second query with IN condition where I use the array of values the finish query could be

WHERE h.id = 10 AND final_price = 200 OR h.id = 12 AND final_price = 150
Its the same as that INNER JOIN no ?

but I dont know how can I use multiple IN() probably like this
WHERE (h.id, t.final_price) IN ((10,200),(12,150))

it will work in Criteria expr ??

@kleinpetr commented on GitHub (Mar 9, 2018): yes or maybe I can use something like this. I have array, where I have h.id and his minimalPrice And then I use second query with IN condition where I use the array of values the finish query could be WHERE h.id = 10 AND final_price = 200 OR h.id = 12 AND final_price = 150 Its the same as that INNER JOIN no ? but I dont know how can I use multiple IN() probably like this WHERE (h.id, t.final_price) IN ((10,200),(12,150)) it will work in Criteria expr ??

admin commented

2026-01-22 15:21:58 +01:00

@stollr commented on GitHub (Sep 10, 2018):

@kleinpetr sub-queries cannot be joined with in DQL, because the type of the subquery is not necessarily well-defined.

@Ocramius can you explain this a bit more? I have use cases where a join into a subquery is needed to get the correct results. For example in paginated lists where aggregated data should be shown.

@stollr commented on GitHub (Sep 10, 2018): > @kleinpetr sub-queries cannot be joined with in DQL, because the type of the subquery is not necessarily well-defined. @Ocramius can you explain this a bit more? I have use cases where a join into a subquery is needed to get the correct results. For example in paginated lists where aggregated data should be shown.

admin referenced this issue

2026-01-22 16:05:24 +01:00

[PR #5912] Clear code parser #9784

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5912