-
Notifications
You must be signed in to change notification settings - Fork 0
/
view.sql
executable file
·130 lines (120 loc) · 4.09 KB
/
view.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
-- Favorite View
CREATE OR REPLACE VIEW favoriteView AS
SELECT favorite.*, items.* , users.user_id FROM favorite
INNER JOIN users ON users.user_id = favorite.favorite_user_id
INNER JOIN items ON items.items_id = favorite.favorite_item_id;
-- Orders View
CREATE OR REPLACE VIEW ordersView AS
SELECT orders.* ,address.* FROM orders
LEFT JOIN address ON address.address_id = orders.order_address_id;
-- Orders Details View
CREATE OR REPLACE VIEW ordersDetailsView AS
SELECT
cart.cart_order_id,
(items.items_price - (items.items_price * items.items_discount / 100)) AS item_price_at_purchase, -- Discounted price
SUM(items.items_price - items.items_price * items.items_discount / 100) AS items_discount_price,
cart.cart_id,
cart.cart_user_id,
cart.cart_item_id,
cart.cart_item_count,
items.items_id,
items.items_name,
items.items_name_ar,
items.items_price,
items.items_discount,
items.items_active,
items.items_count,
items.items_image,
items.items_description,
items.items_description_ar,
items.items_datetime,
items.items_category,
ordersView.order_address_id,
ordersView.order_user_id,
ordersView.order_type,
ordersView.order_delivery_price,
ordersView.order_coupon_id,
ordersView.order_date_time,
ordersView.order_price,
ordersView.order_totalprice,
ordersView.order_payment_type,
ordersView.order_status,
ordersView.order_rating,
ordersView.order_notating,
ordersView.order_delivery,
ordersView.address_city,
ordersView.address_name,
ordersView.address_street,
ordersView.address_lat,
ordersView.address_long
FROM
cart
INNER JOIN
items ON items.items_id = cart.cart_item_id
INNER JOIN
ordersView ON ordersView.order_id = cart.cart_order_id
WHERE
cart.cart_order_id != 0
GROUP BY
cart.cart_order_id, cart.cart_item_id; -- Group by order_id AND cart_item_id
-- CREATE OR REPLACE VIEW ordersDetailsView AS
-- SELECT
-- cart.cart_order_id,
-- SUM(items.items_price - items.items_price * items.items_discount / 100) AS items_discount_price,
-- cart.cart_id,
-- cart.cart_user_id,
-- cart.cart_item_id,
-- cart.cart_item_count,
-- items.items_id,
-- items.items_name,
-- items.items_name_ar,
-- items.items_price,
-- items.items_discount,
-- items.items_active,
-- items.items_count,
-- items.items_image,
-- items.items_description,
-- items.items_description_ar,
-- items.items_datetime,
-- items.items_category,
-- -- Select the remaining columns directly from ordersView
-- ordersView.order_address_id,
-- ordersView.order_user_id,
-- ordersView.order_type,
-- ordersView.order_delivery_price,
-- ordersView.order_coupon_id,
-- ordersView.order_date_time,
-- ordersView.order_price,
-- ordersView.order_totalprice,
-- ordersView.order_payment_type,
-- ordersView.order_status,
-- ordersView.order_rating,
-- ordersView.order_notating,
-- ordersView.order_delivery,
-- ordersView.address_city,
-- ordersView.address_name,
-- ordersView.address_street,
-- ordersView.address_lat,
-- ordersView.address_long
-- FROM
-- cart
-- INNER JOIN
-- items ON items.items_id = cart.cart_item_id
-- INNER JOIN
-- ordersView ON ordersView.order_id = cart.cart_order_id
-- WHERE
-- cart.cart_order_id != 0
-- GROUP BY
-- cart.cart_order_id;
-- CREATE OR REPLACE VIEW ordersDetailsView AS
-- SELECT SUM(items.items_price - items.items_price * items.items_discount / 100) as items_discount_price ,cart.* ,items.* , ordersView.* FROM cart
-- INNER JOIN items ON items.items_id = cart.cart_item_id
-- WHERE cart.cart_order_id != 0
-- -- GROUP BY ... ,cart.cart_order_id So every order is separated from each other
-- GROUP BY cart.cart_item_id ,cart.cart_user_id ,cart.cart_order_id;
-- Top Selling Items View
CREATE OR REPLACE VIEW itemsTopSellingView AS
SELECT COUNT(cart.cart_id) as countTimes , cart.* ,items.* FROM cart
INNER JOIN items ON items.items_id = cart.cart_item_id
WHERE cart.cart_order_id != 0
GROUP BY cart.cart_item_id