DDC-16: DQL Ignores properties of subclasses #21

Closed
opened 2026-01-22 12:24:18 +01:00 by admin · 28 comments
Owner

Originally created by @doctrinebot on GitHub (Sep 15, 2009).

Originally assigned to: @Ocramius on GitHub.

Jira issue originally created by user ablock:

I have a classes B and C which inherit from superclass A. I would like
to get a list of all A's but filter the list to ignore those in C
which have a property d set to 2.

select a from A where a.d == 2 fails because "d" is not a property of A.

Originally created by @doctrinebot on GitHub (Sep 15, 2009). Originally assigned to: @Ocramius on GitHub. Jira issue originally created by user ablock: I have a classes B and C which inherit from superclass A. I would like to get a list of all A's but filter the list to ignore those in C which have a property d set to 2. select a from A where a.d == 2 fails because "d" is not a property of A.
admin added the Improvement label 2026-01-22 12:24:19 +01:00
admin closed this issue 2026-01-22 12:24:19 +01:00
Author
Owner

@doctrinebot commented on GitHub (Sep 15, 2009):

@doctrinebot commented on GitHub (Sep 15, 2009): - is duplicated by [DDC-1377: Doctrine doesn't understand associations from SINGLE_TABLE inheritances](http://www.doctrine-project.org/jira/browse/DDC-1377) - is duplicated by [DDC-3342: Join with child tables](http://www.doctrine-project.org/jira/browse/DDC-3342)
Author
Owner

@doctrinebot commented on GitHub (Mar 31, 2010):

Comment created by romanb:

Thats indeed tricky. That syntax alone can, however, never work, because there might be several subclasses that have a field named "d", so Doctrine would not know which field you mean.

We might need special syntax for such constructs but I'm not sure it is worth it. If anyone has an idea, just shoot.

Alternatively, apart from using a native query, what about this DQL:

select a from A a where a.id not in (select c.id from C c where c.d = 2)
@doctrinebot commented on GitHub (Mar 31, 2010): Comment created by romanb: Thats indeed tricky. That syntax alone can, however, never work, because there might be several subclasses that have a field named "d", so Doctrine would not know which field you mean. We might need special syntax for such constructs but I'm not sure it is worth it. If anyone has an idea, just shoot. Alternatively, apart from using a native query, what about this DQL: ``` select a from A a where a.id not in (select c.id from C c where c.d = 2) ```
Author
Owner

@doctrinebot commented on GitHub (Apr 1, 2010):

Comment created by @beberlei:

I am sorry, but isn't this sort of query a violation of object oriented programming, Accessing a Superclass A and checking for a property that exists on a certain child is not possible in any strict OO language without specific casting into the type beforehand.

@doctrinebot commented on GitHub (Apr 1, 2010): Comment created by @beberlei: I am sorry, but isn't this sort of query a violation of object oriented programming, Accessing a Superclass A and checking for a property that exists on a certain child is not possible in any strict OO language without specific casting into the type beforehand.
Author
Owner

@doctrinebot commented on GitHub (Apr 1, 2010):

Comment created by romanb:

Thats why I said "we would need special syntax for such constructs", i.e. casting syntax :)

However, the alternative query I showed should work just fine for such purposes.

@doctrinebot commented on GitHub (Apr 1, 2010): Comment created by romanb: Thats why I said "we would need special syntax for such constructs", i.e. casting syntax :) However, the alternative query I showed should work just fine for such purposes.
Author
Owner

@doctrinebot commented on GitHub (Jul 18, 2011):

Comment created by holtkamp:

+1 for this request.

When displaying data (stored in a database) to end-users using generated tables, server-side sorting and filtering on properties that belong to subclasses is a common task, for instance to provide the data presented in a JQuery Datatable: http://www.datatables.net.

For this purpose, DQL is great to generate the required queries dynamically. However, since properties of subclasses can currently not be 'accessed', sorting and filtering on these properties becomes impossible, or at least cumbersome to implement. Currently I have lifted/copied specific properties to a general 'value' property of the superclass, which CAN be used for ordering and filtering. Of course, this work-around significantly decreases the power of inheritance strategy in the originally envisioned model.

In Hibernate this seems to have been solved in HQL using downcasting, which seems a viable approach, but as Benjamin already mentioned not a best practice in a strict OO approach. Therefore a specific syntax might be required to hint on the to-be-expected casts:

For additional motivation of this subject, check a recent post of mine at the user groups: http://groups.google.com/group/doctrine-user/msg/caebf8139e06e01a

@doctrinebot commented on GitHub (Jul 18, 2011): Comment created by holtkamp: +1 for this request. When displaying data (stored in a database) to end-users using generated tables, server-side sorting and filtering on properties that belong to subclasses is a common task, for instance to provide the data presented in a JQuery Datatable: http://www.datatables.net. For this purpose, DQL is great to generate the required queries dynamically. However, since properties of subclasses can currently not be 'accessed', sorting and filtering on these properties becomes impossible, or at least cumbersome to implement. Currently I have lifted/copied specific properties to a general 'value' property of the superclass, which CAN be used for ordering and filtering. Of course, this work-around significantly decreases the power of inheritance strategy in the originally envisioned model. In Hibernate this seems to have been solved in HQL using downcasting, which seems a viable approach, but as Benjamin already mentioned not a best practice in a strict OO approach. Therefore a specific syntax might be required to hint on the to-be-expected casts: - http://www.mail-archive.com/hibernate-devel@lists.sourceforge.net/msg03975.html - https://hibernate.onjira.com/browse/HB-792 - https://hibernate.onjira.com/browse/HB-132 For additional motivation of this subject, check a recent post of mine at the user groups: http://groups.google.com/group/doctrine-user/msg/caebf8139e06e01a
Author
Owner

@doctrinebot commented on GitHub (Jul 18, 2011):

Comment created by ablock:

Honestly I gave up on all of this a long time ago, and now use a CQRS approach. I make views, or denormalized tables for every view in my application, and just use straight SQL.

When I need to do creating or updating or whatever, I use doctrine.

@doctrinebot commented on GitHub (Jul 18, 2011): Comment created by ablock: Honestly I gave up on all of this a long time ago, and now use a CQRS approach. I make views, or denormalized tables for every view in my application, and just use straight SQL. When I need to do creating or updating or whatever, I use doctrine.
Author
Owner

@doctrinebot commented on GitHub (Jul 19, 2011):

Comment created by darkangel:

I would like this as well (though my use case involves joining to the subclass).

Registration has one Opponent (superclass to Player and TeamSelection). I would like to count the number of registrations for a particular team within a specific tournament. For example:

SELECT COUNT(*) FROM Registration r INNER JOIN r.opponent CAST TeamSelection ts INNER JOIN ts.team t WHERE r.tournament = 1 AND t.name = "Team #1"

@doctrinebot commented on GitHub (Jul 19, 2011): Comment created by darkangel: I would like this as well (though my use case involves joining to the subclass). Registration has one Opponent (superclass to Player and TeamSelection). I would like to count the number of registrations for a particular team within a specific tournament. For example: `SELECT COUNT(*) FROM Registration r INNER JOIN r.opponent CAST TeamSelection ts INNER JOIN ts.team t WHERE r.tournament = 1 AND t.name = "Team #1"`
Author
Owner

@doctrinebot commented on GitHub (Jul 19, 2011):

Comment created by darkangel:

Thinking about this a bit more, it can actually improve query performance, as it would no longer be necessary to join the other subclasses. In other words, since you're casting the opponent to a TeamSelection, it's not necessary to join to the players table.

A cast should probably also restrict the result using the discriminator column, for example (SQL):

INNER JOIN opponents o ON o.id = r.opponent_d AND o.type = "TeamSelection"

@doctrinebot commented on GitHub (Jul 19, 2011): Comment created by darkangel: Thinking about this a bit more, it can actually improve query performance, as it would no longer be necessary to join the other subclasses. In other words, since you're casting the opponent to a TeamSelection, it's not necessary to join to the players table. A cast should probably also restrict the result using the discriminator column, for example (SQL): `INNER JOIN opponents o ON o.id = r.opponent_d AND o.type = "TeamSelection"`
Author
Owner

@doctrinebot commented on GitHub (Sep 15, 2011):

Comment created by @guilhermeblanco:

Possible solution:

SELECT p FROM CAST(Person AS User) p

Or:

SELECT a, p FROM Article a JOIN CAST(a.person AS User) p
@doctrinebot commented on GitHub (Sep 15, 2011): Comment created by @guilhermeblanco: Possible solution: ``` SELECT p FROM CAST(Person AS User) p ``` Or: ``` SELECT a, p FROM Article a JOIN CAST(a.person AS User) p ```
Author
Owner

@doctrinebot commented on GitHub (Sep 16, 2011):

Comment created by darkangel:

If you're casting a Person (superclass) to a User (subclass), then shouldn't the alias be "u" and not "p"?

i.e. SELECT a, u FROM Article a JOIN CAST(a.person AS User) u

@doctrinebot commented on GitHub (Sep 16, 2011): Comment created by darkangel: If you're casting a Person (superclass) to a User (subclass), then shouldn't the alias be "u" and not "p"? i.e. SELECT a, u FROM Article a JOIN CAST(a.person AS User) u
Author
Owner

@doctrinebot commented on GitHub (Sep 16, 2011):

Comment created by @beberlei:

the alias is that, an alias, you can use whatever you want.

@doctrinebot commented on GitHub (Sep 16, 2011): Comment created by @beberlei: the alias is that, an alias, you can use whatever you want.
Author
Owner

@doctrinebot commented on GitHub (Sep 16, 2011):

Comment created by darkangel:

Yes I know that, I'm just making sure that I understand the syntax.

@doctrinebot commented on GitHub (Sep 16, 2011): Comment created by darkangel: Yes I know that, I'm just making sure that I understand the syntax.
Author
Owner

@doctrinebot commented on GitHub (Feb 18, 2012):

Comment created by jakajancar:

I just ran into a similar problem and am not sure the above cast solution would do.

I'm trying to do:

SELECT
    s
FROM
    Superclass s
WHERE
    s INSTANCE OF SubclassA OR
    s INSTANCE OF SubclassB AND s.foo = 'bar';

So I'd need to use CAST in a WHERE condition, not in the FROM/JOIN clause, e.g:

SELECT
    s
FROM
    Superclass s
WHERE
    s INSTANCE OF SubclassA OR
    s INSTANCE OF SubclassB AND (CAST s AS SubclassB).foo = 'bar';
@doctrinebot commented on GitHub (Feb 18, 2012): Comment created by jakajancar: I just ran into a similar problem and am not sure the above cast solution would do. I'm trying to do: ``` SELECT s FROM Superclass s WHERE s INSTANCE OF SubclassA OR s INSTANCE OF SubclassB AND s.foo = 'bar'; ``` So I'd need to use CAST in a WHERE condition, not in the FROM/JOIN clause, e.g: ``` SELECT s FROM Superclass s WHERE s INSTANCE OF SubclassA OR s INSTANCE OF SubclassB AND (CAST s AS SubclassB).foo = 'bar'; ```
Author
Owner

@doctrinebot commented on GitHub (Apr 3, 2012):

Comment created by dimirc:

Is there any workaround? Would be perfect if I could just:

SELECT a, p FROM Article a JOIN CAST(a.person AS User) p

(+1 for Guilherme Blanco possible solution)

@doctrinebot commented on GitHub (Apr 3, 2012): Comment created by dimirc: Is there any workaround? Would be perfect if I could just: `SELECT a, p FROM Article a JOIN CAST(a.person AS User) p` (+1 for Guilherme Blanco possible solution)
Author
Owner

@doctrinebot commented on GitHub (Apr 4, 2012):

Comment created by dimirc:

I was trying a workaround:

Before executing the dql query, I change the mapping info of the association to the concrete type that I was going to use. Like this:

        $cmf = $this->em->getMetadataFactory();
        $class = $cmf->getMetadataFor("Article");
        $class->associationMappings["person"]["targetEntity"]="User";

But now my problem is that I'm actually using an inverse side (in query where this is needed), so when I change the targetEntity, the sql join that is generated, is using the table of the concrete class (User in this case) and not the base class (Person)

class Person {
...
    /****
     * @OneToOne(targetEntity="Order",cascade={"persist"})
     * @JoinColumn(name="order*id", referencedColumnName="orden*id")
     */
    private $order;
...
}

class Order {
...
    /****
     * @OneToOne(targetEntity="Person", mappedBy="order")
     */
    private $person;
...
}

The SQL generated is joining as if the "user" table has orden_id field :(

@doctrinebot commented on GitHub (Apr 4, 2012): Comment created by dimirc: I was trying a workaround: Before executing the dql query, I change the mapping info of the association to the concrete type that I was going to use. Like this: ``` $cmf = $this->em->getMetadataFactory(); $class = $cmf->getMetadataFor("Article"); $class->associationMappings["person"]["targetEntity"]="User"; ``` But now my problem is that I'm actually using an inverse side (in query where this is needed), so when I change the targetEntity, the sql join that is generated, is using the table of the concrete class (User in this case) and not the base class (Person) ``` class Person { ... /**** * @OneToOne(targetEntity="Order",cascade={"persist"}) * @JoinColumn(name="order*id", referencedColumnName="orden*id") */ private $order; ... } class Order { ... /**** * @OneToOne(targetEntity="Person", mappedBy="order") */ private $person; ... } ``` The SQL generated is joining as if the "user" table has orden_id field :(
Author
Owner

@doctrinebot commented on GitHub (Feb 24, 2013):

Comment created by @ocramius:

I am closing this one.

The requirement of this issue is basically violating OO principles.

If you really need to filter across multiple child-entities in your inheritance, then try something as following instead:

SELECT
    r
FROM
    Root r
WHERE
    r.id IN (
        SELECT
            c.id
        FROM
            Child c
        WHERE
            c.field = :value
    )

Up-casting is not really a viable solution anyway, since it would be even more weird to cast in DQL and then have hydration still retrieve the root entity type.

@doctrinebot commented on GitHub (Feb 24, 2013): Comment created by @ocramius: I am closing this one. The requirement of this issue is basically violating OO principles. If you really need to filter across multiple child-entities in your inheritance, then try something as following instead: ``` SELECT r FROM Root r WHERE r.id IN ( SELECT c.id FROM Child c WHERE c.field = :value ) ``` Up-casting is not really a viable solution anyway, since it would be even more weird to cast in DQL and then have hydration still retrieve the root entity type.
Author
Owner

@doctrinebot commented on GitHub (Feb 24, 2013):

Issue was closed with resolution "Won't Fix"

@doctrinebot commented on GitHub (Feb 24, 2013): Issue was closed with resolution "Won't Fix"
Author
Owner

@doctrinebot commented on GitHub (Jul 17, 2014):

Comment created by nick4fake:

@Marco You are wrong. There is no way to do this. It is just not wise to deny a feature just because it violates OO principles when this is the only method.

There are usecases, when we have nothing to do. For example, we are searching through the whole table in STI. We use quick filters like "Like" so there is not need in difficult indexes/additional tables for them. We CAN NOT in ANY way overcome this limitation when we need to use fields of different classes.

This is certainly not a bug, when we may say "WONT FIX" and just ignore all those people who face it.

@doctrinebot commented on GitHub (Jul 17, 2014): Comment created by nick4fake: @Marco You are wrong. There is no way to do this. It is just not wise to deny a feature just because it violates OO principles when this is the only method. There are usecases, when we have nothing to do. For example, we are searching through the whole table in STI. We use quick filters like "Like" so there is not need in difficult indexes/additional tables for them. We CAN NOT in ANY way overcome this limitation when we need to use fields of different classes. This is certainly not a bug, when we may say "WONT FIX" and just ignore all those people who face it.
Author
Owner

@doctrinebot commented on GitHub (Jul 17, 2014):

Comment created by @ocramius:

{quote}It is just not wise to deny a feature just because it violates OO principles when this is the only method.{quote}

We are working on an ORM (Object-Relational-Mapper), therefore Object-Orientation is first-class citizen and prioritized over everything else in the ORM.

{quote}There are usecases, when we have nothing to do.{quote}

There are use-cases for everything, but that doesn't mean that they should clutter the tool. This does not only apply to Doctrine ORM. An edge case functionality that has multiple workarounds and that cannot be implemented correctly within the tool does NOT need to land in the tool.

I provided a workaround that respects the OO and ORM rules and works also with the OO paradigm. Here is a pseudo-code description of what the DQL I pasted above does:

{quote}

  • select all objects that are instance of Child by $someCriteria
  • select all objects that are instance of Root
  • iterate over Root instances and filter out those that are not in the results of the first selection
    {quote}

{quote}We CAN NOT in ANY way overcome this limitation when we need to use fields of different classes.{quote}

Yes you can: there's SQL. SQL is a "structured query language" that is weakly typed and allows this sort of operation natively. This is a problem of the SQL domain, and it should be solved with SQL whenever the DQL becomes too complex/limited. Don't try to stuff a feature that is only possible in the SQL domain into DQL just because they look similar.
They are not the same thing.

{quote}This is certainly not a bug, when we may say "WONT FIX" and just ignore all those people who face it.{quote}

We are not ignoring the problem: we just point you to the right scope/tools to solve it. DQL has one way of solving it, which may or may not be viable to you. SQL has another way of solving this same problem, and it's not going to be implemented in DQL because DQL is statically typed, and upcasts and type juggling are not going to land in DQL.

@doctrinebot commented on GitHub (Jul 17, 2014): Comment created by @ocramius: {quote}It is just not wise to deny a feature just because it violates OO principles when this is the only method.{quote} We are working on an ORM (Object-Relational-Mapper), therefore Object-Orientation is first-class citizen and prioritized over everything else in the ORM. {quote}There are usecases, when we have nothing to do.{quote} There are use-cases for everything, but that doesn't mean that they should clutter the tool. This does not only apply to Doctrine ORM. An edge case functionality that has multiple workarounds and that cannot be implemented correctly within the tool does NOT need to land in the tool. I provided a workaround that respects the OO and ORM rules and works also with the OO paradigm. Here is a pseudo-code description of what the DQL I pasted above does: {quote} - select all objects that are instance of `Child` by `$someCriteria` - select all objects that are instance of `Root` - iterate over `Root` instances and filter out those that are not in the results of the first selection {quote} {quote}We CAN NOT in ANY way overcome this limitation when we need to use fields of different classes.{quote} Yes you can: there's SQL. SQL is a "structured query language" that is weakly typed and allows this sort of operation natively. This is a problem of the SQL domain, and it should be solved with SQL whenever the DQL becomes too complex/limited. Don't try to stuff a feature that is only possible in the SQL domain into DQL just because they look similar. They are not the same thing. {quote}This is certainly not a bug, when we may say "WONT FIX" and just ignore all those people who face it.{quote} We are not ignoring the problem: we just point you to the right scope/tools to solve it. DQL has one way of solving it, which may or may not be viable to you. SQL has another way of solving this same problem, and it's not going to be implemented in DQL because DQL is statically typed, and upcasts and type juggling are not going to land in DQL.
Author
Owner

@doctrinebot commented on GitHub (Jul 17, 2014):

Comment created by nick4fake:

If we provide a workaround, is there any way to merge it to master branch? What we can do to vote up this feature?

@doctrinebot commented on GitHub (Jul 17, 2014): Comment created by nick4fake: If we provide a workaround, is there any way to merge it to master branch? What we can do to vote up this feature?
Author
Owner

@doctrinebot commented on GitHub (Jul 17, 2014):

Comment created by @ocramius:

[~nick4fake] we already tried looking into this multiple times.

The only "clean-ish" solution would be to support upcasting somehow, and the effort and complexity derived from it is not worth it.

@doctrinebot commented on GitHub (Jul 17, 2014): Comment created by @ocramius: [~nick4fake] we already tried looking into this multiple times. The only "clean-ish" solution would be to support upcasting somehow, and the effort and complexity derived from it is not worth it.
Author
Owner

@doctrinebot commented on GitHub (Jul 17, 2014):

Comment created by nick4fake:

@ocramius
What about "CAST()" construction? You were talking about OO, but there are some languages, that could argue with you.

For example, Delphi. It supports casting manually to selected type (for example TObject -> TButton). Does it break best violates OO principles too? No, of course.
Another example is Java. It has exactly same language construction for exactly same purpose. Is doctrine more concentrated on OO principles than Java or Delphi?

@doctrinebot commented on GitHub (Jul 17, 2014): Comment created by nick4fake: @ocramius What about "CAST()" construction? You were talking about OO, but there are some languages, that could argue with you. For example, Delphi. It supports casting manually to selected type (for example TObject -> TButton). Does it break best violates OO principles too? No, of course. Another example is Java. It has exactly same language construction for exactly same purpose. Is doctrine more concentrated on OO principles than Java or Delphi?
Author
Owner

@doctrinebot commented on GitHub (Jul 22, 2014):

Comment created by @ocramius:

[nick4fake] I think [guilhermeblanco] is thinking of providing a cast syntax.

@doctrinebot commented on GitHub (Jul 22, 2014): Comment created by @ocramius: [<sub>nick4fake] I think [</sub>guilhermeblanco] is thinking of providing a cast syntax.
Author
Owner

@doctrinebot commented on GitHub (Jul 7, 2015):

Comment created by agwidarsito:

"For example, Delphi. It supports casting manually to selected type (for example TObject -> TButton). Does it break best violates OO principles too? No, of course.
Another example is Java. It has exactly same language construction for exactly same purpose. Is doctrine more concentrated on OO principles than Java or Delphi?"

I agree. I fail to see how a type safe CAST() violates OO. We're not talking about dangerously casting anything, at least not in theory. It's polymorphism.

@doctrinebot commented on GitHub (Jul 7, 2015): Comment created by agwidarsito: "For example, Delphi. It supports casting manually to selected type (for example TObject -> TButton). Does it break best violates OO principles too? No, of course. Another example is Java. It has exactly same language construction for exactly same purpose. Is doctrine more concentrated on OO principles than Java or Delphi?" I agree. I fail to see how a type safe CAST() violates OO. We're not talking about dangerously casting anything, at least not in theory. It's polymorphism.
Author
Owner

@doctrinebot commented on GitHub (Jul 22, 2015):

Comment created by nick4fake:

Is there any chance that this would be implemented?

@doctrinebot commented on GitHub (Jul 22, 2015): Comment created by nick4fake: Is there any chance that this would be implemented?
Author
Owner

@doctrinebot commented on GitHub (Jul 22, 2015):

Comment created by @ocramius:

Unlikely right now.

@doctrinebot commented on GitHub (Jul 22, 2015): Comment created by @ocramius: Unlikely right now.
Author
Owner

@doctrinebot commented on GitHub (Jul 22, 2015):

Comment created by nick4fake:

Is it worth it to try to implement such behavior? I am not well aware of Doctrine internals thus I can not chose what to do. Would it require rewriting of doctrine kernel and major changes, or it could be implemented somehow without much patching?

@doctrinebot commented on GitHub (Jul 22, 2015): Comment created by nick4fake: Is it worth it to try to implement such behavior? I am not well aware of Doctrine internals thus I can not chose what to do. Would it require rewriting of doctrine kernel and major changes, or it could be implemented somehow without much patching?
Author
Owner

@doctrinebot commented on GitHub (Jul 22, 2015):

Comment created by @ocramius:

Implementing it is not the problem. The problem is that it violates ORM invariants that we want to keep.

@doctrinebot commented on GitHub (Jul 22, 2015): Comment created by @ocramius: Implementing it is not the problem. The problem is that it violates ORM invariants that we want to keep.
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#21