Building a custom select in Ecto queries

Question

I am building an application using Phoenix and Ecto 2.0 that stores a bunch of data in several models.

I'd like to allow users to perform a query and select only a subset of all fields.

I have been able to get the criteria working perfectly (Ecto was way easier to work with in that regard than I expected; big props to the devs!), however I haven't been able to figure out selecting things.

My initial query looks like this:

query =
  from e in Event,
    left_join: t in assoc(e, :event_type),
    left_join: r in assoc(e, :event_reason_code),
    left_join: o in assoc(e, :event_outcome_code),
    left_join: i in assoc(e, :item),
    left_join: l in assoc(e, :location),
    left_join: c in assoc(l, :client)

I'd like to be able to dynamically assign a select.

Right now, I have it looking like:

["event.id", "client.name", ...]

Where I then pattern match based upon what comes before the first period and then build the SQL to select the proper field.

I tried doing something like this to give you an idea of what I was trying to accomplish:

defp build_selects(query, event_query) do
  select query, [e, t, r, o, i, l, c], Enum.reduce(event_query.select, {}, fn(key, acc) ->
    field =
      case key do
        "event.info." <> rest -> fragment("?->>?", field(e, :info), String.to_atom(rest))
        "event." <> rest -> field(e, String.to_atom(rest))
        "event_type." <> rest -> field(t, String.to_atom(rest))
        "event_reason_code." <> rest -> field(r, String.to_atom(rest))
        "event_outcome_code." <> rest -> field(o, String.to_atom(rest))
        "item.info." <> rest -> fragment("?->>?", field(i, :info), String.to_atom(rest))
        "item." <> rest -> field(i, String.to_atom(rest))
        "location.info." <> rest -> fragment("?->>?", field(l, :info), String.to_atom(rest))
        "location." <> rest -> field(l, String.to_atom(rest))
        "client.info." <> rest -> fragment("?->>?", field(c, :info), String.to_atom(rest))
        "client." <> rest -> field(c, String.to_atom(rest))
      end

    Tuple.append acc, field
  end)
end

Unfortunately, this fails with an error undefined function c/0 in the build_select/2 call within build_selects/2.

I have worked out a way to build the query using raw SQL that works pretty well but I know this opens myself up to injection attacks and the like, so I'd like to just stand on the shoulders of Ecto, if possible.

I hope this makes sense; if it doesn't I'll try to clarify.

Thanks!

Update

I browsed through Ecto's source code some more and came across Ecto.Query.API.take, which seemed like it would fit the bill. I tried creating a map (called s_map) that contains all the models and a list of fields to select (if any). I then set up the select here:

from [e, t, r, o, i, l, c] in query,
  select: {take(e, ^Map.get(s_map, :event, [])),
           take(t, ^Map.get(s_map, :event_type, [])),
           take(r, ^Map.get(s_map, :event_reason_code, [])),
           take(o, ^Map.get(s_map, :event_outcome_code, [])),
           take(i, ^Map.get(s_map, :item, [])),
           take(l, ^Map.get(s_map, :location, [])),
           take(c, ^Map.get(s_map, :client, []))}

This doesn't seem to work entirely either, as there must be at least one field or else it errors.

fmcgeough fmcgeough · Accepted Answer · 2017-02-02T15:01:35

For the problem of "at least one field or else it errors", I would build the tuple specifying the select in a function. The function would check to ensure that some column was being selected. If a caller specified no valid columns then an error should be returned. You can then decide how to handle that error in the caller.

Building a custom select in Ecto queries

Update

1 Answers