CODEWITHSUNDEEP
symfonydoctrine-ormdql
NTsvetkov
NTsvetkov

Reputation: 117

doctrine dql array and object at same time

I have two entities and I want to get a list of distinct elements and their count from the first entity, and the whole second entity as objects. I'm not sure if I can explain it, because of my bad english.

So, this is the function:

public function findMostPopular()
{
    return $this->getEntityManager()
        ->createQuery("
            SELECT 
                e,
                COUNT(p.event) c,
                IDENTITY(p.event) m
            FROM AppBundle:Predictions p
            LEFT JOIN p.event e
            WHERE p.status = 'pending'
            GROUP BY p.event
            ORDER BY c DESC , m ASC
        ")
        ->setMaxResults(5)
        ->getResult()
    ;
}

When I try to call it, I'm getting this error:

[Semantical Error] line 0, col -1 near ' SELECT ': Error: Cannot select entity through identification variables without choosing at least one root entity alias.

Is it even possible this way to get what I want?

Edit: Example data in the table:

737117017
737117017
737117017
737561075
737561075
737561075
738821787
738821787
738821787
738848055
739040139

I want count of each distinct value. These are the foreign keys to the table with events, so I want the corresponding object from events table.

! c !  m        !   event                                    !
--------------------------------------------------------------
! 3 ! 737117017 !  the object from events with id 737117017  !
! 3 ! 737561075 !  the object from events with id 737561075  !
! 3 ! 738821787 !  the object from events with id 738821787  !
! 1 ! 738848055 !  the object from events with id 738848055  !
! 1 ! 739040139 !  the object from events with id 739040139  !
--------------------------------------------------------------

Edit 2

I got it work by following way. I'm sure, it's not correct, but I don't know the right way.

SELECT
    p,
    e,
    COUNT(p.event) c,
    IDENTITY(p.event) m
FROM AppBundle:Predictions p
LEFT JOIN p.event e
WHERE p.status = 'pending'
GROUP BY p.event
ORDER BY c DESC , m ASC

Upvotes: 0

Views: 406

Answers (1)

Oscar Pérez
Oscar Pérez

Reputation: 4397

According to the referred problem you must change the order of your entities in the query, so it should be something like:

public function findMostPopular()
{
  return $this->getEntityManager()
    ->createQuery("
        SELECT 
            e,
            COUNT(p.event) c,
            IDENTITY(p.event) m
        FROM AppBundle:Event e
        LEFT JOIN e.predictions p
        WHERE p.status = 'pending'
        GROUP BY p.event
        ORDER BY c DESC , m ASC
    ")
    ->setMaxResults(5)
    ->getResult()
  ;
}

Upvotes: 1

Related Questions