Select subset of the list contained in an entity

Question

Say I want to get all rows of the MyEntity that have an id lower than 10. This entity contains a list of Another entity. I would like this list to be fetched only by a subset of the listAnother. This subset containing only Another where the user contained in it is a specific one.

Basically in SQL it would be like this :

SELECT * FROM myentity m
LEFT JOIN another a
ON m.idTable=a.my_entity
AND a.user = "test1"
WHERE m.idTable < 10;

I didn't manage however to translate this query to jpql.

My entities being like this :

@Entity
public class MyEntity implements Serializable {    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int idTable;

    @OneToMany(mappedBy = "myEntity")
    private List<Another> listAnother;

}

@Entity
public class Another implements Serializable {    
    @Id
    private int idAnother;

    // bi-directional many-to-one association to Thethread
    @ManyToOne(fetch = FetchType.LAZY)
    private MyEntity myEntity;

    @ManyToOne(fetch = FetchType.LAZY)
    private User user;
}

@Entity
public class User implements Serializable {

    @Id
    private String username;
}

---

In jpa I could do this :

SELECT m FROM MyEntity where m.idTable < 10;

And then for each entity I get from this list call this:

SELECT a FROM Another Where a.user.username=:'test' AND a.myEntity=:entity;

However I would like to do it all at once in one query. Can I do this with criteria ? I didn't take the time to learn it but if it's possible then I will.

Answer 1

JPQL and Critaria API are equal in terms of what you can express with them. What is possible with JPQL is possible with Criteria and vice versa.

With JPQL, you can simply combine your 2 queries into one in this way:

SELECT a FROM Another a Where a.user.username=:test AND a.myEntity.idTable < 10

You can use dot notation (.) to join multiple entities in the query, provided that the relationship is X-to-one. If you have X-to-many relationship, you need to use JPQL JOIN, which is not very complicated. Example with (LEFT) JOIN:

SELECT m FROM MyEntity m LEFT JOIN m.listAnother a Where a.user.username=:test AND m.idTable < 10

The result is of course not equal - in first case you will get list of Another entities and you can get MyEntity by a.myEntity, in the second case you will get list of MyEntity, which all have at least one Another entity with given user