[FIXED] Selecting through multiple mapped tables with HQL?

Issue

I'm having a hard time selecting a Brand from a given Country with HQL. I've done some reverse engineering so you can picture how my database is modeled:

To select the Brand from the right Country I'd have to go through Address as well as their City and State.

In terms of annotated mappings I've done this:

Brand

@Table(name = "brand")
public class Brand extends BasicModel {

    private String name;
    @OneToMany(targetEntity = Address.class, cascade = CascadeType.ALL,
                fetch = FetchType.LAZY, orphanRemoval = true)
    @JoinTable(name="brand_address",
            joinColumns = { @JoinColumn(name = "brand_id") },
            inverseJoinColumns = { @JoinColumn(name = "address_id") })
    private Set<Address> address;

Address

@Table(name = "address")
public class Address extends BasicModel {

    @NotNull
    @ManyToOne
    private AddressCity city;

AddressCity is mapped to AddressState which is mapped to AddressCountry in the same fashion that AddressCity is mapped by above Address.

The table brand_address is created automatically by Hibernate.

Now with SQL I'd need to join all these tables to get to the Country but since it's been mapped with Hibernate annotations, how should I write the HQL to select the Brand from the given Country?

INFO: When I create a Country containing a set of Address, the table brand_address is beign correcly populated.

What worked so far is just selecting the Brand and Hibernate will give me back all the populated objects. I can do it with the following:

    Query query = session
            .createQuery("from Brand brand " +
                            "where brand.name = :brandName " +
                                "and brand.deleted is null");
    query.setParameter("brandName", brandName);

With this, I could easily filter out the Brands with that name but from other Countries, but it doesn't smell like the best practice...

What HAVEN'T worked so far when I tried:

    Query query = session
            .createQuery("from Brand brand " +
                            "where brand.name = :brandName " +
                                "and Address.city.state.country.code = :countryCode " +
                                "and brand.deleted is null");
    query.setParameter("brandName", brandName);
    query.setParameter("countryCode", countryCode);

AND

    Query query = session
            .createQuery("from Brand brand " +
                            "where brand.name = :brandName " +
                                "and AddressCountry.code = :countryCode " +
                                "and brand.deleted is null");
    query.setParameter("brandName", brandName);
    query.setParameter("countryCode", countryCode);

How shoudl I work it out? Is there a way for me to get to the country without writing multiple joins in the above queries? Should I just select it all with the way it has already worked and filter the results back in my DAO?

I've been searching the internet for this but only found information on how to map and insert the information, not really about how to write a query that will select through the tables.

I thank in advance any help and suggestions that you beautiful people can send in my way!

UPDATE 1: I've tried adding

left join brand.address as a
with a.city.state.country.code = :countryCode

And it resulted in a new error:

java.sql.SQLSyntaxErrorException: Unknown column 'addresscit3_.state_id' in 'on clause'

With the following log:

14:02:43.646 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [brand brand0_]
14:02:43.646 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [inner join address_state addresssta4_ on addresscit3_.state_id=addresssta4_.id]
14:02:43.646 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [inner join address_country addresscou5_ on addresssta4_.country_id=addresscou5_.id]
14:02:43.647 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [inner join brand_address addresses1_ on brand0_.id=addresses1_.brand_id inner join address address2_ on addresses1_.address_id=address2_.id and (addresscou5_.code=?)]
14:02:43.647 [main] DEBUG org.hibernate.hql.internal.ast.util.JoinProcessor - Using FROM fragment [inner join address_city addresscit3_ on address2_.city_id=addresscit3_.id]
14:02:43.647 [main] DEBUG org.hibernate.hql.internal.antlr.HqlSqlBaseWalker - select >> end [level=1, statement=select]

UPDATE 2: SOLUTION

Using the with key as suggested by @guillaume did change the error in such a way that the join order seemed misplaced... A friend of mine suggested using join fetch because of the lazy loading and I tweked the query until everything fell in place as follows:

    .createQuery("from Brand brand " +
                    "join fetch brand.addresses as a " +
                        "where brand.name = :brandName " +
                        "and a.city.state.country.code = :countryCode " +
                        "and brand.deleted is null");

Solution

The condition Address.city.state.country.code = :countryCode does not make sense because Address is actually a Set of addresses (it would probably be clearer to name it addresses).

Try this instead:

from Brand brand
left join brand.address as a
with a.city.state.country.code = :countryCode

Answered By - Guillaume