/
attribute.sql
executable file
·153 lines (110 loc) · 3.17 KB
/
attribute.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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
-- Attributes
-- get all attributes
PROCEDURE getAll(
IN language_id INT,
IN product_id INT,
IN attribute_group_id INT,
IN start INT,
IN limit INT,
OUT fetch_all,
OUT fetch_one,
)
BEGIN
-- attribute
SELECT attribute_content.name, attribute_group_content.name as `group`, attribute.*
@IF isset(:product_id)
THEN
,pa.value
END @IF
FROM attribute AS attribute
INNER JOIN attribute_content
ON attribute_content.attribute_id = attribute.attribute_id AND attribute_content.language_id = :language_id
INNER JOIN attribute_group_content
ON attribute_group_content.attribute_group_id = attribute.attribute_group_id AND attribute_group_content.language_id = :language_id
@IF isset(:product_id)
THEN
LEFT JOIN product_attribute pa ON attribute.attribute_id = pa.attribute_id
END @IF
WHERE 1 = 1
@IF isset(:product_id)
THEN
AND pa.product_id = :product_id
END @IF
@IF isset(:attribute_group_id)
THEN
AND attribute.attribute_group_id = :attribute_group_id
END @IF
-- search
@IF isset(:search) AND !empty(:search)
THEN
AND attribute_content.name LIKE CONCAT('%',:search,'%')
END @IF
-- limit
@IF isset(:limit)
THEN
@SQL_LIMIT(:start, :limit)
END @IF;
SELECT count(*) FROM (
@SQL_COUNT(attribute.attribute_id, attribute) -- this takes previous query removes limit and replaces select columns with parameter product_id
) as count;
END
-- get attribute
PROCEDURE get(
IN attribute_id INT,
IN language_id INT,
OUT fetch_row,
)
BEGIN
-- attribute
SELECT *
FROM attribute as _
INNER JOIN attribute_content ac
ON ac.attribute_id = _.attribute_id AND ac.language_id = :language_id
WHERE _.attribute_id = :attribute_id;
END
-- add attribute
PROCEDURE add(
IN attribute ARRAY,
OUT insert_id
)
BEGIN
-- allow only table fields and set defaults for missing values
:attribute_data = @FILTER(:attribute, attribute)
INSERT INTO attribute
( @KEYS(:attribute_data) )
VALUES ( :attribute_data );
:attribute_content = @FILTER(:attribute, attribute_content)
INSERT INTO attribute_content
( @KEYS(:attribute_content), language_id, attribute_id )
VALUES ( :attribute_content, :language_id, @result.attribute);
END
-- edit attribute
CREATE PROCEDURE edit(
IN attribute ARRAY,
IN attribute_id INT,
OUT affected_rows,
OUT affected_rows
)
BEGIN
-- allow only table fields and set defaults for missing values
:attribute_data = @FILTER(:attribute, attribute)
UPDATE attribute
SET @LIST(:attribute_data)
WHERE attribute_id = :attribute_id;
-- allow only table fields and set defaults for missing values
:attribute_content = @FILTER(:attribute, attribute_content)
UPDATE attribute_content
SET @LIST(:attribute_content)
WHERE attribute_id = :attribute_id;
END
-- delete attribute
PROCEDURE delete(
IN attribute_id ARRAY,
OUT affected_rows,
OUT affected_rows,
)
BEGIN
-- attribute
DELETE FROM attribute_content WHERE attribute_id IN (:attribute_id);
DELETE FROM attribute WHERE attribute_id IN (:attribute_id);
END