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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
|
<?php
namespace app\models\market;
use \Repository;
/** Market_repository
* (thematic-entity repository)
* ---
* SQL entity templates for Market
*
* Every '_base' is a repository of common 'prepare' SQL-queries;
* They usually include holders for attaching/binding data safely;
* These queries are expensive in syntax but fast on run time and
* are used to collect multi-joined amounts ot data.
*
* Organize yous repositories in logical/thematic groups (partitioning).
* This way your repository is managable and uses very few sources.
*
* This class acts like a namespaced repository for geting your code readable
* Avoid pushing very simple queries into repository classes. A record like...
* 'CARS_by:Age' => "SELECT * FROM cars WHERE age = :Age"
* ... won't make your code sexier nor easier to read; it will just increase
* your label's entropy and make trickier to pick your next sensable label;
*
* repository classes include two methods (inherited by the abstract, so
* you do not need to implement something more than just construct
* the $repository array)
* ** pull: for pulling a query by a friendly name
* ** echo: optional method used by some devolopment tools
*
* TODO: development tool using echo is still in progress
*/
class Market_repository extends Repository
{
//// use Repository; // repository trait includes
//// // a method to pull an entity of the gathering
//// // and method to (TODO:) ...
/** repository is an array of Prepare SWL queries;
* each query is attached to the array via a friendly label;
*
* NOTE: (PROPOSAL)
* ---
* about the label:
* 1. keep names short descriptive but not too long
* 2. main entity/entities shall be UPPERCASED
* 3. determinands/adjectives shall be Cappitalized
* 4. words are connected with underscore (_)
* 5. if SQL inludes bind-holderd, label shall be suffixed with '_by:'
* followed by holder names connected with undercores (case-sensitively)
*
* about the data-bind holders (if present):
* 1. all holders are prefixed with ':'
* 2. holder name shall begin with a letter and contain one word.
*
* example:
* 'Active_EMPLOYEES_by:Age_CityID' => "SELECT Em.*, c.*
* FROM employee Em LEFT JOIN city c ON c.id = Em.city_id
* WHERE c.id = :CityID AND Em.age = :Age"
*
* It is recommended to leave a descriptive comment befor each declaration
* and you may also include sql-comments inside the query.
*/
public static $repository = [
// PRODUCTS by: storeID, ProductUrl
// picks all product properties
// + default image + price for certain store
'Active_PRODUCT_by:storeID_ProductUrl' =>
"SELECT p.*,
pc.ID AS ProductCategory,
pc.Hierarchy,
pc.FullFriendlyUrl AS `Path`,
prices.ProductPrice_OriginalPrice AS Price,
prices.ProductPrice_DiscountedPrice AS DiscountPrice,
prices.UnitMeasurePrice_OriginalPrice AS PerUnitPrice,
prices.UnitMeasurePrice_DiscountedPrice AS PerUnitDiscountPrice,
b.Title BrandName
FROM products p
LEFT JOIN products_to_product_categories p2pc ON p.ID = p2pc.SimpleProductID
LEFT JOIN product_categories pc ON p2pc.ProductCategoryID = pc.ID
LEFT JOIN prices ON p.SKU = prices.SKU
LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
LEFT JOIN assets ON assets.ID = p2i.ImageID
LEFT JOIN brands b ON b.ID = p.BrandID
WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1
AND p2i.Order = 1
AND p.IsActive = 1
AND p.Published = 1
AND prices.PriceListID = :storeID
AND p.FriendlyUrl = :ProductUrl"
,
// PRODUCTS by: storeID, ProductID
// picks all product properties
// + default image + price for certain store
'Active_PRODUCT_by:storeID_ProductID' =>
"SELECT p.*,
pc.ID AS ProductCategory,
pc.Hierarchy,
pc.FullFriendlyUrl AS `Path`,
prices.ProductPrice_OriginalPrice AS Price,
prices.ProductPrice_DiscountedPrice AS DiscountPrice,
prices.UnitMeasurePrice_OriginalPrice AS PerUnitPrice,
prices.UnitMeasurePrice_DiscountedPrice AS PerUnitDiscountPrice,
b.Title BrandName
FROM products p
LEFT JOIN products_to_product_categories p2pc ON p.ID = p2pc.SimpleProductID
LEFT JOIN product_categories pc ON p2pc.ProductCategoryID = pc.ID
LEFT JOIN prices ON p.SKU = prices.SKU
LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
LEFT JOIN assets ON assets.ID = p2i.ImageID
LEFT JOIN brands b ON b.ID = p.BrandID
WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1
AND p2i.Order = 1
AND p.IsActive = 1
AND p.Published = 1
AND prices.PriceListID = :storeID
AND p.ID = :ProductID"
,
// CATEGORY-PRODUCTS by: storeID, likeHierarchy
// traverses all products from category and subcategories
// picke price and default image
'CATEGORY-PRODUCTS_by:storeID_likeHierarchy' =>
"SELECT p.*,
assets.Url AS ImageUrl,
pc.FullFriendlyUrl AS `Path`,
prices.ProductPrice_OriginalPrice AS Price,
prices.ProductPrice_DiscountedPrice AS DiscountPrice,
prices.UnitMeasurePrice_OriginalPrice AS PerUnitPrice,
prices.UnitMeasurePrice_DiscountedPrice AS PerUnitDiscountPrice
FROM products p
LEFT JOIN prices ON p.SKU = prices.SKU
LEFT JOIN products_to_product_categories p2pc ON p2pc.SimpleProductID = p.ID
LEFT JOIN product_categories pc ON pc.ID = p2pc.ProductCategoryID
LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
LEFT JOIN assets ON assets.ID = p2i.ImageID
WHERE p2pc.ProductCategoryID IN (
SELECT `ID` FROM product_categories
WHERE Hierarchy LIKE :likeHierarchy
)
AND p2i.Order = 1
AND p.IsActive = 1
AND p.Published = 1
AND prices.PriceListID = :storeID"
,
// Count Products
// of all last-lever Product categories
// by StoreID
// ---
// used on creating product_categories tree
// ProductCategories_model::tree()
'Count_Last_Level_PRODUCTS_by:storeID' =>
"SELECT pc.ID, COUNT(p.ID) as `Counter`
FROM product_categories pc
LEFT JOIN products_to_product_categories p2pc ON pc.ID = p2pc.ProductCategoryID
LEFT JOIN products p ON p2pc.SimpleProductID = p.ID
LEFT JOIN prices ON p.SKU = prices.SKU
LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
LEFT JOIN assets ON assets.ID = p2i.ImageID
WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1
AND p2i.Order = 1
AND pc.Level = 2
AND p.IsActive = 1
AND p.Published = 1
AND prices.PriceListID = :storeID
GROUP BY pc.ID"
,
// PRODUCT by: ProductID
// Join images array (json string)
// Join prices for all stores (json string)
'Active_PRODUCT_complete_by:ProductID' =>
"SELECT p.*, pc.ID AS ProductCategory,
pc.Hierarchy, pc.FullFriendlyUrl AS `Path`,
( -- get all store prices as Json
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'Store', prices.PriceListID,
'Price', prices.ProductPrice_OriginalPrice,
'DiscountPrice', prices.ProductPrice_DiscountedPrice,
'PerUnitPrice', prices.UnitMeasurePrice_OriginalPrice,
'PerUnitDiscountPrice', prices.UnitMeasurePrice_DiscountedPrice
))
FROM prices
WHERE prices.SKU = p.SKU
) as pricesJson,
( -- get all images as Json
SELECT JSON_ARRAYAGG(JSON_OBJECT(
'Order', p2i.Order,
'Url', assets.Url,
'Title', assets.Title,
'Description', assets.Description
))
FROM products_to_images p2i
LEFT JOIN assets ON assets.ID = p2i.ImageID
WHERE p2i.SimpleProductID = p.ID
) AS imagesJson,
b.Title BrandName
FROM products p
LEFT JOIN products_to_product_categories p2pc ON p.ID = p2pc.SimpleProductID
LEFT JOIN product_categories pc ON p2pc.ProductCategoryID = pc.ID
LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
LEFT JOIN assets ON assets.ID = p2i.ImageID
LEFT JOIN brands b ON b.ID = p.BrandID
WHERE pc.IsActive = 1 AND pc.IsCurrentlyActive = 1
AND p2i.Order = 1
AND p.IsActive = 1
AND p.Published = 1
AND p.ID = :ProductID"
,
// Product images by: ProductID
// (sorted by 'order' field)
'PRODUCT_IMAGES_by:productID' =>
"SELECT a.Url, a.Title, a.Description
FROM products p
LEFT JOIN products_to_images p2i ON p2i.SimpleProductID = p.ID
LEFT JOIN assets a ON a.ID = p2i.ImageID
WHERE p.IsActive = 1
AND p.Published = 1
AND p.ID = :productID
ORDER BY p2i.Order"
];
/* no need to impement anything else;
* you can overide if needed the default methods:
* + public static function pull($entity){ }
* + public static function echo($content =false){ }
*/
}
|
