My doctrine query returns error with distrinct

Question

I am trying to return my visits but I need a distinct and not a count. I tried these repository methods but only count works.

Working:

public function numberOfVisitsBetweenDates($dateMin, $dateMax)
    {
        $builder = $this->createQueryBuilder('visit');
        $builder->select('count(DISTINCT visit.user)');
        $builder->where('visit.date BETWEEN :dateMin AND :dateMax');
        $builder->setParameter('dateMin', $dateMin);
        $builder->setParameter('dateMax', $dateMax);
        $query = $builder->getQuery();
        // dd($query);
    
            return $query->getResult();
    }

My dump dql -_dql: "SELECT count(DISTINCT visit.user) FROM App\Entity\Visit visit WHERE visit.date BETWEEN :dateMin AND :dateMax"

---

Not working:

    public function numberOfVisitsBetweenDates($dateMin, $dateMax)
        {
            $builder = $this->createQueryBuilder('visit');
            $builder->select('visit.user');
            $builder->where('visit.date BETWEEN :dateMin AND :dateMax');
            $builder->setParameter('dateMin', $dateMin);
            $builder->setParameter('dateMax', $dateMax);
            $builder->distinct();
    
            $query = $builder->getQuery();
            // dd($query);
        
                return $query->getResult();
        }

My dump dql: -_dql: "SELECT DISTINCT visit.user FROM App\Entity\Visit visit WHERE visit.date BETWEEN :dateMin AND :dateMax" My error:

QueryException QueryException HTTP 500 Internal Server Error [Semantical Error] line 0, col 22 near 'user FROM App\Entity\Visit': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

Both codes are almost the same, i can't find the solution

Answer 1

You should try something like this:

    public function numberOfVisitsBetweenDates($dateMin, $dateMax)
        {
            $builder = $this->createQueryBuilder('visit');
            $builder->where('visit.date BETWEEN :dateMin AND :dateMax');
            $builder->setParameter('dateMin', $dateMin);
            $builder->setParameter('dateMax', $dateMax);
            $builder->groupBy('visit.user');
    
            $query = $builder->getQuery();
            return $query->getResult();
        }