Using in one table a pair of instance_id, instance_type in Doctrine

Question

PHP

Hello,

I ran into a problem in doctrine.

Imagine there are three main tables - participant, team, region.
Another one, let's call it log, which stores exactly the same data in structure - rank, points, position, and so on for each object from the first tables.

It is necessary to make it possible to link two tables by instance_id and instance_type - log and one of the main ones, depending on instance_type.

At first glance, everything is quite simple, but after describing it through the usual Doctrine_Query::create() and leftJoin, or through Doctrine_RawQuery::create(), an error is generated that “hydration” cannot be carried out, because the Log object has no connection to instance.

If anyone has come across this problem, please provide a solution.

Answer 1

I use Doctrine ODM + MongoDB :)
If this option is not suitable, consider creating a common primary key (maybe some other fields will be common) for instances, that is, instead of instance_id and instance_type, inctance_id is stored in the log, but it is common for two tables and the connection occurs either directly through it (then you need to ensure the uniqueness of id in both tables, a simple auto-increment will not work), or create another table with the fields id (auto-increment, through which the connection goes with log), instance1_id, instance2_id + other common fields. In general, the pattern "inheritance with tables for each class" (if not confused).
Somehow I tried to solve a similar problem, but my number of types was unlimited and SQL alone could not solve it

Answer 2

Why not add a connection, as Doctrine wants?

participant hasMany log,
team hasMany log,
region hasMany log,
connection by field (instance_id -> id)
And in the query, add a condition for instance_type and the target model.