title: Join cartesian product
body: <p><strong>Context</strong></p>
  
  <p>I send some files to some enterprises every week. I need to restitute for each week and each enterprise whether the file is sent or not.</p>
  
  <p><strong>Tables</strong></p>
  
  <ul>
  <li>ent (enterprise)</li>
  <li>wek (week)</li>
  <li>fil (file : references wek and ent)</li>
  </ul>
  
  <p><strong>Solution with pure SQL</strong></p>
  
  <p>Make a cartesian product between <strong>ent</strong> and <strong>wek</strong> then left outer join  <strong>fil</strong>. This works : </p>
  
  <pre><code>select * from 
    (
    select * from wek, ent e
    ) as t1
  left join fil f 
    on f.ent_id = t1.ent_id 
    and f.wek_id = t1.wek_id
  </code></pre>
  
  <p><strong>Problem :</strong></p>
  
  <p>How to translate this into JPA (in the CriteriaBuilder way)?</p>
  
  <p>For example, if I try :</p>
  
  <pre><code>CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery&lt;ResultClass&gt; query = cb.createQuery(ResultClass.class);
  Root&lt;Week&gt; week = query.from(Week.class);
  Root&lt;Enterprise&gt; query.from(Enterprise.class);
  Expression&lt;???&gt; cartesianProduct = ??? //How?
  cartesianProduct.leftJoin(???_.file);
  query.where(
    cb.equal(file.get(File_.wek_id), week.get(Week.week_id));
  )
  </code></pre>
  
  <p>Using 2 "from" clauses gives me the cartesian product but how do I left join this result?</p>
  
  <p><strong>Unstatisfaying solution :</strong></p>
  
  <p>Create a view : </p>
  
  <pre><code>CREATE VIEW view_ent_wek AS
    SELECT ent_id as ent_id,  wek_id as wek_id, ent_id || '-' || ent_id as id
    FROM ent, wek;
  </code></pre>
  
  <p>Map it to an entity : </p>
  
  <pre><code>@Entity
  @Table(name = "view_ent_wek")
  public class WeekEnterprise {
  
    @Id
    @Column(name = "id")
    private String id;
  
    @ManyToOne
    @JoinColumn(name = "ent_id")
    private Enterprise enterprise;
  
    @ManyToOne
    @JoinColumn(name = "wek_id")
    private Week week;
  
    [...]
  </code></pre>
  
  <p>Then I can use it in a query : </p>
  
  <pre><code>Root&lt;WeekEnterprise&gt; weekEnterprise = query.from(WeekEnterprise.class);
  weekEnterprise.join(...)
  </code></pre>
  
  <p>I don't like this solution because it makes me create a view that is obviously not necessary. Any idea?</p>