fetch-joined native sql associations do not seem to function as described #5938

New Issue

admin · 2026-01-22T15:22:34+01:00

admin commented

2026-01-22 15:22:34 +01:00

Originally created by @yurtesen on GitHub (Mar 29, 2018).

In documentation examples is a nice example of how to map native SQL with a join. I mean the example right under the line Consequently, associations that are fetch-joined do not require the foreign keys to be present in the SQL result set, only associations that are lazy.

However I found out that this does not seem to work. 3 months ago I made a question at stackoverflow with 0 responses. I now managed to solve the issue but it seems to conflict with what documentation says.

I have 2 tables. store and store_product.

In Store entity I have:

/**
 * @ORM\OneToMany(targetEntity="AppBundle\Entity\Store\Product", mappedBy="store")
 * @ORM\JoinColumn(name="store_id")
 */
private $products;

and in Store\Product entity I have composite index with (store_id,product_id). I have (also price field, which is omitted below for brevity):

/**
 * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Store", inversedBy="products")
 * @ORM\JoinColumn(name="store_id",referencedColumnName="id",nullable=false,onDelete="CASCADE")
 * @ORM\Id()
 * @ORM\GeneratedValue("NONE")
 * @var $store \AppBundle\Entity\Store
 */
protected $store;

/**
 * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Product", inversedBy="stores")
 * @ORM\JoinColumn(name="product_id",referencedColumnName="id",nullable=false,onDelete="CASCADE")
 * @ORM\Id()
 * @ORM\GeneratedValue("NONE")
 * @var $product \AppBundle\Entity\Product
 */
protected $product;

I am trying to join store_product table. I am using the following query which returns 1 result as the test case.

SELECT st.id, st.name, stp.store_id, stp.product_id, stp.price FROM store st LEFT JOIN store_product stp ON st.id = stp.store_id WHERE st.id=1 LIMIT 1;

returns something like:

 id |    name    | store_id | product_id | price 
----+------------+----------+------------+-------
  1 | Store Name |        1 | 1234567890 |   129

I setup the result set mapping as follows:

    $rsm = new ResultSetMapping();
            $rsm->addEntityResult('AppBundle\Entity\Store', 'st');
            $rsm->addFieldResult('st', 'id', 'id');
            $rsm->addFieldResult('st', 'name', 'name');
            $rsm->addJoinedEntityResult('AppBundle\Entity\Store\Product', 'stp',
    'st', 'products');
            $rsm->addFieldResult('stp','store_id', 'store');
            $rsm->addFieldResult('stp','product_id','product');
            $rsm->addFieldResult('stp','price','price');

I am getting error: Notice: Undefined index: store I tried several different variations, such as changing store with id etc. and result is either empty product association or error.

The only way to fix the issue was to replace the lines:
$rsm->addFieldResult('stp','store_id', 'store');
$rsm->addFieldResult('stp','product_id','product');
with:
$rsm->addMetaResult('stp', 'store_id', 'store_id', true);
$rsm->addMetaResult('stp', 'product_id', 'product_id', true);
and I had to set the 3rd $isIdentifierColumn parameter to true.

Documentation says when query readily fetches the results, there is no need for foreign keys. So this is a bug or what is going on?

Originally created by @yurtesen on GitHub (Mar 29, 2018). In [documentation examples](http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/native-sql.html#examples) is a nice example of how to map native SQL with a join. I mean the example right under the line `Consequently, associations that are fetch-joined do not require the foreign keys to be present in the SQL result set, only associations that are lazy.` However I found out that this does not seem to work. 3 months ago I made a [question at stackoverflow](https://stackoverflow.com/questions/47734071) with 0 responses. I now managed to solve the issue but it seems to conflict with what documentation says. I have 2 tables. `store` and `store_product`. In `Store` entity I have: /** * @ORM\OneToMany(targetEntity="AppBundle\Entity\Store\Product", mappedBy="store") * @ORM\JoinColumn(name="store_id") */ private $products; and in `Store\Product` entity I have composite index with (store_id,product_id). I have (also price field, which is omitted below for brevity): /** * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Store", inversedBy="products") * @ORM\JoinColumn(name="store_id",referencedColumnName="id",nullable=false,onDelete="CASCADE") * @ORM\Id() * @ORM\GeneratedValue("NONE") * @var $store \AppBundle\Entity\Store */ protected $store; /** * @ORM\ManyToOne(targetEntity="AppBundle\Entity\Product", inversedBy="stores") * @ORM\JoinColumn(name="product_id",referencedColumnName="id",nullable=false,onDelete="CASCADE") * @ORM\Id() * @ORM\GeneratedValue("NONE") * @var $product \AppBundle\Entity\Product */ protected $product; I am trying to join `store_product` table. I am using the following query which returns 1 result as the test case. `SELECT st.id, st.name, stp.store_id, stp.product_id, stp.price FROM store st LEFT JOIN store_product stp ON st.id = stp.store_id WHERE st.id=1 LIMIT 1;` returns something like: id | name | store_id | product_id | price ----+------------+----------+------------+------- 1 | Store Name | 1 | 1234567890 | 129 I setup the result set mapping as follows: $rsm = new ResultSetMapping(); $rsm->addEntityResult('AppBundle\Entity\Store', 'st'); $rsm->addFieldResult('st', 'id', 'id'); $rsm->addFieldResult('st', 'name', 'name'); $rsm->addJoinedEntityResult('AppBundle\Entity\Store\Product', 'stp', 'st', 'products'); $rsm->addFieldResult('stp','store_id', 'store'); $rsm->addFieldResult('stp','product_id','product'); $rsm->addFieldResult('stp','price','price'); I am getting error: `Notice: Undefined index: store` I tried several different variations, such as changing `store` with `id` etc. and result is either empty product association or error. The only way to fix the issue was to replace the lines: $rsm->addFieldResult('stp','store_id', 'store'); $rsm->addFieldResult('stp','product_id','product'); with: $rsm->addMetaResult('stp', 'store_id', 'store_id', true); $rsm->addMetaResult('stp', 'product_id', 'product_id', true); and I had to set the 3rd `$isIdentifierColumn` parameter to true. Documentation says when query readily fetches the results, there is no need for foreign keys. So this is a bug or what is going on?

admin commented

2026-01-22 15:22:36 +01:00

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

Identifiers are indeed meta results, not field results. Can you maybe link the portion of the docs that you found to be buggy? See 1afbf141fc/docs

@Ocramius commented on GitHub (Mar 29, 2018): Identifiers are indeed meta results, not field results. Can you maybe link the portion of the docs that you found to be buggy? See https://github.com/doctrine/doctrine2/tree/1afbf141fc78e34467bafdad493ecec831b3d6b0/docs

admin commented

2026-01-22 15:22:36 +01:00

@yurtesen commented on GitHub (Mar 29, 2018):

4509c770da/docs/en/reference/native-sql.rst
Example just under the text Consequently, associations that are fetch-joined do not require the foreign keys to be present in the SQL result set, only associations that are lazy.
There it uses $rsm->addFieldResult('a', 'address_id', 'id'); for the id values. Hmm?

@yurtesen commented on GitHub (Mar 29, 2018): https://github.com/doctrine/doctrine2/blob/4509c770da4dbed1568ddd3f89bd0663c01ef059/docs/en/reference/native-sql.rst Example just under the text `Consequently, associations that are fetch-joined do not require the foreign keys to be present in the SQL result set, only associations that are lazy.` There it uses `$rsm->addFieldResult('a', 'address_id', 'id');` for the id values. Hmm?

admin commented

2026-01-22 15:22:36 +01:00

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

Hmm, yeah, that looks confusing and also confusing to read.

associations that are fetch-joined do not require the foreign keys to be present in the SQL result set, only associations that are lazy.

What happens if you only fetch the FK (from the owning side of the association) is that a proxy will be instantiated and kept un-initialized. Also, I think the examples were not designed with one-to-many in mind. In your case, @ORM\Id() and @ORM\ManyToOne() appear to be on the same field, in which case a ResultSetMapping#addMetaResult() is required to create an object reference, and you don't have ResultSetMapping#addFieldResult() because that's not a field, but an association.

Does that make sense?

@Ocramius commented on GitHub (Mar 29, 2018): Hmm, yeah, that looks confusing and also confusing to read. > `associations that are fetch-joined do not require the foreign keys to be present in the SQL result set, only associations that are lazy.` What happens if you only fetch the FK (from the owning side of the association) is that a proxy will be instantiated and kept un-initialized. Also, I think the examples were not designed with one-to-many in mind. In your case, `@ORM\Id()` and `@ORM\ManyToOne()` appear to be on the same field, in which case a `ResultSetMapping#addMetaResult()` is required to create an object reference, and you don't have `ResultSetMapping#addFieldResult()` because that's not a field, but an association. Does that make sense?

admin commented

2026-01-22 15:22:38 +01:00

@yurtesen commented on GitHub (Mar 29, 2018):

From documentation what I understood was that Address and User had one-to-one relation. When the example uses addJoinedEntityResult() I understood that it will make an object reference to Address. It says Address is hydrated into the User::$address property. So actually are you sure the code in the example even works?

In either case, documentation is perhaps unnecessarily confusing. Is there any reason why one shouldn't use only addMetaResult() even if it is NOT lazy fetched? For example in my case I use addMetaResult() and doctrine does not seem to re-fetch the joined entity. When I query $products from Store, I get only the 1 product I selected in the query. It is NOT using the proxy functionality.

@yurtesen commented on GitHub (Mar 29, 2018): From documentation what I understood was that `Address` and `User` had one-to-one relation. When the example uses `addJoinedEntityResult()` I understood that it will make an object reference to Address. It says `Address is hydrated into the User::$address property.` So actually are you sure the code in the example even works? In either case, documentation is perhaps unnecessarily confusing. Is there any reason why one shouldn't use only `addMetaResult()` even if it is NOT lazy fetched? For example in my case I use `addMetaResult()` and doctrine does not seem to re-fetch the joined entity. When I query `$products` from Store, I get only the 1 product I selected in the query. It is NOT using the proxy functionality.

admin commented

2026-01-22 15:22:38 +01:00

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

So actually are you sure the code in the example even works?

No, I'm not, but I won't get to check it either right now.

In either case, documentation is perhaps unnecessarily confusing.

Agreed: the entire section should be trashed and made part of the ResultSetMapping docblocks instead.

Is there any reason why one shouldn't use only addMetaResult() even if it is NOT lazy fetched?

An addMetaResult() is always needed when a fetched resultset field is actually a reference: doesn't matter if lazy or not.

It is NOT using the proxy functionality.

Correct: the hydration already happened, so no proxying needed.

@Ocramius commented on GitHub (Mar 29, 2018): > So actually are you sure the code in the example even works? No, I'm not, but I won't get to check it either right now. > In either case, documentation is perhaps unnecessarily confusing. Agreed: the entire section should be trashed and made part of the `ResultSetMapping` docblocks instead. > Is there any reason why one shouldn't use only `addMetaResult()` even if it is NOT lazy fetched? An `addMetaResult()` is always needed when a fetched resultset field is actually a reference: doesn't matter if lazy or not. > It is NOT using the proxy functionality. Correct: the hydration already happened, so no proxying needed.

admin commented

2026-01-22 15:22:38 +01:00

@yurtesen commented on GitHub (Apr 3, 2018):

@Ocramius sorry I was sick and couldn't respond. Thanks for your responses. Hopefully this at least would be useful to somebody who has the same problem or at some point the documentation could be updated. Thanks!

@yurtesen commented on GitHub (Apr 3, 2018): @Ocramius sorry I was sick and couldn't respond. Thanks for your responses. Hopefully this at least would be useful to somebody who has the same problem or at some point the documentation could be updated. Thanks!

admin referenced this issue

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

[PR #5938] #5934 - add `fetch` option to `AssociationOverride` in order to override fetch strategy for subclasses of entities #9795

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#5938