學說EAVorderBy和搜索

我正在使用 Symfony 5 和 Doctrine 2.10 My Book 物體沒有直接屬性，所有欄位都是通過物體-屬性-值模式創建的，如下所示：

書：

#[ORM\OneToMany(mappedBy: 'book', targetEntity: BookAttributeValue::class)]
private Collection $bookAttributeValues;

圖書屬性值：

#[ORM\ManyToOne(targetEntity: Book::class, inversedBy: 'bookAttributeValues')]
#[ORM\JoinColumn(nullable: false)]
private Book $book;

#[ORM\ManyToOne(targetEntity: Attribute::class)]
#[ORM\JoinColumn(nullable: false)]
private BookAttribute $bookAttribute;

#[ORM\Column(type: 'string')]
private string $value;

書本屬性

#[ORM\Column(type: 'string', length: 50)]
private string $name;

#[ORM\Column(type: 'string', length: 50)]
private string $handle; // Like: NAME, DESCRIPTION, AUTHOR etc.

要選擇我在 BookRepositry 中列出的書籍：

$qb = $this->createQueryBuilder('book');

        $qb
            ->addSelect([
                'bookAttributeValues',
                'bookAttribute',
            ])
            ->leftJoin('book.attributeValues', 'bookAttributeValues')
            ->leftJoin('bookAttributeValues.bookAttribute', 'bookAttribute')
            ->andWhere(
                $qb->expr()->in('bookAttribute.handle', [
                    BookAttribute::NAME,
                    BookAttribute::DESCRIPTION,
                ]),
            );

然后我可以像這樣訪問我的屬性：

$book = $books[0];
foreach($book->attributeValues as $value) {
   echo $value->attribute->name . ' : ' . $value->value . '<br>';
}

問題是，如何在 SQL 獲取狀態上按名稱排序？或如何按名稱搜索？Doctrine QB 會是什么樣子？

uj5u.com熱心網友回復：

DQL 關系限制

由于 EAV 使用無模式設計模式，因此沒有使用 DQL (QueryBuilder) 通過 ORM 完成過濾或排序的直接方法，因為Book::$attributeValues資料庫中的結果值 ( ) 是不明確的：

[
     ['value' => 'name_value'], 
     ['value' => 'description_value']
]

簡而言之，ORM 不打算用于這種型別的“報告”。

DQL 解決方法

上面提到的關系問題的一種解決方法 ( Book::$attributeValues) 是通過手動映射查詢構建器，以隔離NAME屬性和關聯的值，然后可以將其用于過濾 ( =, IN(), LIKE) 或排序。

排序NAME屬性值

使用AS HIDDEN添加化名任意連接列，可用于進行排序。

$qb = $this->createQueryBuilder('book');
$expr = $qb->expr();

$qbS = $this->_em->createQueryBuilder()
    ->select('na.id')
    ->from(BookAttribute::class, 'na')
    ->where($expr->eq('na.handle', ':attribute_name'));

$qb->addSelect([
        'bookAttributeValues',
        'bookAttribute',
        'nav.value AS HIDDEN name_value',
    ])
    ->leftJoin('book.attributeValues', 'bookAttributeValues')
    ->leftJoin('bookAttributeValues.bookAttribute', 'bookAttribute')

    //isolate the associated name attribute value as a separate column
    ->leftJoin(BookAttributeValue::class, 'nav', 'WITH', $expr->andX(
         $expr->eq('book.id', 'IDENTITY(nav.book)'),
         $expr->in('IDENTITY(nav.attribute)', $qbS->getQuery()->getDQL())
    ))
    ->andWhere($expr->in('bookAttribute.handle', ':attributes'))
    ->setParameter('attribute_name', BookAttribute::NAME)
    ->setParameter('attributes', [BookAttribute::NAME, BookAttribute::DESCRIPTION])
    ->addOrderBy('name_value')
    ->addOrderBy('a.name', 'ASC'); //Z-A (Name, Description)

按NAME屬性值過濾結果

只需將標準添加到您的陳述中。

$qb->andWhere($expr->eq('nav.value', ':attribute_value'))
    ->setParameter('attribute_value', '<desired_name_value>');

SQL 查詢替代方案

由于限制，我建議將 DQL 轉換為 SQL 查詢，并對屬性及其關聯值使用單獨的嵌套JOIN陳述句。創建關系的資料透視表。然后您可以按別名name連接列值排序。

名稱屬性相關值

SELECT nav.value AS name
#...
LEFT JOIN (book_attribute_value AS nav
INNER JOIN book_attribute AS na 
ON na.id = nav.attribute_id
AND na.handle = BookAttribute::NAME)
ON book.id = nav.book_id 

描述 屬性相關值

SELECT dav.value AS description
#...
LEFT JOIN (book_attribute_value AS dav 
INNER JOIN book_attribute AS da
ON da.id = dav.attribute_id
AND da.handle = BookAttribute::DESCRIPTION)
ON book.id = dav.book_id 

完整示例 DB-Fiddle

嵌套連接將導致關聯書籍的缺失描述或名稱屬性值回傳NULL為該列，而不是排除整行。

class BookRepository
{

    /*
     * @return array|string[][]
     */
    public function filterBooks()
    {
        $sql = <<<SQL
SELECT 
    book.*,
    nav.value AS name,
    dav.value AS description
FROM book
LEFT JOIN (book_attribute_value AS nav
INNER JOIN book_attribute AS na 
ON na.id = nav.attribute_id
AND na.handle = :attr_name)
ON book.id = nav.book_id 
LEFT JOIN (book_attribute_value AS dav 
INNER JOIN book_attribute AS da
ON da.id = dav.attribute_id
AND da.handle = :attr_descr)
ON book.id = dav.book_id 
ORDER BY name
SQL;

        $stmt = $this->_em->getConnection()->prepare($sql);
        $stmt->bindValue('attr_name', BookAttribute::NAME);
        $stmt->bindValue('attr_descr', BookAttribute::DESCRIPTION);

        return $stmt->executeQuery()->fetchAllAssociative();
    }
}

結果

[
   {"id": "1", "name": "Book_1_Name", "description": "Book_1_Description"},
   {"id": "2", "name": "Book_2_Name", "description": "Book_2_Description"}
]

To iterate over the results as desired.

$books = $em->getRepository(Book::class)->filterBooks();
foreach ($books as $book) {
    //ksort($book, SORT_NATURAL); #optionally sort by the attribute column
    //printf('Book %s:<br>', $book['id']); #display the book id
    unset($book['id']); //remove the id column
    foreach ($book as $attribute => $value) {
        printf('%s: %s<br>', $attribute, $value);
    }
}

Output

name: Book_1_Name
description: Book_1_Description
name: Book_2_Name
description: Book_2_Description

To limit the results for a specified name value, change the LEFT JOIN nav to an INNER JOIN nav and add the desired criteria (=, IN(), LIKE) to the ON clause of the statement.

Example Query DB-Fiddle

SELECT 
    book.*,
    nav.value AS name,
    dav.value AS description
FROM book
INNER JOIN (book_attribute_value AS nav
INNER JOIN book_attribute AS na 
ON na.id = nav.attribute_id
AND na.handle = :attr_name)
ON book.id = nav.book_id 
AND nav.value = :name_value
LEFT JOIN (book_attribute_value AS dav 
INNER JOIN book_attribute AS da
ON da.id = dav.attribute_id
AND da.handle = :attr_descr)
ON book.id = dav.book_id 

Be sure to bind the value to the statement criteria.

$stmt->bindValue('name_value', 'Book_1_Name');

標籤：php symfony 教义形式

上一篇：如何根據Symfony作業流的陣列位置過濾物件？

下一篇：更新部分頁面