How to convert key-based-query to macro-based-query with parent_as?

Questions / Help
,
1

Background

For investigation purposes, I am trying to convert a piece of code from another thread from keyword-based-query to a macro-based-query. I am doing this because I am interested in finding out how fast it is.

Code

tops = 
  from top in "file_info_with_counts", 
    where: top.home_id == parent_as(:parent).home_id,
    where: top.path == parent_as(:parent).path,
    order_by: [asc: top.item_id],
    limit: 3,
    select: %{id: top.id}

from parent in "file_info_with_counts",
  as: :parent,
  group_by: [parent.home_id, parent.path],
  lateral_join: top in subquery(tops), 
  on: true,
  select: %{home_id: parent.home_id, path: parent.path, item_id: top.id}

My original attempts successfully convert the tops subquery, but I can’t convert the other one:

tops =
       "file_info_with_counts"
      |> where(
        [fi],
        fi.home_id == parent_as(:parent).home_id and
          fi.path == parent_as(:parent).path
      )
      |> order_by([fi], asc: fi.id)
      |> limit(3)
      |> select([fi], %{
        item_id: fi.id,
        home_id: fi.home_id,
        path: fi.path
      })
      |> subquery()

    # cant think of a proper name for this really
    parent_q =
      "file_info_with_counts"
      |> join(:inner_lateral, [item], top in ^tops, on: true)
      |> group_by([item], [parent_as(:parent).home_id, parent_as(:parent).path])
      |> select([item], %{
        home_id: parent_as(:item).home_id,
        path: parent_as(:item).path,
        item_id: item.item_id
      })
      |> subquery()

Clearly I am doing something wrong, as not matter what I do, I can never define :parent. How can I fix this?

2

You still need the from macro for that: from("file_info_with_counts", as: :parent) |> …

3

Will this work even if file_info_with_counts is actually a subquery and not a table?

4

I have tried using from() as you mentioned, but the binding cannot happen:

** (Ecto.Query.CompileError) can’t apply alias :parent, binding in from is already aliased to :item

This is the query I am using:

file_info_with_counts #this is a subquery
      |> from(as: :parent)
      |> join(:inner_lateral, [item], top in ^top_3_items, on: true)
      |> group_by([item], [parent_as(:parent).dataset_id, parent_as(:parent).path])
      |> select([item], %{
        dataset_id: parent_as(:parent).dataset_id,
        path: parent_as(:parent).path,
        item_id: item.item_id
      })
      |> subquery()

So I though about changing every occurrence of :parent to :item and ended up with this:

tops =
      file_info_with_counts
      |> where(
        [item],
        item.home_id == parent_as(:item).home_id and
          item.path == parent_as(:item).path
      )
      |> order_by([item], asc: item.id)
      |> limit(3)
      |> select([item], %{
        item_id: item.id,
        home_id: item.home_id,
        path: item.path
      })
      |> subquery()

    parent_q =
      file_info_with_counts
      |> join(:inner_lateral, [item], top in ^top_3_items, on: true)
      |> group_by([item], [parent_as(:item).home_id, parent_as(:item).path])
      |> select([item], %{
        home_id: parent_as(:item).home_id,
        path: parent_as(:item).path,
        item_id: item.item_id
      })
      |> subquery()

However upon execution using parent_q |> Repo.all this then blows up:

(Ecto.SubQueryError) the following exception happened when compiling a subquery.
** (Ecto.QueryError) could not find named binding parent_as(:item) in query:
(…)

It appears no matter what I do, I cant get the bindings right.

5

Can you show the working query before the refactoring?

6

This is what file_info_with counts looks like beneath the hood:

from(item in "items_table", as: :item)
    |> where([item: item], not item.deleted)
    |> where([item: item], not item.meta)

Before my attempted refactor, this is what was proposed (not tested, I am trying to do that now):

tops = 
  from top in "file_info_with_counts", 
    where: top.home_id == parent_as(:parent).home_id,
    where: top.path == parent_as(:parent).path,
    order_by: [asc: top.item_id],
    limit: 3,
    select: %{id: top.id}

from parent in "file_info_with_counts",
  as: :parent,
  group_by: [parent.home_id, parent.path],
  lateral_join: top in subquery(tops), 
  on: true,
  select: %{home_id: parent.home_id, path: parent.path, item_id: top.id}

Is this what you needed ?

7

I highly recommend testing this solution before attempting any refactor. Even a simple, single test case — then you can refactor till your heart’s content and have at least some baseline to test against.

One key thing to understand: it’s not macro syntax or keyword syntax with Ecto. They’re equivalent and composable. Once you have the current version working, you can move a single clause over to macro style if you wish, test that, then work on the next:

from(
  parent in "file_info_with_counts",
  as: :parent,
  group_by: [parent.home_id, parent.path],
  lateral_join: top in subquery(tops), 
  on: true
)
|> select([parent: parent, item: item], %{
  home_id: parent.home_id,
  path: parent.path,
  item_id: top.id
})
1 Like
8

I found out how to fix the query:

tops_child_query =
      from(top in file_info_with_counts,
        where: top.home_id == parent_as(:previous_binding).dataset_id,
        where: top.path == parent_as(:previous_binding).path,
        order_by: [asc: top.id],
        limit: 3,
        select: %{id: top.id}
      )

    top_3_items =
      from(parent in file_info_with_counts,
        inner_lateral_join: top in subquery(tops_child_query),
        on: true,
        group_by: [parent.home_id, parent.path, top.id],
        select: %{home_id: parent.home_id, path: parent.path, item_id: top.id}
      )

The issue here what that file_info_with_counts has a previous binding that would conflict with everything.
With that out of the way, the conversion to macro based query should not be a problem.

    tops_child_query =
      file_info_with_counts
      |> where(
        [top],
        top.home_id == parent_as(:previous_binding).dataset_id and top.path == parent_as(:previous_binding).path
      )
      |> order_by([top], asc: top.id)
      |> limit(3)
      |> select([top], %{id: top.id})

# The other query follows a similar vein
1 Like