title: Constraint the number of rows returned by JOIN FETCH in JPA
body: <p>I'm having a Product object with a list of related products (which are also product objects). The field of related products is annotated like this:</p>
  
  <pre><code> public class Product {
  
      @JoinTable(name = "RELATED_PRODUCT", joinColumns = {
         @JoinColumn(name = "PRODUCT_ID", referencedColumnName = "id", nullable = false)}, inverseJoinColumns = {
         @JoinColumn(name = "RELATED_PRODUCT_ID", referencedColumnName = "id", nullable = false)})
      @ManyToMany(fetch = FetchType.LAZY)
      List&lt;Product&gt; relatedProducts;
   }
  </code></pre>
  
  <p>As you can see the list is fetched lazy, which is what I want in most cases. In some cases however, I want the list of related products to be filled immediatly. I created a query for this with a LEFT JOIN FETCH. However, I want only the related products to be added that have a certain rating, let's say a rating of > 3.</p>
  
  <p>I tried the following:</p>
  
  <pre><code> SELECT DISTINCT p FROM Product p LEFT JOIN FETCH p.comparableProducts cp WHERE p.id = :id AND cp.rating &gt; 3 AND CURRENT_DATE BETWEEN p.commenceDate AND p.removeDate
  </code></pre>
  
  <p>But this doesn't work. It always returns back ALL related products in the database, not just the ones that have a rating above 3. How is this fixable?</p>