How to add a count number to an array of data : Doctrine

Question

In fact, after returning a result of data from the database using Doctrine, I'm trying to add the row count number, without calling another query request.

This is my function:

public function search(QueryBuilder $qb, string $search)
    {
        $qb = $qb->addSelect('COUNT(n) as count');

        $search = $this->escape($search);

        $qb->andWhere(
            $qb->expr()->like('n.title', $qb->expr()->literal('%'.$search.'%'))
        );

      $qb->setMaxResults(2);
}

This is my DQL:

SELECT n, COUNT(n) as count FROM CoreBundle\Entity\News n LEFT JOIN n.category c WHERE n.title LIKE '%re%'

And I need to return as a result a all my data with a count key that refer to the number of rows.

The problem that I'm getting only the first row with id = 1, and it seems that the count number is correct.

So the result should by something like that: ['count' => 2 , [Newsn1,Newsn2]

Don't tell me to use array_count because I need to get the count of rows in the database, and I have a setMaxResults function, so I will not get a real number of rows.

I don't get it what you really need.. Do you need a query to return a set of results based on a wildcard (%) ? And then count the number of rows in that result set? — Dan Costinel
– Dan Costinel, Commented Apr 30, 2018 at 20:42
Have you tried the query in raw SQL just to be sure is returning more than one result? — ReynierPM
– ReynierPM, Commented Apr 30, 2018 at 20:47
It's returning one result. I think It's because the sql behavor, when we use count so basically if i can get the result with the count in SQL we can get It with doctrine. — famas23
– famas23, Commented Apr 30, 2018 at 20:52
why not not do the COUNT() and do $results = ($qb->getResults())->count() instead? (or store the result in a var before running ->count() on it so you have both the result and the count? — rkeet
– rkeet, Commented Apr 30, 2018 at 21:00

Dan Costinel · Accepted Answer · 2018-04-30 23:39:09Z

I don't know the configuration of your table, I just can imagine. So, here's my try:

For getting counts for all titles in your table:

# SQL
SELECT COUNT(id) AS count, GROUP_CONCAT(title SEPARATOR ', ') AS titles FROM newses GROUP BY title

# DQL. assuming you are using a Repository method:
$qb = $this->createQueryBuilder('n');
$qb
    ->select("COUNT(n.id) AS count, GROUP_CONCAT(n.title SEPARATOR ', ') AS titles")
    ->leftJoin('n.category', 'c')
    ->groupBy('n.title')
;

return $qb->getQuery()->getArrayResult();

For getting counts for a particular title:

# SQL
SELECT COUNT(id) AS count, GROUP_CONCAT(title SEPARATOR ', ') AS titles FROM newses WHERE n.title LIKE '%news%' GROUP BY title

# NewsRepository.php
public function getTitlesCount($title) 
{
    $qb = $this->createQueryBuilder('n');
    $qb
        ->select("COUNT(n.id) AS count, GROUP_CONCAT(n.title SEPARATOR ', ') AS titles")
        ->leftJoin('n.category', 'c')
        ->where('n.title LIKE :title')
        ->setParameter('title', "%{$title}%")
        ->groupBy('n.title')
    ;

    return $qb->getQuery()->getArrayResult();
}

Collectives™ on Stack Overflow

How to add a count number to an array of data : Doctrine

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related