I'm getting response from Ecto Schema query with joins multiple tables.
Here is my query:
query =
from(
item in ItemSchema,
join: item_cat in ItemCategorySchema,
on: item.item_category_id == item_cat.item_category_id,
preload: [:BusinessSchema],
select: %{
"item_id" => item.item_id,
"item_name" => item.item_name,
"description" => item.description,
"item_category" => item_cat.category_name,
"businesses" => item.business_ids,
}
)
Repo.all(query)
Here is Item Schema:
schema "items" do
field(:item_id, :string)
field(:item_category_id, :string)
field(:user_id, :string)
field(:business_ids, {:array, :string})
field(:item_name, :string)
field(:description, :string)
end
Here is Item Category Schema:
schema "item_categories" do
field(:item_category_id, :string)
field(:item_category_id, :string)
field(:category_name, :string)
end
Here is Business Schema:
schema "businesses" do
field(:business_id, :string)
field(:business_name, :string)
end
But business_ids not populated with an item. How to populate list of businesses which are in each item.
Desirable result:
[
%{
"item_id" => "id_01",
"item_name" => "A",
"description" => "A description",
"item_category" => "some category",
"businesses" => [
${
"business_id" => "business_id_01",
"business_name" => "Business Name 1",
},
${
"business_id" => "business_id_02",
"business_name" => "Business Name 2",
},
]
},
%{
"item_id" => "id_02",
"item_name" => "B",
"description" => "B description",
"item_category" => "some category",
"businesses" => [
${
"business_id" => "business_id_02",
"business_name" => "Business Name 2",
},
${
"business_id" => "business_id_03",
"business_name" => "Business Name 3",
}
]
}
]