Sidus/EAVModelBundle 

How to query data

Using the EAVFinder

If you don't need to build complex queries using AND, OR and multiple conditions over the same attributes, you can use the EAVFinder helper service which behave like Doctrine's findBy() method.

<?php
/**
 * @var \Sidus\EAVModelBundle\Model\FamilyInterface $family
 * @var \Sidus\EAVModelBundle\Doctrine\EAVFinder $eavFinder
 * @var \Sidus\EAVModelBundle\Entity\DataInterface $author
*/
$results = $eavFinder->findBy($family, [
    'published' => true,
    'author' => $author,
]);

You can also use the slightly more advanced filterBy method which allows you to define your operators

<?php
/**
 * @var \Sidus\EAVModelBundle\Model\FamilyInterface $family
 * @var \Sidus\EAVModelBundle\Doctrine\EAVFinder $eavFinder
 * @var \Sidus\EAVModelBundle\Entity\DataInterface $author
*/
$results = $eavFinder->filterBy($family, [
    ['published', '=', true],
    ['title', 'like', 'My little %'],
]);

Also, this service allows you to fetch the Query Builder instead of the results so you can modify it to your needs:

<?php
/**
 * @var \Sidus\EAVModelBundle\Model\FamilyInterface $family
 * @var \Sidus\EAVModelBundle\Doctrine\EAVFinder $eavFinder
 * @var \Sidus\EAVModelBundle\Entity\DataInterface $author
*/
$qb = $eavFinder->getFilterByQb($family, [
    ['published', '=', true],
    ['title', 'like', 'My little %'],
]);

$qb // For example if you want to filter on a property that is not a EAV attribute:
    ->andWhere('e.updatedAt > :date')
    ->setParameter('date', new DateTime('yesterday'));

$results = $qb->getQuery()->getResult();

Fetching the repository

<?php
/**
 * @var \Sidus\EAVModelBundle\Model\FamilyInterface $family
 * @var \Doctrine\ORM\EntityManagerInterface $entityManager
 * @var \CleverAge\EAVManager\EAVModelBundle\Entity\DataRepository $dataRepository
 * @var integer $id
*/
$dataRepository = $entityManager->getRepository($family->getDataClass());

$dataRepository->find($id);

EAVQueryBuilder

Sometimes you want to programmatically search for entities in your database. When using a traditional relational model in Doctrine you can query your database with the QueryBuilder and the DQL language. When using an EAV model, you need to make a join on the values table each time you put a condition on the value of an attribute and the resulting queries are really complicated to write and to maintain manually.

Introducing the EAVQueryBuilder:

<?php
/**
 * @var \Sidus\EAVModelBundle\Entity\DataRepository $dataRepository
 * @var \Sidus\EAVModelBundle\Model\FamilyInterface $categoryFamily
 */

// Initializing a new EAVQueryBuilder from the Category family
$eavQb = $dataRepository->createFamilyQueryBuilder($categoryFamily);

// Creating the proper DQL and parameters to match some category codes
$dqlHandler = $eavQb->a('categoryCode')->in([
    'books',
    'comics',
]);

// Apply dql to main query builder
$qb = $eavQb->apply($dqlHandler);

// Fetching result, the traditional Doctrine's way
$categories = $qb->getQuery()->getResult();

So basically, we need the family and the data repository to create the EAV query builder instance, like a classic query builder.

We use the syntax :

$eavQb->a({{ATTRIBUTE_CODE}})->{{OPERATOR}}({{VALUE}});

To generate an DQLHandler instance that contains the DQL and the parameters that will be used later to build the doctrine query builder.

In the end, we apply the DQLHandler to the main EAVQueryBuilder instance to fetch the Doctrine query builder.

In a more complex example, you can create imbricated DQLHandlers with AND and OR conditions:

<?php
/**
 * @var \Sidus\EAVModelBundle\Entity\DataRepository $dataRepository
 * @var \Sidus\EAVModelBundle\Model\FamilyInterface $bookFamily
 * @var \Sidus\EAVModelBundle\Entity\DataInterface[] $categories
 */
$eavQb = $dataRepository->createFamilyQueryBuilder($bookFamily);

$eavQb->addOrderBy($eavQb->a('title'), 'DESC');

$qb = $eavQb->apply($eavQb->getOr([
    $eavQb->getAnd([
        $eavQb->a('publicationStatus')->in(['validated', 'published']),
        $eavQb->a('price')->lte(16),
        $eavQb->a('title')->like('%programming%'),
    ]),
    $eavQb->getAnd([
        $eavQb->a('title')->like('%example%'),
        $eavQb->a('categories')->in($categories), // Fetched earlier
        $eavQb->a('tomeNumber')->between(2, 4),
    ]),
]));

$books = $qb->getQuery()->getResult();

The end result would look like this in relational SQL:

SELECT * FROM Book b WHERE (
    (
        b.publicationStatus IN ('validated', 'published') AND
        b.price <= 16 AND
        b.title LIKE '%programming%'
    )
    OR
    (
        b.title LIKE '%example%' AND
        b.categoried IN (37, 42) AND
        b.tomeNumber BETWEEN 2 AND 4
    )
)
ORDER BY b.title DESC

Joining on EAV relations

In this example, we will use the Book -> Category relation through the attribute Book.categories.

<?php
/**
 * @var \Sidus\EAVModelBundle\Entity\DataRepository $dataRepository
 * @var \Sidus\EAVModelBundle\Model\FamilyInterface $bookFamily
 * @var \Sidus\EAVModelBundle\Model\FamilyInterface $categoryFamily
 */

// Initializing a new EAVQueryBuilder from the Book family
$eavQb = $dataRepository->createFamilyQueryBuilder($bookFamily);

// We need to fetch the Category.categoryCode attribute manually as it's not
// part of the root family
$categoryCodeAttribute = $categoryFamily->getAttribute('categoryCode');

// Apply dql to main query builder
$qb = $eavQb->apply(
    $eavQb->a('categories')->join()->attribute($categoryCodeAttribute)->in([
        'books',
        'comics',
    ])
);

// Fetching result, the traditional Doctrine's way
$categories = $qb->getQuery()->getResult();

Multiple conditions on the same attribute

You need to build the attribute query builder apart and use the clone method. This way the same join will be used but you will be able to add as many conditions as you want.

<?php
/**
 * @var \Sidus\EAVModelBundle\Entity\DataRepository $dataRepository
 * @var \Sidus\EAVModelBundle\Model\FamilyInterface $bookFamily
 */

// Initializing a new EAVQueryBuilder from the Book family
$eavQb = $dataRepository->createFamilyQueryBuilder($bookFamily);

// We need the attribute query builder to clone it
$publicationDateAttributeQb = $eavQb->a('publicationDate');

// Apply dql to main query builder
$qb = $eavQb->apply(
    $eavQb->getOr([
        $publicationDateAttributeQb->between(new DateTime('last monday'), new DateTime()),
        (clone $publicationDateAttributeQb)->isNull(),
    ])
);

// Fetching result, the traditional Doctrine's way
$categories = $qb->getQuery()->getResult();

Using an EAV attribute in custom DQL

Sometimes you need the raw column reference of a EAV attribute to use it in a custom DQL query.

<?php
/** @var $eavQb \Sidus\EAVModelBundle\Doctrine\SingleFamilyQueryBuilder */
$eavQb->a('publicationDate')->applyJoin()->getColumn();

You need to call the AttributeQueryBuilderInterface::applyJoin method if you are not using the attribute reference somewhere else inside the $eavQb->apply() method.

If you want to use the an attribute both for generic filtering purposes and for something custom you can simply keep the reference to the attribute and simply call getColumn without the need to call ```applyJoin````.

<?php
/** @var $eavQb \Sidus\EAVModelBundle\Doctrine\SingleFamilyQueryBuilder */

// We need the attribute query builder to call fetch the column
$publicationDateAttributeQb = $eavQb->a('publicationDate');

// Apply dql to main query builder
$qb = $eavQb->apply(
    $publicationDateAttributeQb->between(new DateTime('last monday'), new DateTime())
);

// Use the column for some custom purpose, here an orderBy
$qb->orderBy($publicationDateAttributeQb->getColumn(), 'DESC');

// Fetching result, the traditional Doctrine's way
$categories = $qb->getQuery()->getResult();