Symfony + doctrine + inheritance, how to make them work?
I am getting started with Symfony, I found some documentation about inheritance. But also found this discouraging article that makes me doubt Doctrine handles inheritance in any way ...
Anyone find a clever solution for inheritance in Symfony + Doctrine?
As an example, I have already structured the database something like this:
CREATE TABLE `poster` (
`poster_id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) NOT NULL,
PRIMARY KEY (`poster_id`),
UNIQUE KEY `id` (`poster_id`),
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE `user` (
`user_id` int(11) NOT NULL,
`real_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_id` (`user_id`),
CONSTRAINT `user_fk` FOREIGN KEY (`user_id`) REFERENCES `poster` (`poster_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
From this Doctrine generated this "schema.yml":
Poster:
connection: doctrine
tableName: poster
columns:
poster_id:
type: integer(4)
fixed: false
unsigned: false
primary: true
autoincrement: true
user_name:
type: string(50)
fixed: false
unsigned: false
primary: false
notnull: true
autoincrement: false
relations:
Post:
local: poster_id
foreign: poster_id
type: many
User:
local: poster_id
foreign: user_id
type: many
Version:
local: poster_id
foreign: poster_id
type: many
User:
connection: doctrine
tableName: user
columns:
user_id:
type: integer(4)
fixed: false
unsigned: false
primary: true
autoincrement: false
real_name:
type: string(50)
fixed: false
unsigned: false
primary: false
notnull: false
autoincrement: false
relations:
Poster:
local: user_id
foreign: poster_id
type: one
Creating a user for this structure using Doctrine's auto-generated forms does not work.
Any hint would be appreciated.
a source to share
After months of working on a project that uses both column aggregation and specific inheritance, I can only say one thing: avoid specific inheritance! Actually.
Let's say you have 3 tables: media and video / audio, which inherit from Media. You expect to be able to do something like:
Doctrine_Query::create()
->from('Media m')
->execute();
Well, it won't work with specific inheritance. It's just useless and has little practical use other than model inheritance methods.
On the other hand, when aggregating columns, the Media table will have an automatically added "type" column and allow you to do things like:
Doctrine_Query::create()
->from('Video v')
->execute();
Which will return a collection of video objects. But you can also do this:
Doctrine_Query::create()
->from('Media m')
->execute();
And you get a mixed result of Video and Audio objects.
You should check the doctrine documentation anyway . But beware, as inheritance and doctrine can quickly get nasty.
a source to share
The solution to decouple the Video data from the Audio data, but still use the same table to store the Media, is as follows:
Media:
columns:
name: { type: string(255), notnull: true }
description: { type: text }
Video:
inheritance:
type: column_aggregation
keyField: type
keyValue: video
Audio:
inheritance:
type: column_aggregation
keyField: type
keyValue: audio
VideoData:
columns:
resolution_x: { type: integer, notnull: true }
resolution_y: { type: integer, notnull: true }
relations:
Video: { foreignAlias: Data, onDelete: CASCADE }
AudioData:
columns:
sample_rate: { type: integer, notnull: true }
relations:
Audio: { foreignAlias: Data, onDelete: CASCADE }
Something like this ... This way you can store all your "media" in one table and retrieve them like DuoSRX mentioned above and still have no unnecessary data in that table. You should of course join it, but with indexed foreign keys, which shouldn't be a performance hit.
a source to share