[FIXED] Symfony 5 queryBuilder count data

Issue

in one of my queries I am joining three tables to allow me to filter by taking into account these three tables.

The problem is when I do a counter of the data from the main table (CV table), like I join the language table (ManyToMany), the categories table and the diploma table (manyToMany), when I do a counter of the number cv, the latter also takes into account each time a user adds languages ​​or diplomas to his cv. (roughly if there are 20 basic cv in the table, if someone changes their cv to add three languages, the counter will go up to 23 while there are still 20 cv 😳)

I am forced to include the join to the language and diploma table to allow the cvs to be filtered by these criteria, and I am forced to make a counter by taking the data from the searchQuery so that it correctly displays the number of cvs if a nobody filters on languages ​​for example.

So how can I do to count only the CVs and not the number of languages ​​and diplomas in addition while keeping the possibility of filtering by these criteria? basically just hide the language and diploma join counter if possible?

Here is a quick code for the searchQuery :

    private function getSearchQuery(SearchData $search, $ignorePrice = false): QueryBuilder
    {
        $query = $this
            ->createQueryBuilder('p')
            ->select('c', 'p')
            ->join('p.category', 'c')
            ->leftJoin('p.langue', 'l')
            ->leftJoin('p.diplome', 'd');
        // ->select('DISTINCT IDENTITY(c.id)', 'DISTINCT IDENTITY(p.id)');

And the counter:

public function countSearch(SearchData $search)
{
    return $this->getSearchQuery($search)
        ->select('COUNT(p)')
        ->getQuery()
        ->getSingleScalarResult();
}

Solution

Solved the problem by editing my count function like this:

select count distinct p 

and it’s working.

Answered By - Sven Eberth