Left join in class table inheritance on every query #5220

New Issue

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

admin commented

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

Originally created by @jirinapravnik on GitHub (Aug 17, 2016).

Originally assigned to: @Ocramius on GitHub.

I use Class Table Inheritance for article, like this:

/**
 * @ORM\Entity
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="discriminator", type="string")
 * @ORM\DiscriminatorMap({
 * "article" = "Article",
 * "videoArticle" = "VideoArticle"
 * })
 * @ORM\Table(name = "tArticle")
 */

and

VidoArticle extends Article

Problem is, when I ask only for article, there is also left join for videoArticle. It is quite large project, and every left join slows down preformance...

For example I ask only last five ids of articles, or I ask for count of all articles. And even in these cases "left join" is there.

SELECT COUNT(e0_.id) AS sclr_0 
FROM article e0_ 
LEFT JOIN video_article e1_ ON e0_.id = e1_.id 
WHERE e0_.publish =TRUE AND e0_.date_published < '2016-08-17
04:44:01'

Originally created by @jirinapravnik on GitHub (Aug 17, 2016). Originally assigned to: @Ocramius on GitHub. I use Class Table Inheritance for article, like this: ``` php /** * @ORM\Entity * @ORM\InheritanceType("JOINED") * @ORM\DiscriminatorColumn(name="discriminator", type="string") * @ORM\DiscriminatorMap({ * "article" = "Article", * "videoArticle" = "VideoArticle" * }) * @ORM\Table(name = "tArticle") */ ``` and VidoArticle extends Article Problem is, when I ask only for article, there is also left join for videoArticle. It is quite large project, and every left join slows down preformance... For example I ask only last five ids of articles, or I ask for count of all articles. And even in these cases "left join" is there. ``` mysql SELECT COUNT(e0_.id) AS sclr_0 FROM article e0_ LEFT JOIN video_article e1_ ON e0_.id = e1_.id WHERE e0_.publish =TRUE AND e0_.date_published < '2016-08-17 04:44:01' ```

admin added the Invalid label 2026-01-22 15:01:52 +01:00

admin closed this issue

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

admin commented

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

@w3sami commented on GitHub (Aug 24, 2016):

yes, this is the way doctrine does this. when performance is essential ,you can get the type from the parent table and query only from the child table by hand. also, maybe some sort of caching is needed, orm will always be slower than raw sql for eaxample

@w3sami commented on GitHub (Aug 24, 2016): yes, this is the way doctrine does this. when performance is essential ,you can get the type from the parent table and query only from the child table by hand. also, maybe some sort of caching is needed, orm will always be slower than raw sql for eaxample

admin commented

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

@jirinapravnik commented on GitHub (Aug 24, 2016):

@w3sami I understand, orm is slower. That is not problem. But in some cases I need only get items from parent table and I am not interested if it is article/videoarticle child... For example count of all articles, it is nonsense to join all child tables and it is also very slow, if I have many rows in tables.

Is it possible to do it better than via NativeQuery?

@jirinapravnik commented on GitHub (Aug 24, 2016): @w3sami I understand, orm is slower. That is not problem. But in some cases I need only get items from parent table and I am not interested if it is article/videoarticle child... For example count of all articles, it is nonsense to join all child tables and it is also very slow, if I have many rows in tables. Is it possible to do it better than via NativeQuery?

admin commented

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

@w3sami commented on GitHub (Aug 24, 2016):

think of the parent child relation in terms of php objects not database rows. inheritance means that the parent is the abstract class, and cannot be instantiated on its own.

so just do a plain dql query with a count / any other data u need

@w3sami commented on GitHub (Aug 24, 2016): think of the parent child relation in terms of php objects not database rows. inheritance means that the parent is the abstract class, and cannot be instantiated on its own. so just do a plain dql query with a count / any other data u need

admin commented

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

@jirinapravnik commented on GitHub (Aug 24, 2016):

The parent is not abstract class. Look at my DiscriminatorMap in first comment. I have class Article and class VideoArticle extends Article. Both are instantiable.

I want count all articles. DQL is:

SELECT COUNT(a) FROM Article a

And generated SQL is:

SELECT COUNT(e0_.id) AS sclr_0 
FROM article e0_ 
LEFT JOIN video_article e1_ ON e0_.id = e1_.id

IMHO left join is useless

@jirinapravnik commented on GitHub (Aug 24, 2016): The parent is not abstract class. Look at my DiscriminatorMap in first comment. I have class Article and class VideoArticle extends Article. Both are instantiable. I want count all articles. DQL is: ``` SELECT COUNT(a) FROM Article a ``` And generated SQL is: ``` SELECT COUNT(e0_.id) AS sclr_0 FROM article e0_ LEFT JOIN video_article e1_ ON e0_.id = e1_.id ``` IMHO left join is useless

admin commented

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

@w3sami commented on GitHub (Aug 24, 2016):

I was talking about how to look at things the orm way. you might have an instantiable class, but the pattern should be analogous to of php extending. so you cannot get a parent only. it's in the manuals. im not in any way making doctrine myself, just filed a bug related to inheritance today and offered you my knowledge.

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/inheritance-mapping.html
see the note
"A mapped superclass cannot be an entity"

in your case i suggest you make a manual query to just the parent, as the orm cannot instantiate just the base as a class.

@w3sami commented on GitHub (Aug 24, 2016): I was talking about how to look at things the orm way. you might have an instantiable class, but the pattern should be analogous to of php extending. so you cannot get a parent only. it's in the manuals. im not in any way making doctrine myself, just filed a bug related to inheritance today and offered you my knowledge. http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/inheritance-mapping.html see the note "A mapped superclass cannot be an entity" in your case i suggest you make a manual query to just the parent, as the orm cannot instantiate just the base as a class.

admin commented

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

@jirinapravnik commented on GitHub (Aug 24, 2016):

But that note about Mapped supperclass in docs, is only about MappedSuperclass, which is really only abstract class - non-instantiable. I use Class Table Inheritance.

IMHO it should be default joining all child classes, but maybe it could allow turn it off via some hint for example, or something like that.

@jirinapravnik commented on GitHub (Aug 24, 2016): But that note about Mapped supperclass in docs, is only about MappedSuperclass, which is really only abstract class - non-instantiable. I use Class Table Inheritance. IMHO it should be default joining all child classes, but maybe it could allow turn it off via some hint for example, or something like that.

admin commented

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

@w3sami commented on GitHub (Aug 24, 2016):

ah yes sorry, you're right about the documentation. And that it could be better. In our code, theres a table document that contains almost 10 different type contracts and the left joining is really heavy.
That's why we implement the dql mentioned, to first get the type and the just a id search on the child table, without the dumb left joins. and also cache by elasticsearch on top of doctrine

having said that, I still consider doctrine to be good software for a big and problematic issue: orm

@w3sami commented on GitHub (Aug 24, 2016): ah yes sorry, you're right about the documentation. And that it could be better. In our code, theres a table document that contains almost 10 different type contracts and the left joining is really heavy. That's why we implement the dql mentioned, to first get the type and the just a id search on the child table, without the dumb left joins. and also cache by elasticsearch on top of doctrine having said that, I still consider doctrine to be good software for a big and problematic issue: orm

admin commented

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

@Ocramius commented on GitHub (Aug 24, 2016):

Closing, since this is basically the same as #5961.

If this is to be made a documentation issue, please simply send a PR against the documentation, or ask for explicit clarification there.

If you want to fetch data only from a parent table, then doctrine won't be very helpful there, and you will need to resort to raw SQL instead. From an PHP OOP perspective, fetching a parent is exactly the same as fetching the parent and all its children (since they are equivalent, according to LSP).

@Ocramius commented on GitHub (Aug 24, 2016): Closing, since this is basically the same as #5961. If this is to be made a documentation issue, please simply send a PR against the documentation, or ask for explicit clarification there. If you want to fetch data only from a parent table, then doctrine won't be very helpful there, and you will need to resort to raw SQL instead. From an PHP OOP perspective, fetching a parent is exactly the same as fetching the parent and all its children (since they are equivalent, according to LSP).

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