2

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",
                }
            ]
        }
     ]
hassan
  • 57
  • 7

0 Answers0