CODEWITHSUNDEEP
mysqlinheritancedoctrine-ormdoctrineassociations
Jon L.
Jon L.

Reputation: 2343

Computed entity, in Doctrine2?

In Doctrine2, is there a way to create a computed entity, that is based on the results of a complex sql query?

Background:

In an application that I'm working on (existed prior to my joining the project), there is a concept of: attribute (belongs to attribute, has many attribute, overrides attribute) catalogItem (belongs to catalogItem, has many catalogItem, has many attribute)

The idea is that catalogItem has it's own hierarchy, that currently goes up to 4 levels deep. Each catalogItem inherits the attributes of it's parent, but those attributes (which have their own hierarchy as well) can be overridden at any point. This overridden hierarchy is then used by the immediate catalogItem, and it's children as well, with the same ability existing to create overrides.

As a result, the typical structure of direct associations is fairly useless here, since we're always needing to see the computed end-result.

So, my issue is, I would like to be able to inject these computed results into Doctrine2, which can then be used as standard entities, which ability to traverse relations, etc, as normal.

This can't be done as a MySQL view, as performance drops. Additionally, the query that creates the computed result is fairly complex, and for performance, any filtering (say, by X catalogItem) occurs at both a 3rd level nested subquery, as well as the top-level query, and the very existence of subqueries means a normal MySQL view is directly incompatible (though there are workarounds).

Question:

I would like to be able to create an entity in Doctrine2, for which instead of a "table" existing, the actual MySQL query would exist, and would be run as a subquery. I haven't yet seen any way in Doctrine2 to achieve something like what I'm describing, I'm hoping someone can present a solution or workaround to achieve a similar result?

Upvotes: 1

Views: 227

Answers (1)

Ocramius
Ocramius

Reputation: 25431

No, there is no way to handle this kind of logic with Doctrine ORM. The ORM thinks in statically typed entities that are associated and have an unique identifier (from the definition of entity).

Also, the values and the type of those entities should not change dynamically, since the ORM can't handle type casting.

What you can do is:

  1. defining a mysql-level view
  2. creating an entity that is marked as @Entity(readOnly=true), which will be ignored by the ORM on flushes

This way, the ORM will be able to read data from your entity using the normal persisters, and you will be able to use the identifiers of your object in associations.

If your entity type is computed, consider emulating a single table inheritance with a discriminator column in your view.

Upvotes: 1

Related Questions