GORM Go Fiber - Embed result of a join inside another embedded struct

29 Views Asked by At

So I am learning to build a REST API for a restaurant (so not actually for production). The database schema currently looks like this (other tables are unrelated):

my current database schema which has two tables, orders and order_details.

orders:

abs=# \d orders
                               Table "public.orders"
    Column    |           Type           | Collation | Nullable |      Default
--------------+--------------------------+-----------+----------+-------------------
 id           | uuid                     |           | not null | gen_random_uuid()
 member_id    | uuid                     |           |          |
 created_at   | timestamp with time zone |           |          | now()
 is_completed | boolean                  |           |          | false
 completed_at | timestamp with time zone |           |          |
Indexes:
    "pk_pesanan" PRIMARY KEY, btree (id)
    "pesanan_id_pesanan_key" UNIQUE CONSTRAINT, btree (id)
Foreign-key constraints:
    "fk_orders_member" FOREIGN KEY (member_id) REFERENCES members(id)
Referenced by:
    TABLE "order_details" CONSTRAINT "order_details_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE

order_details:

                  Table "public.order_details"
        Column        |  Type   | Collation | Nullable | Default
----------------------+---------+-----------+----------+---------
 order_id             | uuid    |           | not null |
 menu_id              | uuid    |           | not null |
 menu_option_id       | integer |           | not null |
 menu_option_value_id | integer |           | not null |
 quantity             | integer |           | not null |
Indexes:
    "order_details_pkey" PRIMARY KEY, btree (order_id, menu_id)
Foreign-key constraints:
    "order_details_menu_id_fkey" FOREIGN KEY (menu_id) REFERENCES menu(id)
    "order_details_menu_option_id_fkey" FOREIGN KEY (menu_option_id) REFERENCES menu_available_options(id)
    "order_details_menu_option_value_id_fkey" FOREIGN KEY (menu_option_value_id) REFERENCES menu_option_values(id)
    "order_details_order_id_fkey" FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE

variant_values, where I store the prices:

               Table "public.variant_values"
     Column      |  Type   | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
 menu_id         | uuid    |           | not null |
 option_id       | integer |           | not null |
 option_value_id | integer |           | not null |
 price           | numeric |           |          |
Indexes:
    "variant_values_pkey" PRIMARY KEY, btree (menu_id, option_id, option_value_id)
Foreign-key constraints:
    "variant_values_menu_id_fkey" FOREIGN KEY (menu_id) REFERENCES menu(id)
    "variant_values_menu_option_value_id_fkey" FOREIGN KEY (option_value_id) REFERENCES menu_option_values(id)
    "variant_values_option_id_fkey" FOREIGN KEY (option_id) REFERENCES menu_available_options(id)

There is also another table, menu, where I store the names of the menu items.

There are many orders, and each order can have multiple menus in it, so I decided to store the details of the order in a separate table. With an ORM, I can then send a JSON which contains an array orders with another array order_details in it, which in turn contains the price, quantity etc. as a result of a join below:

select order_details.order_id, order_details.menu_id, menu.name, menu_types.type, variant_values.option_id, variant_values.option_value_id, order_details.quantity, (variant_values.price * order_details.quantity) as total_price
from order_details join
    variant_values on order_details.menu_id=variant_values.menu_id
    and
    order_details.menu_option_value_id=variant_values.option_value_id
join menu on menu.id=order_details.menu_id
join menu_types on menu.type_id=menu_types.id
join menu_option_values on order_details.menu_option_value_id=menu_option_values.id;

The query results in:

               order_id               |               menu_id                |    name    |      type      | option_id | option_value_id | quantity | total_price
--------------------------------------+--------------------------------------+------------+----------------+-----------+-----------------+----------+-------------
 d89bfee4-12ed-4add-9a3f-5d9d8dc88bed | 126ad3b5-322d-4fa8-8af8-d58e6276d544 | Lychee Tea | tea            |         1 |               1 |        2 |       24000
 d89bfee4-12ed-4add-9a3f-5d9d8dc88bed | faa0559a-e432-4ff6-945a-c1cea4eead3a | The Smurf  | Artisan Coffee |         1 |               1 |        1 |       12000

The problem I have is when I try to write the handler in Fiber. I was thinking of embedding the result of the join above into order_details, but I don't even know how. I am expecting to send a JSON that looks something like this:

[
  "order-details": [
    {
      "menu-id" "...",
      "quantity": 2,
      "price": 10000,
    }
  ]
]

There is nothing in the documentation about this. Please help!

0

There are 0 best solutions below