Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-16

DQL Ignores properties of subclasses

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Won't Fix
    • Affects Version/s: 2.0-ALPHA2
    • Fix Version/s: None
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None

      Description

      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.

        Issue Links

          Activity

          Hide
          Roman S. Borschel added a comment -

          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)
          
          Show
          Roman S. Borschel added a comment - 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)
          Hide
          Benjamin Eberlei added a comment -

          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.

          Show
          Benjamin Eberlei added a comment - 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.
          Hide
          Roman S. Borschel added a comment -

          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.

          Show
          Roman S. Borschel added a comment - 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.
          Hide
          Menno Holtkamp added a comment -

          +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

          Show
          Menno Holtkamp added a comment - +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
          Hide
          Avi Block added a comment -

          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.

          Show
          Avi Block added a comment - 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.
          Hide
          Glen Ainscow added a comment - - edited

          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"
          Show
          Glen Ainscow added a comment - - edited 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"
          Hide
          Glen Ainscow added a comment -

          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"
          Show
          Glen Ainscow added a comment - 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"
          Hide
          Guilherme Blanco added a comment -

          Possible solution:

          SELECT p FROM CAST(Person AS User) p
          

          Or:

          SELECT a, p FROM Article a JOIN CAST(a.person AS User) p
          
          Show
          Guilherme Blanco added a comment - Possible solution: SELECT p FROM CAST(Person AS User) p Or: SELECT a, p FROM Article a JOIN CAST(a.person AS User) p
          Hide
          Glen Ainscow added a comment -

          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

          Show
          Glen Ainscow added a comment - 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
          Hide
          Benjamin Eberlei added a comment -

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

          Show
          Benjamin Eberlei added a comment - the alias is that, an alias, you can use whatever you want.
          Hide
          Glen Ainscow added a comment -

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

          Show
          Glen Ainscow added a comment - Yes I know that, I'm just making sure that I understand the syntax.
          Hide
          Jaka Jancar added a comment -

          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';
          
          Show
          Jaka Jancar added a comment - 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';
          Hide
          Wladimir Coka added a comment -

          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)

          Show
          Wladimir Coka added a comment - 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)
          Hide
          Wladimir Coka added a comment -

          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

          Show
          Wladimir Coka added a comment - 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
          Hide
          Marco Pivetta added a comment -

          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.

          Show
          Marco Pivetta added a comment - 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.

            People

            • Assignee:
              Marco Pivetta
              Reporter:
              Avi Block
            • Votes:
              6 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: