Discussion:
[doctrine-user] Problem with subentities with discriminator column in query result
f***@gmail.com
2017-12-29 14:24:58 UTC
Permalink
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 doctrine-user+***@googlegroups.com.
To post to this group, send email to doctrine-***@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
Marco Pivetta
2017-12-29 14:48:17 UTC
Permalink
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/
Post by f***@gmail.com
We have some problems querying an entity with embedded sub entities. First
- 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.
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
\Doctrine\Common\Util\Debug::dump($query->getResult(), 3);
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" }*
}
}
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
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 doctrine-user+***@googlegroups.com.
To post to this group, send email to doctrine-***@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
f***@gmail.com
2017-12-29 16:08:57 UTC
Permalink
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.
Post by Marco Pivetta
What is happening seems to be incorrect hydration of a partial result.
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.
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/
Post by f***@gmail.com
We have some problems querying an entity with embedded sub entities.
- 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.
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
\Doctrine\Common\Util\Debug::dump($query->getResult(), 3);
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" }*
}
}
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
<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 doctrine-user+***@googlegroups.com.
To post to this group, send email to doctrine-***@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
Marco Pivetta
2018-01-26 11:01:40 UTC
Permalink
A `LEFT JOIN` is a join that doesn't restrict the results, while a `JOIN`
(or `INNER JOIN`) restricts the results. Think of it as a hidden "WHERE"
condition :-)

Marco Pivetta

http://twitter.com/Ocramius

http://ocramius.github.com/
Post by f***@gmail.com
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
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,
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
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.
Post by Marco Pivetta
What is happening seems to be incorrect hydration of a partial result.
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.
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/
Post by f***@gmail.com
We have some problems querying an entity with embedded sub entities.
- 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.
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
\Doctrine\Common\Util\Debug::dump($query->getResult(), 3);
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" }*
}
}
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/sc
hemas/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\Of
ferline" 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/sc
hemas/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/sc
hemas/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\Possessi
on\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/sc
hemas/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\Vehic
le" />
<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/sc
hemas/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/sc
hemas/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
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
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 doctrine-user+***@googlegroups.com.
To post to this group, send email to doctrine-***@googlegroups.com.
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
Loading...