belongs_to association based a jsonb column, or ability to preload a join without an association?

[Eugene Gilburg]

Hi,

I'm experimenting with free-form query building from a jsonb store (an "entities" table with a "data" jsonb field).

I'm trying to write a query like this:

query = from entity in Entity,

  select: entity,

  join: parent_entity in Entity,

  on: fragment(

    "(?->?) = (?->?)", entity.data, "parent_id", parent_entity.data, "id"

  ),

  preload: [ parent_entity: parent_entity ]

With the "preload" argument, I get an error that parent_entity is not defined as an association (I don't know how to define it, because it can dynamic based on specified jsonb key rather than exact column). Without the "preload", the query works fine, except I can't do "entity.parent_entity" call as parent_entity is not included in the returned structure, despite the join working fine.

Is is possible to write the above query so it'll preload parent_entity based on the above jsonb fragment, or express the jsonb fragment as an association? In both cases, the end goal is to be able to write code like "entity.parent.entity.data["something"]"

Thanks,

Eugene

[José Valim]

Unfortunately it is not possible today. The best you can do is to retrieve both:

select: {entity, parent_entity}

And do a separate pass to unify them. In the future we will allow you to write this:

select: %{entity | parent_entity: parent_entity}

Which is a little bit better but the ideal would be to allow the ON clause to be customized. Please open up an issue with exactly this use case, I will think more about it later. :)

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/4ad35947-6b3a-4c64-982d-7784c8d9d421%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Eugene Gilburg]

Thanks for the reply, will do! Some more questions:

1. Even if I wasn't using jsonb, but just doing a JOIN with custom fields on the joined tables, do you envision Ecto supporting a view-like representation of enumerated columns, or alternatively all columns from all join tables, embedded on the same structure?

(Similar to Rails some_scope.joins(:something_else).select("something_else.foo as something_else_bar"), object.something_else_bar becomes available on each instance, whether or not it exists on schema or not). This makes building flat reports from composite queries easier.

2. For jsonb, it'd also be nice to have data->object syntax in query to pull specific fields from the data as regular fields in the returned data.

Currently I achieved both of the above by hand using:

    sql = """

      SELECT

        child_entities.id AS child_entity_id,

        child_entities.data->>'name' AS child_object_name,

        parent_entities.id AS parent_entity_id,

        parent_entities.data->>'name' AS parent_object_name

      FROM entities child_entities

      INNER JOIN entities parent_entities

        ON (child_entities.data->'parent_id') = (parent_entities.data->'id')

      ORDER BY child_entities.data->>'name'

    """

    {_, result} = Ecto.Adapters.SQL.query(Repo, sql, %{})

    entities = result[:rows] |>

      Stream.map(&Stream.zip(result[:columns], &1) |>

      Enum.into(%{})) |>

      Enum.to_list

Returning:

[

  %{

    "child_entity_id" => 1, 

    "child_object_name" => "foo",

    "parent_entity_id" => 2, 

    "parent_object_name" => "bar"

  }, 

  ...

]

This seems to work, but obviously requires a lot of hand-crafted parsing into something I can actually output as JSON API. And I'm not sure how optimized the above code is. :)

Eugene