3 replies

Using an inline aggregate of unrelated resource filtering by related resources

I believe I could do this using a custom relationship and falling back to Ecto, but I was wondering if there was a "Ash way" of doing what I'm trying to do here.

I have four resources/tables

SceneWidget

scene_id
widget_version_id

scene_id
widget_version_id

Scene

id
channel_id

id
channel_id

WidgetVersion

id
widget_id

id
widget_id

WidgetMemory

channel_id (primary)
widget_id (primary)

channel_id (primary)
widget_id (primary)

I'm trying to write a calculation to go from

SceneWidget

SceneWidget

WidgetMemory

WidgetMemory

Using SQL I would normally write something like

SELECT widget_memories.*
FROM scene_widgets
JOIN scenes ON scene_widgets.scene_id = scenes.id
JOIN widget_versions ON scene_widgets.widget_version_id = widget_versions.id
JOIN widget_memories ON widget_memories.widget_id = widget_versions.widget_id AND widget_memories.channel_id = scenes.channel_id

SELECT widget_memories.*
FROM scene_widgets
JOIN scenes ON scene_widgets.scene_id = scenes.id
JOIN widget_versions ON scene_widgets.widget_version_id = widget_versions.id
JOIN widget_memories ON widget_memories.widget_id = widget_versions.widget_id AND widget_memories.channel_id = scenes.channel_id

Trying to do this as a calculation I've tried:

calculate :memory,
          :map,
          expr(
            first(EKG.Scenes.WidgetMemory,
              query: [
                filter: expr(channel_id == parent(scene.channel_id) and widget_id == parent(widget_version.widget_id))
              ],
              field: :data
            )
          )

calculate :memory,
          :map,
          expr(
            first(EKG.Scenes.WidgetMemory,
              query: [
                filter: expr(channel_id == parent(scene.channel_id) and widget_id == parent(widget_version.widget_id))
              ],
              field: :data
            )
          )

and

calculate :memory,
          :map,
          expr(
            first(EKG.Scenes.WidgetMemory,
              join_filters: %{
                scene: expr(channel_id == parent(channel_id)),
                widget_version: expr(widget_id == parent(widget_id))
              },
              field: :data
            )
          )

calculate :memory,
          :map,
          expr(
            first(EKG.Scenes.WidgetMemory,
              join_filters: %{
                scene: expr(channel_id == parent(channel_id)),
                widget_version: expr(widget_id == parent(widget_id))
              },
              field: :data
            )
          )

But neither seem to work. Does Ash have a suggestion on how to to model these complex relationships? Or are you meant to just fallback to CustomRelationship/Ecto in these situations?

Using an inline aggregate of unrelated resource filtering by related resources

I believe I could do this using a custom relationship and falling back to Ecto, but I was wondering if there was a "Ash way" of doing what I'm trying to do here.

I have four resources/tables

SceneWidget

scene_id
widget_version_id

scene_id
widget_version_id

Scene

id
channel_id

id
channel_id

WidgetVersion

id
widget_id

id
widget_id

WidgetMemory

channel_id (primary)
widget_id (primary)

channel_id (primary)
widget_id (primary)

I'm trying to write a calculation to go from

SceneWidget

SceneWidget

WidgetMemory

WidgetMemory

Using SQL I would normally write something like

SELECT widget_memories.*
FROM scene_widgets
JOIN scenes ON scene_widgets.scene_id = scenes.id
JOIN widget_versions ON scene_widgets.widget_version_id = widget_versions.id
JOIN widget_memories ON widget_memories.widget_id = widget_versions.widget_id AND widget_memories.channel_id = scenes.channel_id

SELECT widget_memories.*
FROM scene_widgets
JOIN scenes ON scene_widgets.scene_id = scenes.id
JOIN widget_versions ON scene_widgets.widget_version_id = widget_versions.id
JOIN widget_memories ON widget_memories.widget_id = widget_versions.widget_id AND widget_memories.channel_id = scenes.channel_id

Trying to do this as a calculation I've tried:

calculate :memory,
          :map,
          expr(
            first(EKG.Scenes.WidgetMemory,
              query: [
                filter: expr(channel_id == parent(scene.channel_id) and widget_id == parent(widget_version.widget_id))
              ],
              field: :data
            )
          )

calculate :memory,
          :map,
          expr(
            first(EKG.Scenes.WidgetMemory,
              query: [
                filter: expr(channel_id == parent(scene.channel_id) and widget_id == parent(widget_version.widget_id))
              ],
              field: :data
            )
          )

and

calculate :memory,
          :map,
          expr(
            first(EKG.Scenes.WidgetMemory,
              join_filters: %{
                scene: expr(channel_id == parent(channel_id)),
                widget_version: expr(widget_id == parent(widget_id))
              },
              field: :data
            )
          )

calculate :memory,
          :map,
          expr(
            first(EKG.Scenes.WidgetMemory,
              join_filters: %{
                scene: expr(channel_id == parent(channel_id)),
                widget_version: expr(widget_id == parent(widget_id))
              },
              field: :data
            )
          )

But neither seem to work. Does Ash have a suggestion on how to to model these complex relationships? Or are you meant to just fallback to CustomRelationship/Ecto in these situations?

Using an inline aggregate of unrelated resource filtering by related resources

SceneWidget

Scene

WidgetVersion

WidgetMemory

Using an inline aggregate of unrelated resource filtering by related resources

SceneWidget

Scene

WidgetVersion

WidgetMemory

Similar Threads

Similar Threads

Similar Threads