• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to select c.field as alias in JPA

 
Syed Saifuddin
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Everybody

how to write query in JPA-QL.

My query is

"select c.nameenglish as student, count(m.contact) as messagecount
from Message m , Contact c where m.contact=c.id " group by contact
order by messagecount desc";

but I am getting error in parsing [as].

Any solution or idea to solve this problem is appreciated
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you need the "as" clause at all, the query is going to return an Object[], so there is no naming indexes in an array.

A JPA Query is not the same syntax as SQL.

Mark
 
Syed Saifuddin
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi Mark Spritzler

sir but in case of aggregate function like count(field) how to get this function value.
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Syed Saifuddin:
hi Mark Spritzler

sir but in case of aggregate function like count(field) how to get this function value.


Again it is an Object[][] that is being returned, the value of the count will be one of the indexes in the object array. Objects don't have names.

So in your query, the count is the second item, so when you get a row from the Object[][] it will be the second item it the array.

so for each Object[x][y], the y will equal "1"

Now you can do another trick where you create a new object of your special type

[CODE]
public class MyObject
{

private String student;
private int count;

public MyObject(String student, int count) {
this.student = student;
this.count = count;
}

//Getters and Setters here
}
[\CODE]

then your JPA QL could be

"select new MyObject(c.nameenglish, count(m.contact))
from Message m , Contact c where m.contact=c.id " group by m.contact";

So now you will get a Collection of MyObject, and now to get the values you would be able to call myObjectVar.getCount();

Then sort it later too.

Mark
 
Syed Saifuddin
Ranch Hand
Posts: 130
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi Mark Spritzler

Thank You Mark Spritzler this really solve the big part of my problem.
but 1 more thing is left

in start of query I say count(contact) as smscount
and in last I write order by smscount desc.

If I dont put alias to the function how can I use order by
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Syed Saifuddin:
hi Mark Spritzler

Thank You Mark Spritzler this really solve the big part of my problem.
but 1 more thing is left

in start of query I say count(contact) as smscount
and in last I write order by smscount desc.

If I dont put alias to the function how can I use order by


Exactly.

You just need to remember that JPA-QL is not SQL.

Try the alias again in the count and what happens. If you keep getting an error then you know you can't use that alias in the query and might just have to put the count() function call in the order by. Try them both out and see what happens.


Mark
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic