How to use correctly result from Repo.query()

Questions / Help
, ,
1

My data model is like this

Category has_many Products
Products belongs_to Category
Products belongs_to User

What I want from database query is this.
All categories and 10 products for each category and also each user who owns the product.

I post a question here. searched forum.
But couldn’t find a solution for this.
So I ended up using raw sql to get what I want.

here is sql query.

SELECT *
  FROM categories c LEFT JOIN LATERAL
          (SELECT p.*
           FROM products p
           WHERE c.id = p.category_id
           ORDER BY p.updated_at
           LIMIT 10) AS p
           ON 1 = 1
           INNER JOIN users ON p.user_id = users.id

query = "SELECT * FROM categories c LEFT JOIN LATERAL (SELECT p.* FROM products p WHERE c.id = p.category_id ORDER BY p.updated_at LIMIT 10) AS p ON 1=1 INNER JOIN users u ON p.user_id = u.id"

{:ok, result} = Repo.query(query)

And finally I got correct query result from that raw query.
But rows and columns in result, all columns and rows are in single list.
for exmaple,

%Postgrex.Result{
  columns: ["id", "name", "id", "product_name"...etc.],
  rows: [
    [1, "Electronics", "2", "TV",,,,, etc]
    [2, "Software", .....],
    [3, ""....]
  ]
}

All join table’s rows and columns are in same list.

So I need to use Enum.slice to get cols and rows for each schema(Category, Product, User)
for example,

category_cols = Enum.slice(query_result.columns, 0, 4)                                                                                                                                                                                        
category_rows = Enum.map(query_result.rows, &Enum.slice(&1, 0, 4))
 # Build categories
categories = Enum.map(category_rows, &Repo.load(Category, {category_cols, &1})

I have to do this for Product and User. And I need to connect association for each struct manually
Something like this

Enum.map(categories, &Map.put(&1, :products, product))

And I think it is tedious and error prone solution.

So my questions are

  1. Is there any way to convert my raw query to ecto query? So I can get a ready to use struct format.
  2. Is it better to define new absinthe schema object type like :top10_products and define new field same as raw query result?
2

https://hexdocs.pm/ecto/Ecto.Repo.html#c:load/2 is a super handy function for turning query results into ecto structs.

2 Likes
3

Thanks.
Yes I am using Repo.load()
So I got %Category{}, %Prodcut{}, %User{} respectively in each list.
But I don’t know how to these struct to like this

%Category{
  products: %Product{ user: %User{} }
}

I want the same data structure when I use preload