Question about query and assoc

Questions / Help
, ,
1

576/5000

I have 4 models, Resellers, Address, Products, Categories

I need to list all Resellers together with the addresses and products, however they are Resellers that contain products of category X (if the params came with category in the request)

But every time I try to do something, it goes wrong.
I tried to use preload but I can’t limit the quantity of products in the category, so there are more than 100 products for each Reseller.

I really need help, I tried several times and was unsuccessful, I don’t know if I modeled the best way but I’m trying to learn.

schema “resellers” do
field :description, :string
field :email, :string
field :encrypted_password, :string
field :name, :string
field :tax_code, :string
field :phone, :string
field :store_name, :string
# VIRTUAL FIELDS
field :password, :string, virtual: true
field :password_confirmation, :string, virtual: true
has_one :address, Address
has_many :products, Product
timestamps()
end

schema “addresses” do
field :complement, :string
field :country, :string
field :number, :string
field :observation, :string
field :state, :string
field :street, :string
field :zip_code, :string
field :lat, :float
field :long, :float
belongs_to :reseller, Reseller
timestamps()
end

schema “products” do
field :description, :string
field :name, :string
field :price, :decimal
field :brand, :string
field :quantity, :decimal
field :size, :string
field :category_id, :integer
field :deleted_at, :utc_datetime
field :is_active, :boolean
field :cost, :decimal
belongs_to :reseller, Reseller
has_many :product_images, ProductImage
timestamps()
end

schema “categories” do
field :name, :string
timestamps()
end

2

Can you provide a database schema?

1 Like
3

Yes i can. Its done.

4

Task 1: “I need to list all Resellers together with the addresses and products”

Repo.all(Reseller)
|> Repo.preload([:address, :products])

I suppose something like this is a good start for the above task.

I’m unclear on the rest of your requirement though:

however they are Resellers that contain products of category X (if the params came with category in the request)

Can you try to clear that? Give an example of given request and expected response.

5

Can you try to clear that? Give an example of given request and expected response.
Expected response:
api.get(/resellers?category=Candy)

Response:

resellers: [
%{
description,
email,
encrypted_password,
name,
tax_code,
phone,
store_name,
products: [
%{
description,
name,
price,
brand,
quantity,
size,
category_id,
deleted_at,
is_active,
cost,
category: 'Candy'},
repeat products...,
],
address: %{fully_address},
]

But i need to limit products ( example 10/20/30) and limit resellers (5/10/15) example.

80/5000

So that the user does not receive everything I have in the database at once.

For example, if 30 products come from 30 different resellers, there will already be 900 different results.

6

Pagination can be a later concern. Important is to have something working first.

category_id = Repo.get_by(Category, name: ^category_name)

Repo.all from r in Reseller,
           join: p in assoc(r, :products),
           where: p.category_id == ^category_id,
           preload: [products: p, :address]

You could further refine this and also add another join for the Category and do it all in one query, but this seemed a bit easier for the 1st iteration.

Check the Ecto.Query.preload/3 documentation.

For pagination, have a look at Ecto.Query.limit/3 and Ecto.Query.offset/3

I hope it works (I only had the ElixirForum editor compiler assisting me :grin:)

7

Thanks a lot for the help,
I had already written exactly what you wrote but I cannot limit the result of the preload products.

What can I do? I’m losing on how to deal with this problem.

I have already read Repo.Query.limit

Note: keep in mind operations like limit and offset in the preload query will affect the whole result set and not each association. For example, the query below: