Conditional dynamic joins

Questions / Help
1

I have the following query:

from o in Offer,
left_join: c in Customer, on: o.customer_id == c.id, as: :customer,
left_join: co in Company, on: c.company_id == co.id, as: :company,
where: ^maybe_search(term)

defp maybe_search(nil), do: dynamic(true)
defp maybe_search(""), do: dynamic(true)
defp maybe_search(term) do
  safe_term = strip_unsafe_chars(term)
  dynamic([company: co], fragment("? ILIKE '%' || ? || '%'", co.name, ^safe_term))
end

This works fine. However, there’s one (IMO big) problem with it, which is that the original query always performs the two joins (I think?), even in situations where the search term will be empty, which is the vast majority of the time. In my particular case, this is very wasteful.

How can the above be rewritten to eliminate this inefficiency? Ideally using the keyword syntax (i.e. not the pipe operator) because that’s what I use everywhere else in this codebase.

2

First some general thoughts: The “problem” is not big, and maybe even not a problem at all. If you have indexes in the customer_id and company_id, then for the database the query is nothing. So if you are in general fine with the code, then think again if you want to change it.

Now, having said that, I would still optimize it, because not doing joins is still better than doing them. Queries are composable, so you can do things like

def search(term) do
  query = from o in Offer
  
  maybe_search(query, term)
end

defp maybe_search(query, term) when term in [nil, ""], do: query

defp maybe_search(query, term) do
  safe_term = strip_unsafe_chars(term)
  
  from o in query,
    left_join: c in Customer, on: o.customer_id == c.id, as: :customer,
    left_join: co in Company, on: c.company_id == co.id, as: :company,
    where: dynamic([company: co], fragment("? ILIKE '%' || ? || '%'", co.name, ^safe_term))
end

I would do it with pipe-based syntax personally, but it doesn’t really matter.

3

Assuming you always want to join the offer’s associated customers, but only join the companies associated with the customers when there’s a valid search term, you could try something like this.

# Create a query
base_query =
  from o in Offer,
  left_join: c in Customer, on: o.customer_id == c.id, as: :customer

# Extend the query
maybe_add_company_search(base_query, term)

def maybe_add_company_search(query, ""), do: query
def maybe_add_company_search(query, nil), do: query
def maybe_add_company_search(query, term) do
  safe_term = strip_unsafe_chars(term)
  query_with_company_search = 
    from [o, c] in base_query,
    left_join: co in Company, on: c.company_id == co.id, as: :company,
    where: dynamic([company: co], fragment("? ILIKE '%' || ? || '%'", co.name, ^safe_term))
end

reference: Composition | Ecto.Query

Basically, it’s very similar @egze’s solution which was posted just as I was wrapping up this response, but includes the customer join in the base query since that’s how I interpreted the initial question.

1 Like