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

I am not sure what you want: You say you want the `visits` but then why are you trying to select the user in both queries (`visit.user`)? Is this a copy/paste error or do you actually want to select the user of visits? — goulashsoup, Apr 22 '21 at 07:30

score 2 · Accepted Answer · answered Feb 24 '21 at 14:51

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();
        }

Welcome to StackOverflow. Thanks for your first answer. Please add some more details WHY @Laurie97275 should try it. ;-) Thanks! — Roman, Feb 25 '21 at 07:56

My doctrine query returns error with distrinct

1 Answers1