I'm using Symfony 5 and Doctrine 2.10 My Book entity have no direct properties, all the fields are made via Entity-Attribute-Value pattern, like so:

Book:

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

BookAttributeValue:

#[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;

BookAttribute

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

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

To select the books for listing I'm doing in 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,
                ]),
            );

Then I can access my attributes like so:

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

The question is, how to, say, order by name on the state of SQL fetching? Or How to search by name? How the Doctrine QB would look like?

CodePudding user response：

DQL Relational Limitations

Since EAV uses a schema-less design pattern, there is not a direct approach to accomplish the filtering or sorting with the ORM using DQL (QueryBuilder), as the resulting values (Book::$attributeValues) in the database are ambiguous:

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

Simply put, the ORM is not intended to be used for this type of "reporting".

DQL Workaround

One workaround to the relational issue mentioned above (Book::$attributeValues), is by mapping the query builder manually, in order to isolate the NAME attribute and associated values, that can then be used to be filtered (=, IN(), LIKE) or sorted.

Sort NAME Attribute Value

Use AS HIDDEN to add an arbitrary aliased join column, that can be used to be sorted.

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

Filter Results by NAME Attribute Value

Simply add the criteria to your statement.

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

SQL Query Alternative

Due to the limitations, I suggest converting the DQL to an SQL query and use separate nested JOIN statements for the attributes and their associated values. Creating a pivot-table of the relationships. Then you can order by the aliased name join column value.

Name Attribute Related Values

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 

Description Attribute Related Values

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 

Full Example DB-Fiddle

The nested join will cause a missing description or name attribute value for the associated book to return as NULL in that column as opposed to excluding the entire row.

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();
    }
}

Result

[
   {"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');