Thanks for the very quick reply! We appreciate it that you took some time
to look into the issue.
We see what you mean by the partial graph in the result, especially in the
example you provided.
However, it is not very clear to us whether his applies to our case as
well. In our test case, we have:
1. one *Offer* (filtered by id in the query)
2. exactly two related *Offerline*s (so the INNER JOIN matches the whole
set)
3. for each Offerline exactly one *Generalassuranceline*
4. for each *Generalassuranceline *exactly one *Possession* (which we
did not include in our query)
Changing our DQL to the following, to eliminate filtering by INNER JOIN,
gave us the same results:
SELECT
offer,
offerline,
generalassuranceline
FROM
Entity\Offer\Offer offer
LEFT JOIN offer.offerlines offerline
LEFT JOIN offerline.generalassuranceline generalassuranceline
WHERE
offer.id = 6749
Experimenting a bit after reading your reply, we tried extending our DQL
with the missing join to see if this would change anything. And indeed, the
full resultset is returned after adding the join:
SELECT
offer,
offerline,
generalassuranceline,
object
FROM
Entity\Offer\Offer offer
INNER JOIN offer.offerlines offerline
INNER JOIN offerline.generalassuranceline generalassuranceline
LEFT JOIN generalassuranceline.object object
WHERE
offer.id = 6749
However, this is not really the behaviour we expected. And this means in
our production software that we would have to extend pretty simple queries
with multiple extra joins just to make sure that the entire resultset is
filled nicely. In the example above, the subentities Offerlines are not
filled completely *unless* we join two more subentities
(generalassuranceline and object).
So, we have no clue why the missing join leads to an incomplete partial
result.
On Friday, December 29, 2017 at 3:48:43 PM UTC+1, Marco Pivetta wrote:
>
> What is happening seems to be incorrect hydration of a partial result.
>
> This is usually a problem with queries like following:
>
> SELECT a, b FROM A a JOIN a.b b WHERE b.something = 123;
>
> This query will select all A records with all its a.b populated, but will
> filter them by a restriction. The problem is that this will hydrate a
> partial graph into your object, and that object will afterwards remain in
> memory, broken until you clear the UnitOfWork.
>
> The fix is to not join in records and filter:
>
> SELECT a, b FROM A a LEFT JOIN a.b b JOIN a.b b2 WHERE b2.something = 123;
>
> In your case, the filtering is subtle because you used a JOIN condition
> twice, and that implicitly acts as restriction.
>
> Marco Pivetta
>
> http://twitter.com/Ocramius
>
> http://ocramius.github.com/
>
> On Fri, Dec 29, 2017 at 3:24 PM, <[email protected] <javascript:>> wrote:
>
>> We have some problems querying an entity with embedded sub entities.
>> First let me sketch the context:
>>
>> - We have a table with *Offer* entities
>> - Each *Offer* consists of *multiple* *Offerlines*
>> - Each *Offerline* has *one* related *Generalassuranceline*
>> - Each *Generalassuranceline* has *one* related *Possession*
>> - This *Possession* has a discriminator column defined to
>> distinguish two possession subtypes: *Vehicle* and *Misc*
>>
>> In the mapping of *Generalassuranceline* the *Possession* is defined as
>> a many-to-one relation (named 'object') to the generic *Possession* type,
>> because at the *Generalassuranceline* level the 'object' can be either a
>> *Vehicle* or a *Misc* entity.
>>
>> The detailed mappings of the entities are listed below.
>>
>>
>> The DQL query we execute is the following:
>>
>> SELECT
>> offer,
>> offerline,
>> generalassuranceline
>> FROM
>> Entity\Offer\Offer offer
>> INNER JOIN offer.offerlines offerline
>> INNER JOIN offerline.generalassuranceline generalassuranceline
>> WHERE
>> offer.id = 6749
>>
>> *Problem: *
>> In the database, the *offer* we query has two *offerlines*. In the
>> following situations, the output is generated by:
>>
>> \Doctrine\Common\Util\Debug::dump($query->getResult(), 3);
>>
>> Situation 1:
>> If we specify the "object" relation in *Generalassuranceline* as type
>> *Possession*, we only get one *offerline* entity in our result.
>> array(1) {
>> [0]=>
>> object(stdClass)#27 (5) {
>> ["__CLASS__"]=>
>> string(18) "Entity\Offer\Offer"
>> ["id"]=>
>> int(6749)
>> ["offernumber"]=>
>> string(8) "98037192"
>> ["description"]=>
>> string(13) "auto 29-KQB-3"
>> ["offerlines"]=>
>> * array*
>>
>>
>> *(1) { [0]=> string(22) "Entity\Offer\Offerline" }*
>> }
>> }
>>
>>
>> Situation 2:
>> If we specify the "object" relation in *Generalassuranceline* as type
>> *Vehicle*, we get both the *offerline* entities as expected.
>> array(1) {
>> [0]=>
>> object(stdClass)#27 (5) {
>> ["__CLASS__"]=>
>> string(18) "Entity\Offer\Offer"
>> ["id"]=>
>> int(6749)
>> ["offernumber"]=>
>> string(8) "98037192"
>> ["description"]=>
>> string(13) "auto 29-KQB-3"
>> ["offerlines"]=>
>> * array*
>>
>>
>>
>>
>> *(2) { [0]=> string(22) "Entity\Offer\Offerline" [1]=>
>> string(22) "Entity\Offer\Offerline" }*
>> }
>> }
>>
>>
>> Can someone explain to us the reason why we get only one *offerline* entity
>> in the *offer* result in situation 1 and what we can change to retrieve
>> more reliable results?
>>
>>
>> *Mappings:*
>>
>> *Offer*
>> <?xml version="1.0" encoding="utf-8"?>
>> <doctrine-mapping
>> xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping";
>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
>> xsi:schemaLocation="
>> http://doctrine-project.org/schemas/orm/doctrine-mapping
>> http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd";>
>>
>> <entity name="Entity\Offer\Offer" table="accountingoffer">
>>
>> <id name="id" type="integer" column="id">
>> <generator strategy="IDENTITY"/>
>> </id>
>>
>> <field name="offernumber" type="string" column="offernumber" length=
>> "255" nullable="true" />
>> <field name="description" type="string" column="description" length=
>> "255" nullable="true" />
>>
>> <one-to-many field="offerlines" target-entity=
>> "Entity\Offer\Offerline" mapped-by="offer" fetch="LAZY" />
>> </entity>
>> </doctrine-mapping>
>>
>>
>> *Offerline*
>> <?xml version="1.0" encoding="utf-8"?>
>> <doctrine-mapping
>> xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping";
>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
>> xsi:schemaLocation="
>> http://doctrine-project.org/schemas/orm/doctrine-mapping
>> http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd";>
>>
>> <entity name="Entity\Offer\Offerline" table="accountingofferline">
>>
>> <id name="id" type="integer" column="id">
>> <generator strategy="IDENTITY"/>
>> </id>
>>
>> <field name="offernumber" type="string" column="offernumber" length=
>> "255" nullable="true" />
>>
>> <many-to-one field="offer" target-entity="Entity\Offer\Offer"
>> inversed-by="offerlines">
>> <join-column name="accountingoffer_id" referenced-column-name=
>> "id"/>
>> </many-to-one>
>>
>> <one-to-one field="generalassuranceline" target-entity=
>> "Entity\Offer\Generalassuranceline" mapped-by="offerline" />
>> </entity>
>> </doctrine-mapping>
>>
>>
>> *Generalassuranceline*
>> <?xml version="1.0" encoding="utf-8"?>
>> <doctrine-mapping
>> xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping";
>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
>> xsi:schemaLocation="
>> http://doctrine-project.org/schemas/orm/doctrine-mapping
>> http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd";>
>>
>> <entity name="Entity\Offer\Generalassuranceline" table=
>> "accountingofferline_generalassurance">
>>
>> <id name="offerlineId" type="integer" column="accountingofferline_id"
>> >
>> <generator strategy="IDENTITY"/>
>> </id>
>>
>> <one-to-one field="offerline" target-entity="Entity\Offer\Offerline"
>> inversed-by="generalassuranceline">
>> <join-column name="accountingofferline_id" referenced-column-name
>> ="id"/>
>> </one-to-one>
>>
>> <many-to-one field="object" target-entity=
>> "Entity\Possession\Possession" fetch="LAZY">
>> <join-column name="crmpossession_id" referenced-column-name="id"
>> />
>> </many-to-one>
>> </entity>
>> </doctrine-mapping>
>>
>>
>> *Possession*
>> <?xml version="1.0" encoding="utf-8"?>
>> <doctrine-mapping
>> xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping";
>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
>> xsi:schemaLocation="
>> http://doctrine-project.org/schemas/orm/doctrine-mapping
>> http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd";>
>>
>> <entity name="Entity\Possession\Possession" table="crmpossession"
>> inheritance-type="SINGLE_TABLE">
>>
>> <id name="id" type="integer" column="id">
>> <generator strategy="IDENTITY"/>
>> </id>
>>
>> <discriminator-column name="crmpossessiontypecategory_id" type=
>> "integer" />
>> <discriminator-map>
>> <discriminator-mapping value="1" class=
>> "Entity\Possession\Vehicle" />
>> <discriminator-mapping value="2" class="Entity\Possession\Misc"
>> />
>> </discriminator-map>
>>
>> <field name="description" type="string" column="description" length=
>> "255" nullable="true" />
>> </entity>
>> </doctrine-mapping>
>>
>>
>> *Vehicle*
>> <?xml version="1.0" encoding="utf-8"?>
>> <doctrine-mapping
>> xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping";
>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
>> xsi:schemaLocation="
>> http://doctrine-project.org/schemas/orm/doctrine-mapping
>> http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd";>
>>
>> <entity name="Entity\Possession\Vehicle" />
>>
>> </doctrine-mapping>
>>
>>
>> *Misc*
>> <?xml version="1.0" encoding="utf-8"?>
>> <doctrine-mapping
>> xmlns="http://doctrine-project.org/schemas/orm/doctrine-mapping";
>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
>> xsi:schemaLocation="
>> http://doctrine-project.org/schemas/orm/doctrine-mapping
>> http://doctrine-project.org/schemas/orm/doctrine-mapping.xsd";>
>>
>> <entity name="Entity\Possession\Misc" />
>>
>> </doctrine-mapping>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "doctrine-user" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> To post to this group, send email to [email protected]
>> <javascript:>.
>> Visit this group at https://groups.google.com/group/doctrine-user.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
You received this message because you are subscribed to the Google Groups
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
