Symfony / Doctrine findBy

Question

Hove to create custom Repository function who query by json field. I have params column in my database who look like this:

"params": {
    "product": "stopper",
    "itemIdentifier": ""
}

I want to query record by product value. In this case stopper term.

score 1 · Answer 1 · answered Jan 29 '20 at 00:45

If I understood your question correctly, you have a table with a column named params. And inside this mysql column, you store JSON text.

And then you want to query that table and filter by looking into the JSON in your column.

This can be a bit tedious and was also highly discouraged in the past (prior to the JSON Type in Mysql 5.7.8). Best practices would be to have a NoSQL DB such as MongoDB which is actual JSON stored in a collection(table).

Anyways, there is a solution for you.

Taking into account @AppyGG explained how to make a custom repository function.

First of all, we have to make a query using pure SQL. It can be done two ways:

1.Return arrays containing your data.

$conn = $this->getEntityManager()->getConnection();

$sql = '
    SELECT * FROM product p
    WHERE p.price > :price
    ORDER BY p.price ASC
    ';
$stmt = $conn->prepare($sql);
$stmt->execute(['price' => $price]);

// returns an array of arrays (i.e. a raw data set)
return $stmt->fetchAll();

2.Return hydrated Entities

use Doctrine\ORM\Query\ResultSetMappingBuilder;

$rsm = new ResultSetMappingBuilder($entityManager);
$rsm->addRootEntityFromClassMetadata('MyProject\Product', 'p');

$sql = '
    SELECT * FROM product p
    WHERE p.price > :price
    ORDER BY p.price ASC
    ';
 $nql = $this->_em->createNativeQuery( $sql, $rsm );
 $nql->setParameter('price', $price);

//Return loaded entities
return $nql->getResult();

Now, knowing how to make make a MySQL query with doctrine, we want to select results filtered in JSON data.

I'm am referencing this beautiful stackoverflow which explains it all: How to search JSON data in MySQL?

The easiest solution proposed in there requires at least MySQL 5.7.8

Your MySQL query would be as follow:

//With $entity->getParams() == '{"params": {"product":"stopper", "itemIdentifier":""}}'
$conn = $this->getEntityManager()->getConnection();

$sql = '
    SELECT * FROM Entity e
    WHERE JSON_EXTRACT(e.params, "$.params.product") = :product
    ';
//Or Like this if the column is of Type JSON in MySQL(Not doctrine, yes check MySQL).
$sql = '
    SELECT * FROM Entity e
    WHERE e.params->"$.params.product" = :product
    ';
$stmt = $conn->prepare($sql);
$statement->bindValue("product","stopper");
$stmt->execute();
return $statement->fetchAll();

Hope this helps!

P.S: Note that my example uses a column named 'params' with a Json containing also a named attribute 'params', this can be confusing. The intended purpose is to show how to do multiple level filtering.

score -1 · Answer 2 · answered Jan 28 '20 at 14:41

You can achieve this with a classic example :

In your repository : For one result

public function findOneProduct($value): ?Params
{
    return $this->createQueryBuilder('p')
        ->andWhere('p.product = :val')
        ->setParameter('val', $value)
        ->getQuery()
        ->getOneOrNullResult()
    ;
}

For multiple result

public function findParamsByProduct($value): ?Params
{
    return $this->createQueryBuilder('p')
        ->andWhere('p.product = :val')
        ->setParameter('val', $value)
        ->orderBy(/*some field */)
        ->setMaxResults(/*if needed*/)
        ->getQuery()
        ->getResults()
    ;
}

In your controller:

$stoppers = $entityManager->getRepository(Params::class)->findParamsByProduct('stopper');

Symfony / Doctrine findBy

2 Answers2