How to dynamically build select query for a dynamically built query?

Phoenix Forum Questions / Help
,
1

I am building a function that dynamically builds a query from user input. There are 3 main parameters; filters, includes (which assoc to include; just the belongs_to assoc), and which columns to select from the joins.

  def get(opts \\ []) do
    allowed_opts = [
      filters: [:where_ids, :where_product_name, :in_market],
      include: [:image, :product],
      select: %{}
    ]

    if Enum.any?(Keyword.keys(opts), &(!Keyword.has_key?(allowed_opts, &1))),
      do: raise("Allowed opts #{inspect(allowed_opts)}")

    filter_opts =
      Keyword.get(opts, :filters) || [where_ids: nil, where_product_name: nil, in_market: nil]

    includes_opts = Keyword.get(opts, :include) || [image: false, product: false]
    select_opts = Keyword.get(opts, :select) || nil

    opts =
      Enum.reject(
        Keyword.merge(filter_opts, includes_opts),
        fn {_, v} = _opt ->
          is_nil(v)
        end
      )

    base_query = from(v in Variant)

    query =
      Enum.reduce(opts, base_query, fn
        {:in_market, country_code}, query -> in_market(query, country_code)
        {:where_ids, ids}, query -> with_id(query, ids)
        {:image, true}, query -> select_image(query)
        _, query -> query
      end)

    query
    |> Repo.all()
  end

For reference, the queries that get composed are the following.

  def select_image(query \\ Variant) do
    query
    |> join(:left, [v], image in Image, on: image.id == v.image_id, as: :image)
  end

  def with_id(query \\ Variant, ids) do
    query
    |> where([v], v.id in ^ids)
  end

  def in_market(query \\ Variant, country_code) do
    query
    |> join(:left, [v], vm in VariantMarket, on: vm.variant_id == v.id, as: :variant_markets)
    |> join(:left, [v, variant_markets: vm], m in Market, on: vm.market_id == m.id, as: :market)
    |> where([market: m], m.country_code == ^country_code)
  end

This works great, but I’d like to allow the caller to determine which columns to select via the function params as well.

The main difficulty i’m finding is building both the keyword list of bindings (since not all assocs are always available depending on the function inputs), and the column → map key selection. I imagined it looking something like:

query = if is_nil(select_opts) do
        query
      else
        select(query, dynamic_binding, dynamic_select_opts)
      end

And finally

    query
    |> Repo.all()

The endgoal is to be able to do something like this in a dynamic way

          Variants.in_market(country_code)
          |> Variants.select_image()
          |> Variants.search_by_product_name(text)
          |> select([v, vm, product: p, image: image], %{
            id: v.id,
            variant_id: v.id,
            name: v.name,
            price: vm.price,
            product_name: p.name,
            image: image
          })
          |> Repo.all()

But I’m not sure which direction to look into. Any help would be much appreciated! :slight_smile: