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
10 5372
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.
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.
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.
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
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.
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 StateFieldPathExpression.
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
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.
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.
Ok, thanks anyway for all the help.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: 3f |
last post by:
Hello;
We have made a web application that people can download from our web site
and installed on:
Windows XP
Windows 2000 Professional
Windows 2003 Server
Windows 2000 Server
|
by: manish |
last post by:
Hi,
I am a fresher in the programming field i.e although I have done
programming at the basic level but at professional level I am very new
and I am facing many problems. These probllems are...
|
by: jp29 |
last post by:
My take on problems composing, serving and rendering XHTML
documents/web pages:
1. Typical conscientious web authors are producing XHTML documents (Web
pages) that feature valid Markup and with...
|
by: Corky |
last post by:
This works:
db2 SELECT DISTINCT PROBLEM_OBJECTS.PROBLEM_ID FROM PROBLEM_OBJECTS
INNER JOIN PROBLEMS ON PROBLEM_OBJECTS.PROBLEM_ID =
PROBLEMS.PROBLEM_ID WHERE INTEGER(DAYS(CURRENT DATE) -...
|
by: Ellen Graves |
last post by:
I am having a lot of problems with DB2 8.3.1 on RH Linux AS2.1.
Installing and running stored procedures is problematic. Stored
procedures I have used for years on V7 on WinNT are now failing...
|
by: Jim |
last post by:
I have spent the past few weeks designing a database for my company.
The problem is I have started running into what I believe are stack
overflow problems. There are two tab controls on the form...
|
by: jamesbeswick |
last post by:
I've been using Access since version 97 and I've migrated to 2003. I've
noticed a substantial number of strange ActiveX/OLE and code corruption
problems when writing databases. The only solution...
|
by: Dales |
last post by:
I have a custom control that builds what we refer to as "Formlets"
around some content in a page. These are basically content "wrapper"
sections that are tables that have a colored header and...
|
by: Sergistm |
last post by:
Hello World, :D
I have a problem that it is making me crazy, I hope you can help me.
I'm trying to execute a .exe file with the Procces.Start, and there
is no problem when the file is on my...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |