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;

}

score 7 · Accepted Answer · edited May 23 '17 at 10:29

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
                    )
            )
    )

Zdeněk Topič · Answer 2 · 2013-02-13T13:04:18.073

1

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.

edited Feb 13 '13 at 13:04

answered Feb 13 '13 at 12:48

Zdeněk Topič

762
4
29

You cannot search by "author", since "author" is not a property of "Item". – Ocramius Feb 13 '13 at 13:13
Ah, right :\ Though that basically assigns (from a logic perspective) an author to the pack. That's again weird from an OOP perspective – Ocramius Feb 13 '13 at 13:27
But it is the simplest solution. – Zdeněk Topič Feb 13 '13 at 13:30
It would still require to use subqueries, plus you introduced a problem that is gonna follow you at OOP API level – Ocramius Feb 13 '13 at 13:32
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. – Zdeněk Topič Feb 13 '13 at 15:35

score 0 · Answer 3 · answered Feb 13 '13 at 11:41

0

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.

answered Feb 13 '13 at 11:41

egulias

1
1

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 – egulias Feb 13 '13 at 11:54
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 – egulias Feb 13 '13 at 12:22

Where-ing in discriminated tables

3 Answers3

Linked