GROUB BY problems-(Using ORM Doctrine-DQL)

[Xory]

Hi, English is not my native language, so please excuse my redaction.
I´m using the php framework: Symfony 2, with Doctrine.

I´m doing the next query:
->

createQuery('SELECT e, count(e.id) 
FROM Registro\RegistroBundle\Entity\Evaluacion e 
   JOIN e.estudiante s 
   JOIN e.clase c 
   JOIN e.tipo_evaluacion t 
WHERE e.tipo_evaluacion = 1 AND 
   s.grupo = 1 GROUP BY s.id, c.id')

and it`s given this:
SQLSTATE[42803]: Grouping error: 7 ERROR: column "e0_.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT e0_.id AS id0, e0_.evaluacion AS evaluacion1, e0_.est...
^
evaluacion have relations manyToOne with estudiante and with clase.

Thanks in advance

[Rabbit]

Everything that's in your SELECT clause must either be in a GROUP BY or an AGGREGATE function. Your field that you call "e" is not in a GROUP BY or AGGREGATE function.

[Xory]

Well I know in SQL that's the way to do it, but here's an example from the Doctrine official documentation:
SELECT u, count(g.id) FROM Entities\User u JOIN u.groups g GROUP BY u.id
as you can see u is an object of the entity user and u is not entirely in the group by clause. I did the same I want to select the entity Evaluacion grouping by the fields estudiante and clase. I also use an aggregate function AVG to find the average of an integer field named evaluacion.

[Rabbit]

It may be the case that the program allows you to not put everything except the id in the group by, but that means if you want to select anything or everything from e, you still need to put the id of e in the group by.

If you look at the example you just posted to your SQL in your original post, you will see that your SQL is still wrong compared to the working sample that you posted.

Basically, the error you're getting is the error that occurs when you have something in the SELECT clause that's not in the GROUP BY. It's what that error means regardless of which database you're working in. Even if that particular implementation allows you to not put most of the fields in the group by, that doesn't mean you can leave out every field, and that is what the error message is telling you.

[Xory]

Yes, I know what you mean, but I can't group by the id of the entity. A student (estudiante), might have several evaluations in one class (clase), that's why I need the average of those evaluations. If I do a group by the id of the table evaluacion I won't be able to get the information I need. This is the query in native SQL that I need to do in DQL:

SELECT estudiante_id, clase_id, AVG(evaluacion)
FROM public.evaluacion
GROUP BY estudiante_id, clase_id

[Rabbit]

Then don't use every field in e in the select clause. That's the only reason why it wants you to put the id in the group by. If you don't put every field in e in the select clause, then you won't have to put the id in the group by. What I'm saying is, select only what you need.

[Xory]

Well I've tried but it's not that simple... The query I sent you works just fine in native SQL, but when I try to do it in DQL I can't use something like e.estudiante_id 'cause even when that's a field of the evaluacion table it's not an attribute of the class 'Evaluacion'. The class 'Evaluacion' has an attribute estudiante which is an object of the class 'Estudiante' and it happen the same with the attribute clase. I did something like this:

SELECT e.estudiante, 
   e.clase, 
   AVG(e.evaluacion) 
FROM Evaluacion e 
WHERE e.tipo_evaluacion = 1 
GROUP BY e.estudiante, 
   e.clase

and it's giving me this error
[Semantical Error] line 0, col 9 near 'estudiante, e.clase,': Error: Invalid PathExpression. Must be a StateFieldPathE xpression.

[Rabbit]

In the end, you're not trying to group by Evaluacion yet you keep bringing in those fields. What you actually want is to group by estudiante and clase. What I was trying to lead you to was to try this.

SELECT s, c, count(e.id)  
FROM Registro\RegistroBundle\Entity\Evaluacion e  
   JOIN e.estudiante s  
   JOIN e.clase c  
   JOIN e.tipo_evaluacion t  
WHERE e.tipo_evaluacion = 1 AND  
   s.grupo = 1 GROUP BY s.id, c.id

[Xory]

I tried that code and it's giving me this error
[Semantical Error] line 0, col -1 near 'SELECT s, c,': Error: Cannot select entity through identification variables without choosing at least one root entity alias.
I even tried to add the root entity alias, but... didn't work out.

[Rabbit]

At this point, I don't know what could be causing it. You might be able to find further help at a forum dedicated to ORM Doctrine DQL.

[Xory]

Ok, thanks anyway for all the help.