Exadra37

Exadra37

I am trying to learn the basics of Ecto from Elixir School and I see in their examples that using this syntax Repo.all(from m in Movie, preload: [:actors]) leads to 2 queries being done to the database while using Repo.all(from(m in Movie, join: a in assoc(m, :actors), preload: [actors: a])) leads to only one query being made to the database, and my question is why does not the first syntax only do 1 query to the database?

Is there another way to fetch the associations in only one database call without the need to be such verbose as in the last syntax?

Example:
I would like to be able query with something like Repo.all_with_associations(Movie) or similar, and have only one dtabase call being made to fetch everything…

IEX

First Syntax - Two database calls

iex(42)> Repo.all(from m in Movie, preload: [:actors]
...(42)> )

10:26:40.102 [debug] QUERY OK source="movies" db=0.8ms queue=0.1ms
SELECT m0."id", m0."title", m0."tagline" FROM "movies" AS m0 []

10:26:40.130 [debug] QUERY OK source="actors" db=4.5ms queue=0.1ms
SELECT a0."id", a0."name", m1."id" FROM "actors" AS a0 INNER JOIN "movies" AS m1 ON m1."id" = ANY($1) INNER JOIN "movies_actors" AS m2 ON m2."movie_id" = m1."id" WHERE (m2."actor_id" = a0."id") ORDER BY m1."id" [[1]]
[
  %Example.Movie{
    __meta__: #Ecto.Schema.Metadata<:loaded, "movies">,
    actors: [
      %Example.Actor{
        __meta__: #Ecto.Schema.Metadata<:loaded, "actors">,
        id: 1,
        movies: #Ecto.Association.NotLoaded<association :movies is not loaded>,
        name: "Tyler Sheridan"
      },
      %Example.Actor{
        __meta__: #Ecto.Schema.Metadata<:loaded, "actors">,
        id: 2,
        movies: #Ecto.Association.NotLoaded<association :movies is not loaded>,
        name: "Gary"
      }
    ],
    characters: #Ecto.Association.NotLoaded<association :characters is not loaded>,
    distributor: #Ecto.Association.NotLoaded<association :distributor is not loaded>,
    id: 1,
    tagline: "Something about video games",
    title: "Ready Player One"
  }
]

Second Syntax - One Database call

iex(43)> query = from(m in Movie, join: a in assoc(m, :actors), preload: [actors: a])
#Ecto.Query<from m in Example.Movie, join: a in assoc(m, :actors),
 preload: [actors: a]>
iex(44)> Repo.all(query)

10:28:27.329 [debug] QUERY OK source="movies" db=4.8ms decode=0.1ms queue=0.1ms
SELECT m0."id", m0."title", m0."tagline", a1."id", a1."name" FROM "movies" AS m0 INNER JOIN "movies_actors" AS m2 ON m2."movie_id" = m0."id" INNER JOIN "actors" AS a1 ON m2."actor_id" = a1."id" []
[
  %Example.Movie{
    __meta__: #Ecto.Schema.Metadata<:loaded, "movies">,
    actors: [
      %Example.Actor{
        __meta__: #Ecto.Schema.Metadata<:loaded, "actors">,
        id: 1,
        movies: #Ecto.Association.NotLoaded<association :movies is not loaded>,
        name: "Tyler Sheridan"
      },
      %Example.Actor{
        __meta__: #Ecto.Schema.Metadata<:loaded, "actors">,
        id: 2,
        movies: #Ecto.Association.NotLoaded<association :movies is not loaded>,
        name: "Gary"
      }
    ],
    characters: #Ecto.Association.NotLoaded<association :characters is not loaded>,
    distributor: #Ecto.Association.NotLoaded<association :distributor is not loaded>,
    id: 1,
    tagline: "Something about video games",
    title: "Ready Player One"
  }
]

Showing Posts 1 to 5

paulsullivanjr

paulsullivanjr

It’s not being verbose it’s about being explicit. Ecto will only provide what you specifically ask for by design. I think it’s a great design choice and you always know what the query will do. As far as a second query vs join, if performance is an issue you could test writing the query with a join.

Exadra37

Exadra37 OP

I love explicit code, but not necessarily verbose code… I mean I don’t care how is being done, I am happy that the semantics are explicit enough for me to know what will happen. So I think that having this method name or a similar one Repo.all_preload(Movie) will be explicit without the need to expose lower level details.

This is to much verbose and my brain needs to parse it each time I found it in the code… I would prefer just to have to use it in advanced use cases.

Not having an high level approach has of my suggestion, this would be explicit enough without being to much verbose… But I am curious to know why this syntax does not perform only one query.

LostKobrakai

LostKobrakai

It cannot perform the preloading in one query automatically, because it might change the results especially if you’re using offsets or limits and one-to-many associations, which join more rows than your primary selected table has.

axelson

axelson

Scenic Core Team

Also in your “Second Syntax - One Database call” section another limitation is that if each movie has ten actors in it, you are returning a row for each movie/actor pair.

i.e.:

  1. Movie 1, Actor 1
  2. Movie 1, Actor 2
  3. Movie 1, Actor 3

and this includes all the field of the movie each time which is a lot of duplicated data over the wire. There might be a way around this but I do not currently know of it.

OvermindDL1

OvermindDL1

It can be a whole lot of duplicate data, and the way around it is indeed 2 queries :slight_smile:

— All posts loaded —

Where Next? Top

Trending in Questions Top

RSP87
I’m working on a project that simulates the bumbl example in the programming phoenix book. It acts almost like an email client. We have a...
New
kpanic
Hi everyone, I am toying with the idea of building a “match maker” for giving personal help to people that wants to start coding. I sta...
New
nseaSeb
Hello, I know there is an approach for handling lists that allows for optimized traversal, but I can’t recall the specific method (somet...
New
brecabral
Documentation While reading the Scoped Routes section, I noticed that the documentation currently refers to a problem without explainin...
New
velrest
So my question is quite simple and i have found no conclusive answer on forum, google or AI. Should we use :erlang.float for Integer to ...
New
asweet-confluent
I recently noticed that Elixir’s Logger defaults its primary log level to :debug when no :logger, :level application configuration is pre...
New
apz
I’m new to elixir and just tried to install the elixirLS extension for VScode(ium) and it is throwing some errors that I would like help ...
New

Other Trending Topics Top

JesseHerrick
Hey, I’m Jesse and I’m the main contributor behind Dexter, a full-featured, lightning-fast Elixir LSP optimized for large codebases. It s...
New
mudasobwa
I am happy to introduce the very α version of the new programming language compiled to BEAM. Welcome Cure. It has literally three kille...
New
marciok
Hi there! We created Gust: A task orchestrator inspired by Airflow. For those who have never heard about Aiflow, it’s a Python-based wor...
New
mhanberg
Hi everyone! The first release candidate for the Expert language server project is now available! We’ve published a press release detai...
New
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
Dmk
Xamal is a deployment tool for Elixir apps that deploys native releases to bare metal servers over SSH. It’s a port of GitHub - basecamp/...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews