Dynamic Query Generation

Questions / Help
, ,
1

I’m trying to build a query builder that takes as input a bunch of JSON and parses it out
into a valid Ecto query. I’ve got most of the actual parsing working, but I can’t quite grok
the process of actually turning a “rule” from the JSON into a query.

Essentially what I’m doing now is this:

  def json_to_query(json) do
    query = from students in "students",
      select: students.id,
      distinct: true,
      limit: 10

    parse(query, json)
  end

  defp parse(source_query, node) do
    res = node
      |> Map.get("rules")
      |> Enum.reduce(source_query, fn(r, q) -> parse_node(q, r) end) 

    res
  end

  defp parse_node(source_query, node) do
    if (is_top_level_node(node)), do: parse(source_query, node), else: sql_from_rule(source_query, node)
  end

  def sql_from_rule(source_query, rule) do
    field    = Map.get(rule, "field")
    value    = Map.get(rule, "values")   |> List.first |> Map.get("value") |> get_value
    operator = Map.get(rule, "operator") |> get_operator

    # IEx.pry
    source_query
      |> join_clause(field)
      |> where_clause(field, value, operator)
  end

  defp join_clause(query, field) do
    table = String.split(field, ".") |> List.first

    case table do
      "students" -> query
      _          -> query |> join(:inner, [s], t in ^table, s.id == t.student_id)
    end
  end

  defp where_clause(query, field, value, operator) do
    column = String.split(field, ".") |> List.last

    # The obvious problem here is that it's going to end up generating the initial
    # query just fine, and the joins are fine as far as I can tell, but this
    # is not referencing the correct table when it generates the where. Instead of 
    # `where grades.term == "T1"` I end up with `where "grades.term" == "T1"` which is
    # just comparing the strings.
    query
      |> where(^column == ^value)
  end

I’ve found a few related forum posts, but can’t quite figure out how to make the code work
for my situation. Here’s an example of a rule that I’ll be parsing:

            {  
               "rules":[  
                  {  
                     "id":"9a9b899a-0123-4456-b89a-b15f9237a0de",
                     "field":"students.gifted",
                     "type":"boolean",
                     "input":"boolean",
                     "operator":"equal",
                     "values":[  
                        {  
                           "type":"boolean",
                           "value":true
                        }
                     ]
                  },
                  {  
                     "rules":[  
                        {  
                           "id":"aa98baaa-89ab-4cde-b012-315f9238650b",
                           "field":"grades.term",
                           "type":"select",
                           "input":"select",
                           "operator":"select_equals",
                           "values":[  
                              {  
                                 "type":"select",
                                 "value":"T1"
                              }
                           ]
                        },
                        {  
                           "id":"a9bb8a8b-4567-489a-bcde-f15f92389859",
                           "field":"students.school_id",
                           "type":"select",
                           "input":"select",
                           "operator":"select_any_in",
                           "values":[  
                              {  
                                 "type":"multiselect",
                                 "value":[  
                                    "234siteid",
                                    "456siteid"
                                 ]
                              }
                           ]
                        }
                     ],
                     "condition":"AND"
                  }

The above should generate something like:

select
  id,
  students.gifted,
  grades.term,
  students.school_id
from
  students s
    join grades g on s.id == g.student_id
where
  s.gifted == true and
  (grades.term == "T1" and s.school_id in ("234siteid", "456siteid"))

I’m looking into dynamic queries but I
don’t quite understand how I could use them in this context. I’d appreciate any help.

2 Likes
2

Using Ecto’s field I’m able to get to the point of doing something like this:

    column = String.split(field, ".") |> List.last |> String.to_atom

    query
      |> where([s, ..., c], field(c, ^column) == ^value)

The problem is that the table inside of c isn’t always correct.

1 Like
3

Congrats! It looks like you hit the same problems that I do with Ecto (which would be solved by named tables). :slight_smile:

I have a very bad but workable solution (that no one posted a better one) at: Ecto Query Patterns - Named joins ^.^;

2 Likes