QueryDSL JPA- Self join without relationship with group by

Question

Is there any way to get this query with QueryDSL?

select
  person.name,
  count(neighbors.*)
from person as neighbors
where person.address = neighbor.address
group by person.name

where address is not a FK.

Answer 1

The key to the first problem is to use aliases to be able to make the self-join:

// static instance uses default alias "person"
QPerson person = QPerson.person;
QPerson neighbor = new QPerson("neighbor");

JPAQuery query = new JPAQuery(em)
    .from(person, neighbor)
    .where(person.adress.eq(neighbor.adress))
    .groupBy(person.name);

To get the results you can simply use the Tuple class:

List<Tuple> results = query.list(person.name, neighbor.count());
for (Tuple row : result) {
    System.out.println("name: " + row.get(person.name));
    System.out.println("count: " + row.get(neighbor.count()));
}

Or you can use a ConstructorExpression to do something like this:

List<MyResult> results = query.list(ConstructorExpression.create(
        MyResult.class, person.name, neighbor.count()));

public class MyResult {
    private String name;
    private long count;
    public MyResult(String name, long count) {
        this.name = name;
        this.count = count;
    }
}