HQL join on collection elements

Asked by At

I'm trying to create HQL which will connect my Products with Prices if the Price is present for given Product and Provider.

// The main entity
@Entity public class Product {
    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String name;

    @ManyToMany
    private Set<Provider> providers;
}

// Contained in Product as Set<Provider>
@Entity public class Provider {
    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String name;

    private String address;
}

// Each provider can have its own Price for given Product
@Entity public class Price {
    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @OneToOne
    private Product product;

    @OneToOne
    private Provider provider;

    private BigDecimal amount;
}

I would like to write a HQL that will return some SearchResultDTO containing Products with Providers and Prices.

To get this, I need to join Prices on a Set of Providers which is inside a Product and I don't know how to.

Native SQL that does the job:

// The SQL
SELECT prod.name, prov.name, pr.amount
FROM product prod
LEFT OUTER JOIN products_providers pp ON pp.product_id = prod.id
LEFT OUTER JOIN provider prov ON prov.id = pp.providers_id
LEFT OUTER JOIN price pric ON (pric.product_id = prod.id AND pric.provider_id = prov.id);

I am also open to architectural hints because after introducing Prices to the model I'm not so sure I did the right thing embedding Providers inside Product.

0 Answers