Orm: Entity mapped to two tables

[jardayn]

Feature Request

Having an entity that's mapped to multiple tables would allow to split entities into a table that contains the FKs and the other one with the rest of the data.

Summary

So here's an example of a query that takes a long time to run:
https://gist.github.com/jardayn/32c0e1b6ccc97ee5354bd4bde8438fcb

The obvious solution would be to partition the tables by HASH(status) or HASH(type). But because there are foreign keys in the table - partitioning is out of the question without moving them out of the table.

So you can create a job_details table, and move all the non-FKd data over there and partition it.
But then you will have to create a JobDetails entity, and link it to the JobEntity.
Then you have to modify your getters and setters to include jobDetails
Like this:

$this->jobDetails->setStatus()

Which is pretty inconvenient if you ask me. But if you could map each attribute to a specific table, that would eliminate a huge headache

[Ocramius]

This is already supported by partitioning the data in a storage engine that can handle transparent partitioning, such as PostgreSQL.

Creating userland hacks because MySQL can't have FKs in this scenario seems wrong to me.

If you really must operate with MySQL under these conditions, a joined table inheritance may already mitigate this, by having one of the two involve tables being the parent table in the inheritance.

[jardayn]

Per postgres docs:
https://www.postgresql.org/docs/10/static/ddl-partitioning.html
Since primary keys are not supported on partitioned tables, foreign keys referencing partitioned tables are not supported, nor are foreign key references from a partitioned table to some other table.

Or am I missing something?

[Ocramius]

Ah, my bad then, I thought that you wanted a reference from the partitioned
table, not towards it.

On Thu, 25 Oct 2018, 00:36 jardayn, [e-mail] wrote:

Per postgres docs:
https://www.postgresql.org/docs/10/static/ddl-partitioning.html
Since primary keys are not supported on partitioned tables, foreign keys
referencing partitioned tables are not supported, nor are foreign key
references from a partitioned table to some other table.

Or am I missing something?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/doctrine/doctrine2/issues/7442#issuecomment-432851816,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAJakGWwGYTRoEkKL8WC4Vj8HAE-kBNgks5uoOtXgaJpZM4X5I0d
.

[beberlei]

Mapping an entity to two tables is what Joined Table Inheritance essentially is, you could use that feature.