Writing a query - translation approach

some time ago I had posted this question on a parser which I had already written in Python/ply and I am porting to elixir / ecto / erlang, using yecc.

one point where I got stuck was which format to target from the erlang code associated to the grammar productions, and it took time to understand that the result of quote is not something I can easily reuse, also because I did not manage to understand which alternatives were being offered.

also possibly puzzling for me, was that in the iex console, a query gets printed in a format which is not a format I can use to insert the query by hand in the console.

iex(163)> from(p in "plant")
#Ecto.Query<from p0 in "plant">

the present follow-up question, more than a question, is a RFC, since it contains the help I would give to someone who made the same assertion as myself a few weeks ago. and since I’m a total beginner in Elixir, I guess some might want to comment.

should I be scared of the quoted format. I’m not sure why I should not just produce it from my parser and put it into a macro?

scared, that’s not the word, but it is a dead-end way. you want to build the value representing the query, that’s a Ecto.Query structure, and you should have a look at the sources in the lib/ecto/query.ex and nearby files, to understand which are the fields you need to define, and which values to give them.

in particular, check the four fields from, select, wheres, and joins. they are either individual structures, or homogeneous lists of structures. you can build whatever sample query, and get the field from it, like this:

q = Ecto.Query.from(p in "plant", select: [p.code], where: p.location_id==3)
q.from
q.wheres

then try it yourself. it’s just values, there’s no OO complications here.

after some experimenting, I found out that the following works quite fine with my database botanic database:

w = %Ecto.Query{}
w = %{ w | from: %Ecto.Query.FromExpr{source: {"accession", nil}}}
w = %{ w | select: %Ecto.Query.SelectExpr{expr: [
  {{:., [], [{:&, [], [0]}, :code]}, [], []}]}}
Botany.Repo.all(w)

as do the more complex ones (I’m joining plant with accession and location, and filtering based on values in all tables. tables get numbered according to the position in which they appear in the joins list, 0 being reserved for the from.source. 0: plant, 1: accession, 2: location):

w = %Ecto.Query{}
w = %{ w | from: %Ecto.Query.FromExpr{source: {"plant", nil}}}
w = %{ w | joins: [
  %Ecto.Query.JoinExpr{
    source: {"accession", nil}, 
    qual: :inner,
    on: %Ecto.Query.QueryExpr{
      expr: {:==, [], [
        {{:., [], [{:&, [], [0]}, :accession_id]}, [], []},
        {{:., [], [{:&, [], [1]}, :id]}, [], []}
      ]}}},
  %Ecto.Query.JoinExpr{
    source: {"location", nil}, 
    qual: :inner,
    on: %Ecto.Query.QueryExpr{
      expr: {:==, [], [
        {{:., [], [{:&, [], [0]}, :location_id]}, [], []},
        {{:., [], [{:&, [], [2]}, :id]}, [], []}
      ]}}}
  ]}
w = %{ w | select: %Ecto.Query.SelectExpr{expr: [
  {{:., [], [{:&, [], [2]}, :code]}, [], []}, 
  {{:., [], [{:&, [], [1]}, :code]}, [], []}, 
  {{:., [], [{:&, [], [0]}, :code]}, [], []}]}}
Botany.Repo.all(w)

the above is a base query, defining the joins, selecting one field per each of the three tables involved, and returning everything. below I’m adding a wheres list, length 1, containing a few of the operators I have in my grammar.

%{ w | wheres: [%Ecto.Query.BooleanExpr{op: :and, expr: 
    {:==, [], [
      {{:., [], [{:&, [], [2]}, :code]}, [], []}, 
      "GH1"]}}]} |> 
  Botany.Repo.all()
%{ w | wheres: [%Ecto.Query.BooleanExpr{op: :ane, expr: 
    {:in, [], [
      {{:., [], [{:&, [], [1]}, :code]}, [], []}, 
      ["2018.0044", "2018.0045", "2018.0046", "2018.0047"]]}}]} |> 
  Botany.Repo.all()
%{ w | wheres: [%Ecto.Query.BooleanExpr{op: :and, expr: 
    {:or, [], [
      {:==, [], [
        {{:., [], [{:&, [], [2]}, :code]}, [], []}, 
        "GH2"]}, 
      {:==, [], [
        {{:., [], [{:&, [], [1]}, :code]}, [], []}, 
        "2018.0044"]}]}}]} |> 
  Botany.Repo.all()
%{ w | wheres: [%Ecto.Query.BooleanExpr{op: :and, expr: 
    {:and, [], [
      {:==, [], [
        {{:., [], [{:&, [], [2]}, :code]}, [], []}, 
        "GH2"]}, 
      {:==, [], [
        {{:., [], [{:&, [], [1]}, :code]}, [], []}, 
        "2018.0044"]}]}}]} |> 
  Botany.Repo.all()

I haven’t tried yet, but I guess that producing this from the yecc parser will not be much more difficult than the quote representation.