Sanjibukai

How to fetch data field between 2 models from 2 Context Schema (that share the same Table)

Hello everybody!

I love how Phoenix (with Ecto) allows clean boundaries thanks to context and schemas.
But I just came across something that seems trivial yet I’m not able to find how to manage that..

The question might be confusing so here is a quick example..
Let’s say I have one database table about cars..
And I have two contexts one for the sales and one for the mechanics.
So Sales.Car deal with sales related data like price etc. while Mech.Car deal with technical datas and metrics about the car..
Each Schema on each Context having their own changeset it’s simple and clean etc.

However, now I want to display somewhere like in a Dashboard, data from both context/schemas…
Let’s say for example Sales.Car.price and Mech.Car.mileage..

If I already have access to one Model, e.g Sales.Car in one of my controller (which I fetched from the database).. Now how can I have access to the data from the other context?

This seems really trivial, but yet I have no clue how to do that..

If it was two different tables, I have put a relation like CarMetrics belongs to Car (which CarMetrics would had the car_id), and I would have fetch the Car with the CarMetrics preloaded.

However, here there’s no key to reference..

One easy solution could be to fetch from the other context using the ID of my previous fetch..

sales_car = Sales.get_car(..)
metrics_car = Mech.get_car(sales_car.id)

But here, there will be two database query right..
So is it possible to have access to those columns with one query?

Thank you for any suggestions..

8 comments

/phoenix #ecto #ecto-schema

0 1959 8

2021-04-21 01:19:17 UTC

First 8 of 8 Posts

pmangalakader

You can use the ecto joins clause combined with a where match clause.

Here’s an article, that details it out: Please feel free to skip ahead to InnerJoins section in the following:

Official link:

It’s easier to add a reference key in your relationship table to make this simpler, but I’m not aware of the intricate details of your implementation.

Post #1

Sanjibukai

Thanks for the response…
However, I was pretty sure my explanations will be confusing…

I have a single table in the database with many columns…

But I have different Schemas (for different context) that lists only part of what they deal with…

So it’s not a join per se, right?

Post #2

pmangalakader

Even though they are different contexts, you can access using alias in the other context for using them in your join clause.

Your query should be like in your Sales Context file:

alias Mech.Car

q = from s in Sales.Car,
       joins: m in Mech.Car,
       where: s.id == m.sales_id,
       select: %{price: s.price, mileage: m.mileage}

Repo.all(q)

Post #3

Sanjibukai

Thanks.. I’ll try that!

Post #4

pmangalakader

You can directly query using the table name (Ecto.Query — Ecto v3.14.0) instead of using the contexts.

q = from c in "cars" #tablename here
      where: c.id == c.sales_car_id #comparison columns
      select: {c.price, c.mileage}

The joins clause probably is an overkill and it’s way better to do it this way. I misinterpreted the real question probably like two tables two contexts and hence the dilemma.

Post #5

Sanjibukai

Well.. I wonder why I didn’t think for that..
In fact my models are a little more complex..
And the best would have been to be able to have “free access” to any fields, like when we are doing a Repo.preload..

E.g. it’s kinda like inheritance.. A Sales.Car is a Mech.Car.. But yeah… I don’t like to go into STI and the like..

Honestly I think I’ll simply fetch two times the DB and give back each models in a tuple..

Post #6