Search code examples
javamysqlquerydsl

QueryDSL subquery not working

I'm trying to recreate the following SQL query in QueryDSL. The following is my SQL query which is currently working as inteded.

SELECT * FROM room x WHERE unit_id = (SELECT unit_id FROM room WHERE unit_id = x.unit_id GROUP BY unit_id HAVING(SUM(sqft) > 0))

I'm trying to write a QueryDSL query that does the same thing but honestly can't come any further than the bottom query.

JPASubQuery subQuery = new JPASubQuery();
subQuery.from(qRoom).groupBy(qRoom.unit).having(qRoom.sqft.sum().goe(0));

JPAQuery unitquery = from(qRoom)
   .where(qRoom.building.eq(building)).where(qRoom.unit.eq(subQuery));

return unitquery.list(qRoom);

The above query isn't working and i'm having trouble using subqueries in QueryDSL. What should I add/change to make this query working?

Solution

  • Fixed it.

    I just needed to change .where(qRoom.unit.eq(subQuery)); too .where(qRoom.unit.in(subQuery.list(qRuimte.unit)));