Joins and 3 tables with associations and preloading

Questions / Help
, ,
1

I have the following schema and associations:

Restaurant has_many Review
Review belong_to Restaurant
Review belongs_to User
User has_many Review

I can easily query and retrieve all the reviews of a specific restaurant by preloading the Reviews, but I can’t seem to be able to retrieve the specific user who submitted the review in one query. I’m currently doing this in my live view:

 |> assign(:restaurant, Listing.get_restaurant!(id))
 |> assign(:reviews, Listing.get_reviews_by_restaurant_id(id))

Which then allows me to display the info about the restaurant first. Then the second query gets me all the reviews for that specific restaurant.

def get_reviews_by_restaurant_id(restaurant_id) do
    Repo.all(
      from r in Review,
        where: r.restaurant_id == ^restaurant_id,
        join: rt in assoc(r, :restaurant),
        join: u in assoc(r, :user),
        preload: [[restaurant: rt], [user: u]],
        order_by: [desc: :id]
    )
  end

Wondering is there a more idiomatic way to retrieve the restaurant and all its associated reviews + user info in one query instead of 2 round trips to the DB?

2

Hi,

is the following working?

def get_restaurant_with_reviews!(restaurant_id) do
  from(
    rt in Restaurant,
    left_join: r in assoc(rt, :reviews),
    left_join: u in assoc(r, :user),
    preload: [reviews: {r, user: u}]
  )
  |> Repo.get!(restaurant_id)
end
3

Personally, I would just run multiple queries. It’s not nearly as inefficient as people make it out to be, and can even less efficient to do a single query in Ecto. There is past discussion here.

In your situation, I would most likely do one query to load the restaurant, then another to load all reviews with preloaded user.

4

yeap that works, I didn’t know I could pass the first query as an arg to the Repo.get!