I have this existing database query which takes input search params (an array of shop_ids that contain the product, and an array categories which makes sure to only return products that have those categories) and returns the correct products and their shops:
def create_unique_shop_query_no_keyword(categories, shop_ids) do
products_shops_categories =
from p in Product,
join: ps in ProductShop,
on: p.id == ps.p_id,
join: s in Shop,
on: s.id == ps.s_id,
join: pc in ProductCategory,
on: p.id == pc.p_id,
join: c in Subcategory,
on: c.id == pc.c_id,
distinct: s.id,
where: c.id in ^categories,
where: s.id in ^shop_ids,
group_by: [s.id, s.name],
select: %{
products:
fragment(
"json_agg( DISTINCT (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS products",
p.id,
p.name,
p.brand,
p.description,
p.image,
p.rating,
p.number_of_votes,
ps.not_in_shop_count,
ps.is_in_shop_count,
ps.price,
p.not_vegan_count,
p.vegan_count
),
shop:
fragment(
"json_agg( DISTINCT (?, ?, ST_X(?), ST_Y(?), ?, ?, ?, ?, ?)) AS shop",
s.id,
s.name,
s.point,
s.point,
s.place_id,
s.street,
s.suburb,
s.city,
s.street_number
)
}
end
The productCategory table columns are: id, p_id, c_id which represents each product p_id having 1 to many categories c_id. I want to add one more field to the results: an array of categories the product has. I have tried it below but it says there is a syntax error. I will paste the error at the bottom of the question. What am I doing wrong?
def create_unique_shop_query_no_keyword(categories, shop_ids) do
products_shops_categories =
from p in Product,
join: ps in ProductShop,
on: p.id == ps.p_id,
join: s in Shop,
on: s.id == ps.s_id,
join: pc in ProductCategory,
on: p.id == pc.p_id,
join: c in Subcategory,
on: c.id == pc.c_id,
distinct: s.id,
where: c.id in ^categories,
where: s.id in ^shop_ids,
group_by: [s.id, s.name],
select: %{
products:
fragment(
"json_agg( DISTINCT (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS products",
p.id,
p.name,
p.brand,
p.description,
p.image,
p.rating,
p.number_of_votes,
ps.not_in_shop_count,
ps.is_in_shop_count,
ps.price,
p.not_vegan_count,
p.vegan_count,
fragment("json_agg((?)) AS categories", pc.c_id)
),
shop:
fragment(
"json_agg( DISTINCT (?, ?, ST_X(?), ST_Y(?), ?, ?, ?, ?, ?)) AS shop",
s.id,
s.name,
s.point,
s.point,
s.place_id,
s.street,
s.suburb,
s.city,
s.street_number
)
}
end
error:
[error] #PID<0.516.0> running VepoWeb.Endpoint (connection
#PID<0.515.0>, stream id 1) terminated Server: 192.168.0.105:4000 (http) Request: GET /products?categories[]=1&categories[]=2&categories[]=3&keyword=null&latitude=-37.6739483&longitude=176.1662587&distanceFromLocationValue=10&distanceFromLocationUnit=%22kilometers%22
** (exit) an exception was raised:
** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "AS"
query: SELECT DISTINCT ON (s2."id") json_agg( DISTINCT (p0."id", p0."name", p0."brand", p0."description", p0."image", p0."rating", p0."number_of_votes", p1."not_in_shop_count", p1."is_in_shop_count", p1."price", p0."not_vegan_count", p0."vegan_count", json_agg((p3."c_id")) AS categories)) AS products, json_agg( DISTINCT (s2."id", s2."name", ST_X(s2."point"), ST_Y(s2."point"), s2."place_id", s2."street", s2."suburb", s2."city", s2."street_number")) AS shop FROM "products" AS p0 INNER JOIN "product_shops" AS p1 ON p0."id" = p1."p_id" INNER JOIN "shops" AS s2 ON s2."id" = p1."s_id" INNER JOIN "product_categories" AS p3 ON p0."id" = p3."p_id" INNER JOIN "subcategories" AS s4 ON s4."id" = p3."c_id" WHERE (s4."id" = ANY($1)) AND (s2."id" = ANY($2)) GROUP BY s2."id", s2."name"
(ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:593: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql 3.4.5) lib/ecto/adapters/sql.ex:526: Ecto.Adapters.SQL.execute/5
(ecto 3.4.5) lib/ecto/repo/queryable.ex:192: Ecto.Repo.Queryable.execute/4
(ecto 3.4.5) lib/ecto/repo/queryable.ex:17: Ecto.Repo.Queryable.all/3
(vepo 0.1.0) lib/vepo_web/services/product_service.ex:392: VepoWeb.ProductService.get_products_from_search/1
(vepo 0.1.0) lib/vepo_web/controllers/product_controller.ex:1: VepoWeb.ProductController.action/2
(vepo 0.1.0) lib/vepo_web/controllers/product_controller.ex:1: VepoWeb.ProductController.phoenix_controller_pipeline/2
(phoenix 1.5.4) lib/phoenix/router.ex:352: Phoenix.Router.__call__/2
(vepo 0.1.0) lib/vepo_web/endpoint.ex:1: VepoWeb.Endpoint.plug_builder_call/2
(vepo 0.1.0) lib/plug/debugger.ex:132: VepoWeb.Endpoint."call (overridable 3)"/2
(vepo 0.1.0) lib/vepo_web/endpoint.ex:1: VepoWeb.Endpoint.call/2
(phoenix 1.5.4) lib/phoenix/endpoint/cowboy2_handler.ex:65: Phoenix.Endpoint.Cowboy2Handler.init/4
(cowboy 2.8.0) /Users/benjaminfarquhar/Api/vepo/deps/cowboy/src/cowboy_handler.erl:37: :cowboy_handler.execute/2
(cowboy 2.8.0) /Users/benjaminfarquhar/Api/vepo/deps/cowboy/src/cowboy_stream_h.erl:300: :cowboy_stream_h.execute/3
(cowboy 2.8.0) /Users/benjaminfarquhar/Api/vepo/deps/cowboy/src/cowboy_stream_h.erl:291: :cowboy_stream_h.request_process/3
(stdlib 3.13) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Attempt of Aleksei Matiushkin's answer:
def create_unique_shop_query_no_keyword(categories, shop_ids) do
products_shops_categories =
from p in Product,
join: ps in ProductShop,
on: p.id == ps.p_id,
join: s in Shop,
on: s.id == ps.s_id,
join: pc in ProductCategory,
on: p.id == pc.p_id,
join: c in Subcategory,
on: c.id == pc.c_id,
distinct: s.id,
where: c.id in ^categories,
where: s.id in ^shop_ids,
group_by: [s.id, s.name],
select: %{
products:
fragment(
"json_agg( DISTINCT (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS products, json_agg((?)) AS categories",
p.id,
p.name,
p.brand,
p.description,
p.image,
p.rating,
p.number_of_votes,
ps.not_in_shop_count,
ps.is_in_shop_count,
ps.price,
p.not_vegan_count,
p.vegan_count,
pc.c_id
),
shop:
fragment(
"json_agg( DISTINCT (?, ?, ST_X(?), ST_Y(?), ?, ?, ?, ?, ?)) AS shop",
s.id,
s.name,
s.point,
s.point,
s.place_id,
s.street,
s.suburb,
s.city,
s.street_number
)
}
end
This is the data in my productCategories table where P is p_id and c is c_id:
ID P C
1 1 5
2 2 3
3 2 4
4 2 5
5 3 1
6 3 2
7 3 3
I would like to provide you with similar use cases. Here i start by making a subquery that will return me parent_uuid which will be p.id in your use case.
In subquery, I am also returning list of categories that I want to append for each product.
Than I join that subquery with Organization which would be Product in your use case through product id
Than I can provide categories in fragment itself.
def product_with_categories do
from o in Organization,
inner_join: po in Organization, on: o.platform_uuid == po.parent_uuid,
group_by: [po.parent_uuid],
select: %{
product_uuid: po.parent_uuid,
categories: fragment("SELECT json_agg(DISTINCT(?, ?)) as categories", o.platform_uuid, o.name)
}
end
def query do
from pos in subquery(product_with_categories),## Product Category Subquery
join: o in Organization, ## Join with product with uuid returned in subquery
on: o.parent_uuid == pos.product_uuid,
join: c in Course, ## This is join with shop
on: c.organization_uuid == o.platform_uuid,
group_by: [o.platform_uuid],
select: %{
products: fragment("SELECT json_agg(DISTINCT(?, ?, cast(? as text)::jsonb))",
o.platform_uuid, o.name, pos.categories),
shop_of_courses: fragment("json_agg(DISTINCT(?, ?))", c.platform_uuid, c.name)
}
end
Result has same structure that you want
products: [
%{
"f1" => "e73e8953-7cdd-4a01-a5da-46e0c0ee6b55",
"f2" => "Another Product",
"f3" => [
%{
"f1" => "7971ca8b-98b4-4c41-aad7-486bf761d8b6",
"f2" => "Another category"
}
]
}
],
shop_of_courses: [
%{"f1" => "f415b90f-8aa9-4f89-b2c0-2544fe7792eb", "f2" => "3-D Design SHOP"},
%{
"f1" => "fcd4df71-2730-4b06-bf2e-066308ab2d06",
"f2" => "2-D Design and Color SHOP"
}
]
}
]
As it's hard to test without running env I am providing pseudo code also along with it that would make it easier for you to implement on your side.
PSEUDOCODE
def product_with_categories do
from p in Product,
join: pc in ProductCategory,
on: pc.p_id == p.id,
group_by: [p.id],
select: %{
product_id: p.id,
categories: fragment("SELECT json_agg(DISTINCT(?, ?)) as categories", pc.id, pc.name) ## if pc has a name or add as per the requirement
}
end
## Than do the join with this subquery in your existing query.
join: pcategories in subquery(product_with_categories),
on: pcategories.id == p.id
### and than in select query
products:
fragment(
"json_agg( DISTINCT (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, cast(? as text)::jsonb)) AS products",
p.id,
p.name,
p.brand,
p.description,
p.image,
p.rating,
p.number_of_votes,
ps.not_in_shop_count,
ps.is_in_shop_count,
ps.price,
p.not_vegan_count,
p.vegan_count,
pcategories.categories
)