Ecto - Select an array field from a postgresql database

Question

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

Answer 1

One cannot nest fragments, also I doubt that was the original intent. In products query you currently have

fragment(
  "json_agg( DISTINCT (...)) AS products",
    p.id,
    ...,
    fragment("json_agg((?)) AS categories", pc.c_id)
)

I believe you wanted instead

fragment(
  "json_agg(DISTINCT (...)) AS products, json_agg((?)) AS categories",
    p.id,
    ...,
    pc.c_id
)

If you had provided MCVE, I could probably have it tested, but it should likely work that way or in any case it’s a good starting point to tweak it further.

Answer 2

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.

1. In subquery, I am also returning list of categories that I want to append for each product.

2. Than I join that subquery with Organization which would be Product in your use case through product id

3. 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
            )