DDC-821: Consider adding Query-Join as another join method for DQL #1011

New Issue

admin · 2026-01-22T12:58:53+01:00

admin commented

2026-01-22 12:58:53 +01:00

Originally created by @doctrinebot on GitHub (Sep 29, 2010).

Originally assigned to: @guilhermeblanco on GitHub.

Jira issue originally created by user mjh_ca:

Some ORM systems support an alternative to fetch-join queries, called a "query-join". See [http://www.avaje.org/ebean/introquery_joinquery.html].

A query-join accomplishes the same as a fetch-join (hydrating a larger object graph across all associations types) but executes more than one SQL query in sequence in order to hydrate the requested portions of the graph in a result set. The first query retrieves data from the base entity/table and the next queries retrieve the data for the requested associations.

In some cases this approach is more efficient to a fetch-join:

(1) No data duplication in the SQL result as occurs in a fetch-join on to-many associations. Instead, this data is loaded through a second query. This saves network traffic, memory and general overhead in hydrating the returned results. In the case where large TEXT data is included in result sets, the savings here may be substantial.

(2) setFirstResult() and setMaxResult() are again effective (for pagination) and more importantly more efficient on these query-joins. The current DoctrineExtension solution to enable pagination on fetch-joins requires a series of queries to determine the target primary keys of the root entity of the query. The primary key lookup query requires DISTINCT or GROUP BY -- which often triggers filesorts, temporary tables, etc (at least on MySQL) and greatly slows down the query. Query joins would not require this.

Possible implementation example:


// existing fetch-join
$query = $em->createQuery('SELECT c, o FROM Customers c JOIN c.orders o');
$query->setFirstResult(10)->setMaxResult(20); // doesn't do what you'd hope it would do, no ability to use this for pagination
$customersAndOrders = $query->getResult(); // array of Customer objects with Orders hydrated

// proposed query-join
$query = $em->createQuery('SELECT c, o FROM Customers c QUERY JOIN c.orders o');
$query->setFirstResult(10)->setMaxResult(20); // now works for pagination
$customersAndOrders = $query->getResult(); // array of Customer objects with Orders hyrdated
// this would execute a series of queries -- i.e. in SQL
// SELECT ... FROM customers LIMIT 10, 20
// SELECT ... FROM orders WHERE customer_id IN (.....)

and/or, could there be a way to trigger a "query-join" against an existing array of entities? for example

$query = $em->createQuery('SELECT c FROM Customers c'); // single query to fetch customers
$customers = $query->getResult(); // array of Customer objects
$em->join($customers, 'orders'); // fetch and hydrate the 'orders' association on each Customer using a single query

Perhaps at some point in the future Doctrine/DBAL could even make use of asynchronous queries (i.e. mysqlnd supports this) to allow these query-joins to run in parallel and the result would be more efficient paginated resultsets.

Thoughts/feedback?

Originally created by @doctrinebot on GitHub (Sep 29, 2010). Originally assigned to: @guilhermeblanco on GitHub. Jira issue originally created by user mjh_ca: Some ORM systems support an alternative to fetch-join queries, called a "query-join". See [http://www.avaje.org/ebean/introquery_joinquery.html]. A query-join accomplishes the same as a fetch-join (hydrating a larger object graph across all associations types) but executes more than one SQL query in sequence in order to hydrate the requested portions of the graph in a result set. The first query retrieves data from the base entity/table and the next queries retrieve the data for the requested associations. In some cases this approach is more efficient to a fetch-join: (1) **No data duplication in the SQL result** as occurs in a fetch-join on to-many associations. Instead, this data is loaded through a second query. This saves network traffic, memory and general overhead in hydrating the returned results. In the case where large TEXT data is included in result sets, the savings here may be substantial. (2) **setFirstResult() and setMaxResult() are again effective (for pagination)** and more importantly more efficient on these query-joins. The current DoctrineExtension solution to enable pagination on fetch-joins requires a series of queries to determine the target primary keys of the root entity of the query. The primary key lookup query requires DISTINCT or GROUP BY -- which often triggers filesorts, temporary tables, etc (at least on MySQL) and greatly slows down the query. Query joins would not require this. Possible implementation example: ``` // existing fetch-join $query = $em->createQuery('SELECT c, o FROM Customers c JOIN c.orders o'); $query->setFirstResult(10)->setMaxResult(20); // doesn't do what you'd hope it would do, no ability to use this for pagination $customersAndOrders = $query->getResult(); // array of Customer objects with Orders hydrated // proposed query-join $query = $em->createQuery('SELECT c, o FROM Customers c QUERY JOIN c.orders o'); $query->setFirstResult(10)->setMaxResult(20); // now works for pagination $customersAndOrders = $query->getResult(); // array of Customer objects with Orders hyrdated // this would execute a series of queries -- i.e. in SQL // SELECT ... FROM customers LIMIT 10, 20 // SELECT ... FROM orders WHERE customer_id IN (.....) ``` and/or, could there be a way to trigger a "query-join" against an existing array of entities? for example ``` $query = $em->createQuery('SELECT c FROM Customers c'); // single query to fetch customers $customers = $query->getResult(); // array of Customer objects $em->join($customers, 'orders'); // fetch and hydrate the 'orders' association on each Customer using a single query ``` Perhaps at some point in the future Doctrine/DBAL could even make use of asynchronous queries (i.e. mysqlnd supports this) to allow these query-joins to run in parallel and the result would be more efficient paginated resultsets. Thoughts/feedback?

admin added the New Feature label 2026-01-22 12:58:53 +01:00

admin closed this issue

2026-01-22 12:58:53 +01:00

admin commented

2026-01-22 12:58:54 +01:00

@doctrinebot commented on GitHub (Sep 30, 2010):

Comment created by @beberlei:

There is another approach for this using several subqueries to build an IN clause, the Paginator extension supports this: http://github.com/beberlei/DoctrineExtensions

I rather go the extension approach than changing the DQL for this feature.

@doctrinebot commented on GitHub (Sep 30, 2010): Comment created by @beberlei: There is another approach for this using several subqueries to build an IN clause, the Paginator extension supports this: http://github.com/beberlei/DoctrineExtensions I rather go the extension approach than changing the DQL for this feature.

admin commented

2026-01-22 12:58:55 +01:00

@doctrinebot commented on GitHub (Sep 30, 2010):

Comment created by @beberlei:

I just saw your second example, that is rather cool though and gets +1 from me.

I had the same idea for "not initialized proxies", i.e.

$em->getUnitOfWork()->initializeProxies('Customer');

@doctrinebot commented on GitHub (Sep 30, 2010): Comment created by @beberlei: I just saw your second example, that is rather cool though and gets +1 from me. I had the same idea for "not initialized proxies", i.e. ``` $em->getUnitOfWork()->initializeProxies('Customer'); ```

admin commented

2026-01-22 12:58:56 +01:00

@doctrinebot commented on GitHub (Dec 29, 2010):

Comment created by mjh_ca:

Second example is a duplicate of DDC-734

@doctrinebot commented on GitHub (Dec 29, 2010): Comment created by mjh_ca: Second example is a duplicate of [DDC-734](http://www.doctrine-project.org/jira/browse/DDC-734)

admin commented

2026-01-22 12:58:57 +01:00

@doctrinebot commented on GitHub (Jul 30, 2013):

Comment created by @guilhermeblanco:

Fixed based on this related issue: http://www.doctrine-project.org/jira/browse/DDC-734

@doctrinebot commented on GitHub (Jul 30, 2013): Comment created by @guilhermeblanco: Fixed based on this related issue: http://www.doctrine-project.org/jira/browse/[DDC-734](http://www.doctrine-project.org/jira/browse/DDC-734)

admin commented

2026-01-22 12:58:58 +01:00

@doctrinebot commented on GitHub (Jul 30, 2013):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Jul 30, 2013): Issue was closed with resolution "Fixed"

admin referenced this issue

2026-01-22 16:02:52 +01:00

[PR #1011] [MERGED] Single quotes can't nest #9031

admin referenced this issue

2026-01-22 16:02:52 +01:00

[PR #1011] Single quotes can't nest #9034

Sign in to join this conversation.