Get all entities with a one to many relationship where one of the related entities meets criteria

Question

I need to fetch records based on constraints placed on the related records in a one-to-many relationship. For instance, I have:

@Entity
@Table (name = "LISTING")
public class Listing
{
    @Id
    @GeneratedValue (strategy = GenerationType.SEQUENCE, generator = "LISTING_SEQ")
    @SequenceGenerator (name = "LISTING_SEQ", initialValue = 1, allocationSize = 1, sequenceName = "LISTING_SEQ")
    @Column (unique = true, nullable = false, updatable = false)
    long id;

    @OneToMany (mappedBy = "listing", fetch = FetchType.EAGER)
    Set<ListingLineItem> listingLineItems;

    ...
}

And....

@Entity
@Table (name = "LISTING_LINE_ITEM")
public class ListingLineItem
{
    @EmbeddedId
    ListingLineItemPK         id;

    boolean ignored;

    @ManyToOne (fetch = FetchType.EAGER)
    @JoinColumn (name = "listing_id", nullable = false)
    Listing                   listing;

    ...
}

I need to either write a JPQL/HQL query or utilize a CriteriaBuilder to give me Listings which have a related ListingLineItem where ignored = true.

Answer 1

Here the Solution in jpql:

SELECT l FROM Listing l, ListingLineItem lli 
WHERE lli.listing.id = l.id AND lli.ignored = true