title: Ecto join with dynamically built conditions
body: <p>I'm trying to build an Ecto query with a left join with optional extra conditions on the join. I'll try to describe it with the typical posts and comments example.</p>
  
  <p>Post has_many Comments
  Comment belongs_to Post.</p>
  
  <p>Let's say Comment has two boolean fields, approved and featured.</p>
  
  <p>I want to get all Posts regardless of whether they have comments or not, hence the left join.
  I want comments preloaded but preferably one SQL query.
  I want to optionally filter comments on approved and featured.</p>
  
  <p>I'm trying to write a function something like this where if approved or featured is not nil, they will be included in the join, if they are nil, they will be ignored.  I haven't found a better way than something like this:</p>
  
  <pre><code>def posts_with_comments(approved, featured, some_var) do
    query = Post
    |&gt; where([p], p.some_field == ^some_var
  
    cond do
      !is_nil(approved) and !is_nil(featured)
        -&gt; join(query, :left, [p], c in Comment, [post_id: p.id, approved: ^approved, featured: ^featured])
  
      !is_nil(approved)
        -&gt; join(query, :left, [p], c in Comment, [post_id: p.id, approved: ^approved])
  
      !is_nil(featured)
        -&gt; join(query, :left, [p], c in Comment, [post_id: p.id, featured: ^featured])
  
      true -&gt; join(query, :left, [p], c in Comment, [post_id: p.id])
    end
  
    |&gt; preload([p, c], [comments: c])
    |&gt; select([p], p)
    |&gt; Repo.all
  
  end
  </code></pre>
  
  <p>That works but there must be a better way. It would get crazy if I had a third parameter.  I'm looking for a way to dynamically build that list for the <code>on</code> parameter of <code>join()</code>. My attempts to do that have failed because of the requirement to pin.</p>
  
  <p>I can't put those conditions in a <code>where</code> because if I do something like <code>where t.approved == true</code> I get only posts approved comments.</p>