JPQL select by attribute in list

Question

I'm using Spring data (jpa-repository) and I have the following entities:

public class City{
    ....
    private Street street;
    ....
}

public class Street{
    ...
    private List<Building> buildings;
    ...
}

public class Building{
    ... 
    private List<Flat> flats;
    ....
}

public class Flat{
   ...
   private boolean lightsOn;
   ...
}

I want to create a query to get all (distinct) cities that has at least one flat with powered on lights.

I tried this query:

@Query("select distinct c from Cities c where  c.street.buildings.flats.lightsOn = true")

but got this error message:

The state field path 'c.street.buildings.flats.lightsOn' cannot be resolved to a valid type.

How can I do that?

I'm not familiar with JPQL. Is it the fact that buildings (and flats) are `List`s — Michael, Jan 26 '17 at 16:39
@Michael - I'm sure it is - I just don't know what's the correct query (I'm sure there is a way) — Noam, Jan 26 '17 at 16:41
@Gimby - Thanks for noticing the typo (changed my question) - still getting this error — Noam, Jan 26 '17 at 16:43
_select distinct c from City c JOIN c.street s JOIN s.buildings b JOIN b.flats f where f.lightsOn = true_. But then looking at ANY JPQL tutorial would show you how to JOIN! — Neil Stockton, Jan 26 '17 at 18:23

score -1 · Answer 1 · answered Jan 26 '17 at 16:59

-1

this is the sql code :

select * from city as c where c.idcity in 
    (select s.idcity from street as s where c.idcity = s.idcity and s.idstreet in
        (select b.idstreet from building as b where b.idstreet = s.idstreet and b.idbuilding in
            (select f.idbuilding from flat as f where f.idbuilding = b.idbuilding and f.lightsOn = 1)
        )
    )

if you find difficulty to convert it to JPQL Just tell me :) .

answered Jan 26 '17 at 16:59

Mohamed Nabli

1,629
3
17
24

Can you please convert it to JPQL...? and can it be done using join? what's better? – Noam Jan 26 '17 at 17:08

JPQL select by attribute in list

1 Answers1