How to swap which table is returned in Ecto?

Questions / Help
,
1

Background

I have a a couple of queries that result in the archival of some metadata items. My objective is to merge these queries into one (if possible).

Code

meta_item_ids_to_archive =
      Post
      |> where([post], post.archive_meta)
      |> select([post], %{item_id: post.meta_id})
      |> subquery()

archive_meta_items_query =
      Meta
      |> join(:inner, [item], item_to_archive in ^meta_item_ids_to_archive,
        on: item.id == item_to_archive.item_id
      )

########
later on
########

Multi.update_all(
  :query_a, 
  archive_meta_items_query, 
  set: [archived: true]
)

So my original query starts with Post and then int eh subsequent query I start with Meta. I want to archive items in the Meta table. My first attempt at merging these queries was the following:


new_query = 
  Post
  |> where([post], post.archive_meta)
  |> join(:inner, [post], item_to_archive in Meta,
        on: post.meta_id == item_to_archive.item_id
  )

Multi.update_all(
  :query_a, 
  new_query, 
  set: [archived: true]
)

Problem

Now, the issue here is that this won’t work:

field archived in update does not exist in schema Post in query (…):

Because my new_query starts with Post instead of Meta, the update operation fails.

Question

  • Is there a way to tell Ecto “I want the Meta table returned instead of Post?”
  • If now, how can I solve this?
2

I’d suggest making use of Repo.to_sql(:update_all, query) and seeing what ecto generates. With SQL you can only update rows in a single table, so that table name needs to show up in th correct place. With ecto that will be the table name of the first binding in the query.

3

And I can’t change the first binding? What I understand here is that no matter what I will always need 2 queries.
I was wondering if using subquery() would allow me to cheat.

4

I don’t think you need a subquery. A plain join might be enough. Though those questions indicate even more that it might be worthwhile investing into learning more about sql – this will in turn make it easier to create queries with ecto as well.

Looking at things just from the ecto perspective can be deceiving, as “code” is usually much more flexible than a declarative sql query.

1 Like
5

The first approach that comes to mind is to reverse the join - since it’s an INNER, either form will return the same rows:

new_query = 
  Meta
  |> join(:inner, [meta], post in Post,
        on: post.meta_id == meta.item_id.
        on: post.archive_meta
  )

Multi.update_all(
  :query_a, 
  new_query, 
  set: [archived: true]
)
1 Like