Composable Ecto query, joins and where bindings: can't get how to do it

Questions / Help
1

Hi!

Stright to the line :slight_smile: :

I would like to parse incoming search request in a number subsequential parsing passes like:

  defp gen_query(sq) do
    from(d in SomeModel, order_by: d.date, distinct: true)
    |> gen_query(sq)
  end

  defp gen_query(dbq, %{ "in_annotations" => true, "str" => str} = sq) do
    dbq
    |> or_where([q], fragment("lower(?) like lower(?)", q.annotation, ^"%#{str}%"))
    |> gen_query(stripe_sq(sq))
  end

  defp gen_query(dbq, %{ "in_names" => true, "str" => str} = sq) do
    dbq
    |> or_where([q], fragment("lower(?) like lower(?)", q.name, ^"%#{str}%"))
    |> gen_query(stripe_sq(sq))
  end

I’m going to leave behind the scope stripe_sq function because it’s pretty trivial and just stripe used keys from map.

And everything is great till we use only one model. But I need to use multiple models with joins (via the first and only first model - SomeModel in my example). And the problem is that I need not only join another table but also use it fields with where or or_where. I don’t actually select it or something - it just for searching proposes.

So the next bodies of the gen_query will be like

  defp gen_query(dbq, %{ "in_customers" => true, "str" => str} = sq) do
    dbq
    |> join(:left, [q], c in assoc(c, :customer))
    |> or_where([q, c], fragment("lower(?) like lower(?)", c.full_name, ^"%#{str}%"))
    |> gen_query(stripe_sq(sq))
  end
  defp gen_query(dbq, %{ "in_contractors" => true, "str" => str} = sq) do
    dbq
    |> join(:left, [q], c in assoc(c, :contractor))
    |> or_where([q, c], fragment("lower(?) like lower(?)", c.full_name, ^"%#{str}%"))
    |> gen_query(stripe_sq(sq))
  end

This scheme also works pretty well while I have only one join ( [q, c] binding ). But there could be a lot of joins and it’s impossible to know how many in advance. The last join appears in the binding at the last position, but I don’t know how to get the last one without listing it all (which is impossible because I don’t know how many joins were there before.

So the question is: is it possible to bind models for where clause by name or take the last element of the binding list?

Thanks a lot!

2

Ups, sorry, read Ecto.Query docs inattentively. It’s possible to bind like [p, …, c]. It solves my problem without any doubts.

Thanks a lot for reading in any way :wink:

1 Like
3

Ecto 2.1 (it is a RC) allows you to match on the last join:

    dbq
    |> join(:left, [q], c in assoc(c, :contractor))
    |> or_where([q, ..., c], fragment("lower(?) like lower(?)", c.full_name, ^"%#{str}%"))
    |> gen_query(stripe_sq(sq))

But cases like this is precisely why I prefer to use the keyword expression syntax:

query = 
  from q in dbq,
    join: c in assoc(q, :contractor),
    or_where: fragment("lower(?) like lower(?)", c.full_name, ^"%#{str}%")
gen_query(query, strip_sq(sq))

Sure, it is not pipeable but it is much more readable than the pipe one.

5 Likes
4

José,

Thanks a lot, my tired brain didn’t even think about keyword expression syntax, of course this is much better!