Where-ing in discriminated tables

Question

How can I select all items from one specific author ? Its possible this way ? Or how can I edit entities if I want many item types and item packages (item has many items) too ?

Item

/**
 * @ORM\Table()
 * @ORM\Entity
 * @ORM\InheritanceType("JOINED")
 * @ORM\DiscriminatorColumn(name="discr", type="string")
 * @ORM\DiscriminatorMap({
 * "cd"   = "ItemCD",
 * "dvd"   = "ItemDVD",
 * "pack" = "ItemPack",
 * })
 */
class Item
{

    /**
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @ORM\Column(name="name", type="string", length=250, nullable=false)
     */
    private $name;

}

ItemCD

/**
 * @ORM\Table()
 * @ORM\Entity
 */
class ItemCD extends Item
{

    /**
     * @ORM\ManyToOne(targetEntity="Author", inversedBy="item")
     * @ORM\JoinColumn(name="author_id", referencedColumnName="id")
     */
    private $author;

}

ItemDVD

/**
 * @ORM\Table()
 * @ORM\Entity
 */
class ItemDVD extends Item
{

    /**
     * @ORM\ManyToOne(targetEntity="Author", inversedBy="item")
     * @ORM\JoinColumn(name="author_id", referencedColumnName="id")
     */
    private $author;

}

ItemPack

/**
 * @ORM\Table()
 * @ORM\Entity
 */
class ItemPack extends Item
{

    /**
     * @ORM\ManyToMany(targetEntity="Item", inversedBy="item")
     * @ORM\JoinTable()
     */
    private $items;

}

Author

/**
 * @ORM\Table()
 * @ORM\Entity
 */
class Author
{

    /**
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     *
     */
    private $id;

    /**
     * @ORM\Column(name="name", type="string", length=250, nullable=false)
     */
    private $name;

}

Bottoms

Answer 1

You will have to query for specific elements. This is a known (and wanted) limitation, since DQL is a static typed language: see http://www.doctrine-project.org/jira/browse/DDC-16

Related: how to access fields in inherited table in doctrine2 / dql query

A way of handling this with a workaround is using 2 subqueries in your DQL:

SELECT
    i
FROM
    Item i
WHERE
    i.id IN(
        SELECT 
            i2.id
        FROM
            ItemDvd i2
        WHERE
            i2.author = :author
    )
    OR
    i.id IN(
        SELECT
            i3.id
        FROM
            ItemCd i3
        WHERE
            i3.author = :author
    )

As you can see you have to extract the identifiers for each possible subtype manually.

Edit: to get all the packs from a given author (along with single DVDs or CDs), the query becomes even worse:

SELECT
    i
FROM
    Item i
WHERE
    i.id IN(
        SELECT 
            i2.id
        FROM
            ItemDvd i2
        WHERE
            i2.author = :author
    )
    OR
    i.id IN(
        SELECT
            i3.id
        FROM
            ItemCd i3
        WHERE
            i3.author = :author
    )
    OR
    i.id IN(
        SELECT
            i4.id
        FROM
            ItemPack i4
        JOIN
            i4.items i5
        WHERE
            i5.id IN (
                SELECT
                    i6.id
                FROM
                    Item i6
                WHERE
                    i6.id IN(
                        SELECT 
                            i7.id
                        FROM
                            ItemDvd i7
                        WHERE
                            i7.author = :author
                    )
                    OR
                    i6.id IN(
                        SELECT
                            i8.id
                        FROM
                            ItemCd i8
                        WHERE
                            i8.author = :author
                    )
            )
    )

Hofstetter

Answer 2

Make $author in Item and have ItemPacks $author value always be null. Then you can do:

$em->findBy("Item", array("author" => $author));

And you always get instances of ItemDVD or ItemCD.

Mckean

You cannot search by "author", since "author" is not a property of "Item". – Hofstetter

Ah, right :\ Though that basically assigns (from a logic perspective) an author to the pack. That's again weird from an OOP perspective – Hofstetter

But it is the simplest solution. – Katz

It would still require to use subqueries, plus you introduced a problem that is gonna follow you at OOP API level – Hofstetter

Well, I obviously did not include packs that contain specified author. Well, your solution is better in this way, but I would still choose this approach. – Katz

Answer 3

It's tricky and lengthy the answer. I think the Entities aproach is ok, and by querying the item entity you would get what you want. Now for forms you'll probably need one FormType per sub-item and then user the aproach for Form Collections (http://symfony.com/doc/2.1/cookbook/form/form_collections.html) and I'm certain that you will need to hook into the pre-bind event to prepare the data.

This is a quick thought, may be it can help you.

Vanden

Ok, I think you are missing the on to many relation between Author and Item in the main Entity. What I mean is that probably you should put the join in ITEM and not in every sub-item – Vanden

If ItemPack is an Item, the pack "belongs" to an author but the items inside the pack belongs to many authors. Is that case ok for you? if not, may be ItemPack should not be part of Item – Vanden