Search code examples
phpmysqldoctrine-ormblobdatabase-optimization

How to save one Doctrine entity to two database tables (required for MySQL optimisation)

In my database I've a table file and a table file_content. The file table stores the metadata of the file such as name, mime and some more. The file_content stores a blob with the file content. I'm not storing the blob in the same table as the metadata for performance reasons only.

For a 'version 2' of my project I'm looking into Doctrine (2.3). To me, a "File" seems to be one entity, with properties such as name, mime, extension, content that should be used like this: 

$file = new File();
$file->setName('hello.txt');
$file->setMime('text/plain');
$file->setContent('Hello world!')
$em->persist($file);
$em->flush();

Is this behaviour possible? To me it makes no sense to create two entities for something that's really just one entity. I could not find anything about it in the documentation and I read in a 2-year-old topic that it isn't possible in Doctrine 2.1: Doctrine 2.1 - Map entity to multiple tables

Someone any suggestions how to handle this correctly? I'm new to Doctrine and have been playing around with it a bit to see if it's the right choice for my project. Thanks.

Solution

  • Do you have the ability to alter the schema of your database? If so, I'd consider consolidating this into one table.

    Barring that, you may want to try a one-to-one relationship in Doctrine. Perhaps something like:

    class File {
    private $id;
    private $name;
    private $mime;
    private $content;
}

class Content {
    private $id;
    private $data;
    private $fileId;
}

    If you map Content->fileId with a one-to-one relationship to File->id, then you can do things like: 

    $file->getContent()->getData();
$file->getContent()->setData("something different");

    Here's some more info on one-to-one mappings: http://docs.doctrine-project.org/en/2.0.x/reference/association-mapping.html#one-to-one-unidirectional