473,326 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

GROUB BY problems-(Using ORM Doctrine-DQL)

6
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:
->
Expand|Select|Wrap|Line Numbers
  1. createQuery('SELECT e, count(e.id) 
  2. FROM Registro\RegistroBundle\Entity\Evaluacion e 
  3.    JOIN e.estudiante s 
  4.    JOIN e.clase c 
  5.    JOIN e.tipo_evaluacion t 
  6. WHERE e.tipo_evaluacion = 1 AND 
  7.    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
Sep 17 '12 #1
10 5372
Rabbit
12,516 Expert Mod 8TB
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.
Sep 17 '12 #2
Xory
6
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.
Sep 19 '12 #3
Rabbit
12,516 Expert Mod 8TB
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.
Sep 19 '12 #4
Xory
6
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT estudiante_id, clase_id, AVG(evaluacion)
  2. FROM public.evaluacion
  3. GROUP BY estudiante_id, clase_id
Sep 19 '12 #5
Rabbit
12,516 Expert Mod 8TB
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.
Sep 19 '12 #6
Xory
6
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT e.estudiante, 
  2.    e.clase, 
  3.    AVG(e.evaluacion) 
  4. FROM Evaluacion e 
  5. WHERE e.tipo_evaluacion = 1 
  6. GROUP BY e.estudiante, 
  7.    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.
Sep 19 '12 #7
Rabbit
12,516 Expert Mod 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT s, c, count(e.id)  
  2. FROM Registro\RegistroBundle\Entity\Evaluacion e  
  3.    JOIN e.estudiante s  
  4.    JOIN e.clase c  
  5.    JOIN e.tipo_evaluacion t  
  6. WHERE e.tipo_evaluacion = 1 AND  
  7.    s.grupo = 1 GROUP BY s.id, c.id
Sep 19 '12 #8
Xory
6
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.
Sep 25 '12 #9
Rabbit
12,516 Expert Mod 8TB
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.
Sep 25 '12 #10
Xory
6
Ok, thanks anyway for all the help.
Sep 25 '12 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

1
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
1
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...
32
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...
5
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) -...
2
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...
19
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...
26
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...
19
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...
0
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.