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!