区分表中的位置

2022-01-16 00:00:00 php doctrine doctrine-orm dql

如何选择一位特定作者的所有项目?这种方式可能吗?或者，如果我想要许多项目类型和项目包(项目有很多项目)，我该如何编辑实体?

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 ?

物品

/**
 * @ORMTable()
 * @ORMEntity
 * @ORMInheritanceType("JOINED")
 * @ORMDiscriminatorColumn(name="discr", type="string")
 * @ORMDiscriminatorMap({
 * "cd"   = "ItemCD",
 * "dvd"   = "ItemDVD",
 * "pack" = "ItemPack",
 * })
 */
class Item
{

    /**
     * @ORMColumn(name="id", type="integer", nullable=false)
     * @ORMId
     * @ORMGeneratedValue(strategy="IDENTITY")
     */
    private $id;

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

}

物品CD

/**
 * @ORMTable()
 * @ORMEntity
 */
class ItemCD extends Item
{

    /**
     * @ORMManyToOne(targetEntity="Author", inversedBy="item")
     * @ORMJoinColumn(name="author_id", referencedColumnName="id")
     */
    private $author;

}

物品DVD

/**
 * @ORMTable()
 * @ORMEntity
 */
class ItemDVD extends Item
{

    /**
     * @ORMManyToOne(targetEntity="Author", inversedBy="item")
     * @ORMJoinColumn(name="author_id", referencedColumnName="id")
     */
    private $author;

}

物品包

/**
 * @ORMTable()
 * @ORMEntity
 */
class ItemPack extends Item
{

    /**
     * @ORMManyToMany(targetEntity="Item", inversedBy="item")
     * @ORMJoinTable()
     */
    private $items;

}

作者

/**
 * @ORMTable()
 * @ORMEntity
 */
class Author
{

    /**
     * @ORMColumn(name="id", type="integer", nullable=false)
     * @ORMId
     * @ORMGeneratedValue(strategy="IDENTITY")
     *
     */
    private $id;

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

}

推荐答案

您必须查询特定元素.这是一个已知的(并且想要的)限制，因为 DQL 是一种静态类型语言:请参阅 http://www.doctrine-project.org/jira/browse/DDC-16

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

相关:如何访问在教义2/dql 查询继承表

一种解决方法是在 DQL 中使用 2 个子查询:

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.

要从给定作者那里获取所有包(以及单个 DVD 或 CD)，查询会变得更糟:

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

相关文章