Kan ik deze queries combineren als 1
1:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
sql="
SELECT
al.attribute_id, al.attribute_label,
la.lightspeed_id
FROM
lightspeed_types lt
JOIN
attributes_categories ac
ON ac.category_id = lt.category_id
JOIN
attributes_lang al
ON al.attribute_id = ac.attribute_id
AND al.lang_id = '".$lang."'
LEFT JOIN
lightspeed_attributes la
ON la.attribute_id = ac.attribute_id
WHERE
la.lightspeed_id IS NULL ";
SELECT
al.attribute_id, al.attribute_label,
la.lightspeed_id
FROM
lightspeed_types lt
JOIN
attributes_categories ac
ON ac.category_id = lt.category_id
JOIN
attributes_lang al
ON al.attribute_id = ac.attribute_id
AND al.lang_id = '".$lang."'
LEFT JOIN
lightspeed_attributes la
ON la.attribute_id = ac.attribute_id
WHERE
la.lightspeed_id IS NULL ";
en 2:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
sl.spec_id, sl.specification_label,
la.lightspeed_id
FROM
lightspeed_types lt
JOIN
specifications_categories sc
ON sc.category_id = lt.category_id
JOIN
specifications_lang sl
ON sl.spec_id = sc.spec_id
AND sl.lang_id = '".$lang."'
LEFT JOIN
lightspeed_attributes la
ON la.spec_id = sc.spec_id
WHERE
la.lightspeed_id IS NULL
sl.spec_id, sl.specification_label,
la.lightspeed_id
FROM
lightspeed_types lt
JOIN
specifications_categories sc
ON sc.category_id = lt.category_id
JOIN
specifications_lang sl
ON sl.spec_id = sc.spec_id
AND sl.lang_id = '".$lang."'
LEFT JOIN
lightspeed_attributes la
ON la.spec_id = sc.spec_id
WHERE
la.lightspeed_id IS NULL
query1 Union query2 Union query 3 .....
Toevoeging op 21/12/2017 16:35:29:
Jan excuus voor mijn vorige reactie, ik heb me wat verder verdiept in UNION en heb de query nu zo:
Code (php)
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
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
SELECT
'att' as type,
al.attribute_id, al.attribute_label label,
la.lightspeed_id
FROM
lightspeed_types lt
JOIN
attributes_categories ac
ON ac.category_id = lt.category_id
JOIN
attributes_lang al
ON al.attribute_id = ac.attribute_id
AND al.lang_id = 'NL'
LEFT JOIN
lightspeed_attributes la
ON la.attribute_id = ac.attribute_id
WHERE
la.lightspeed_id IS NULL
UNION
SELECT
'spec',
sl.spec_id, sl.specification_label label,
la.lightspeed_id
FROM
lightspeed_types lt
JOIN
specifications_categories sc
ON sc.category_id = lt.category_id
JOIN
specifications_lang sl
ON sl.spec_id = sc.spec_id
AND sl.lang_id = 'NL'
LEFT JOIN
lightspeed_attributes la
ON la.spec_id = sc.spec_id
WHERE
la.lightspeed_id IS NULL
'att' as type,
al.attribute_id, al.attribute_label label,
la.lightspeed_id
FROM
lightspeed_types lt
JOIN
attributes_categories ac
ON ac.category_id = lt.category_id
JOIN
attributes_lang al
ON al.attribute_id = ac.attribute_id
AND al.lang_id = 'NL'
LEFT JOIN
lightspeed_attributes la
ON la.attribute_id = ac.attribute_id
WHERE
la.lightspeed_id IS NULL
UNION
SELECT
'spec',
sl.spec_id, sl.specification_label label,
la.lightspeed_id
FROM
lightspeed_types lt
JOIN
specifications_categories sc
ON sc.category_id = lt.category_id
JOIN
specifications_lang sl
ON sl.spec_id = sc.spec_id
AND sl.lang_id = 'NL'
LEFT JOIN
lightspeed_attributes la
ON la.spec_id = sc.spec_id
WHERE
la.lightspeed_id IS NULL
Zou je eens kunnen beschrijven wat voor recordset je precies uit je database wilt halen en wat de structuur is van de tabellen die hierbij betrokken zijn?
Jan morgen zal ik dat bewerkstelligen, alvast bedankt voor alle moeite
Toevoeging op 22/12/2017 08:35:19:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
mysql> describe lightspeed_types;
+---------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------+------+-----+-------------------+-----------------------------+
| category_id | int(9) | NO | MUL | NULL | |
| lightspeed_id | int(11) | NO | PRI | NULL | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+-----------+------+-----+-------------------+-----------------------------+
+---------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-----------+------+-----+-------------------+-----------------------------+
| category_id | int(9) | NO | MUL | NULL | |
| lightspeed_id | int(11) | NO | PRI | NULL | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+-----------+------+-----+-------------------+-----------------------------+
Toevoeging op 22/12/2017 08:39:32:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
mysql> describe attributes_categories;
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| attribute_id | bigint(20) unsigned | NO | MUL | NULL | |
| category_id | int(11) unsigned | NO | MUL | NULL | |
| sort_order | int(11) | NO | MUL | 0 | |
+--------------+---------------------+------+-----+---------+-------+
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| attribute_id | bigint(20) unsigned | NO | MUL | NULL | |
| category_id | int(11) unsigned | NO | MUL | NULL | |
| sort_order | int(11) | NO | MUL | 0 | |
+--------------+---------------------+------+-----+---------+-------+
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
mysql> describe attributes_lang;
+-----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+----------------+
| attribute_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| lang_id | varchar(2) | NO | PRI | | |
| attribute_label | varchar(250) | NO | | | |
+-----------------+---------------------+------+-----+---------+----------------+
+-----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+----------------+
| attribute_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| lang_id | varchar(2) | NO | PRI | | |
| attribute_label | varchar(250) | NO | | | |
+-----------------+---------------------+------+-----+---------+----------------+
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
mysql> describe lightspeed_attributes;
+-------------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------+------+-----+-------------------+-----------------------------+
| attribute_id | int(9) | YES | | NULL | |
| spec_id | int(9) | YES | | NULL | |
| lightspeed_id | int(11) | NO | PRI | NULL | |
| is_type_attribute | int(1) | NO | | 0 | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+-----------+------+-----+-------------------+-----------------------------+
+-------------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-----------+------+-----+-------------------+-----------------------------+
| attribute_id | int(9) | YES | | NULL | |
| spec_id | int(9) | YES | | NULL | |
| lightspeed_id | int(11) | NO | PRI | NULL | |
| is_type_attribute | int(1) | NO | | 0 | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------------+-----------+------+-----+-------------------+-----------------------------+
Toevoeging op 22/12/2017 08:41:18:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
mysql> describe specifications_categories;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| spec_id | bigint(20) unsigned | NO | MUL | NULL | |
| category_id | int(11) unsigned | NO | MUL | NULL | |
| sort_order | int(11) | NO | MUL | 0 | |
+-------------+---------------------+------+-----+---------+-------+
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| spec_id | bigint(20) unsigned | NO | MUL | NULL | |
| category_id | int(11) unsigned | NO | MUL | NULL | |
| sort_order | int(11) | NO | MUL | 0 | |
+-------------+---------------------+------+-----+---------+-------+
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
mysql> describe specifications_lang;
+---------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+----------------+
| spec_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| lang_id | varchar(2) | NO | PRI | | |
| specification_label | varchar(250) | NO | | | |
+---------------------+---------------------+------+-----+---------+----------------+
+---------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+----------------+
| spec_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| lang_id | varchar(2) | NO | PRI | | |
| specification_label | varchar(250) | NO | | | |
+---------------------+---------------------+------+-----+---------+----------------+
Toevoeging op 22/12/2017 08:43:20:
Dit is het resultaat van de UNION query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
+------+--------------+--------------------+---------------+
| type | attribute_id | label | lightspeed_id |
+------+--------------+--------------------+---------------+
| att | 1 | Opslagcapaciteit | NULL |
| att | 2 | Productsoort | NULL |
| att | 3 | Voor wie | NULL |
| att | 4 | Flacon | NULL |
| att | 5 | Test Geuren | NULL |
| att | 7 | Test 2 | NULL |
| spec | 1 | geluidsniveau (db) | NULL |
| spec | 2 | vermogen (watt) | NULL |
| spec | 3 | Type | NULL |
| spec | 4 | Geurnoot | NULL |
+------+--------------+--------------------+---------------+
| type | attribute_id | label | lightspeed_id |
+------+--------------+--------------------+---------------+
| att | 1 | Opslagcapaciteit | NULL |
| att | 2 | Productsoort | NULL |
| att | 3 | Voor wie | NULL |
| att | 4 | Flacon | NULL |
| att | 5 | Test Geuren | NULL |
| att | 7 | Test 2 | NULL |
| spec | 1 | geluidsniveau (db) | NULL |
| spec | 2 | vermogen (watt) | NULL |
| spec | 3 | Type | NULL |
| spec | 4 | Geurnoot | NULL |
+------+--------------+--------------------+---------------+
Toevoeging op 22/12/2017 08:44:54:
En zo schrijf ik het nu weg:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
function createAttributes($lang){
$attributes = $this->m_lightspeed->getAttributes(TRUE, strtoupper($lang));
dump($attributes);
foreach ($attributes as $value):
$attribute = $this->api()->attributes->create([
"title" => $value->label
]);
if($value->type == 'att'):
$this->m_lightspeed->saveAttributes($value->attribute_id, NULL, $attribute['id']);
else:
$this->m_lightspeed->saveAttributes(NULL, $value->attribute_id, $attribute['id']);
endif;
endforeach;
redirect('dashboard');
}
$attributes = $this->m_lightspeed->getAttributes(TRUE, strtoupper($lang));
dump($attributes);
foreach ($attributes as $value):
$attribute = $this->api()->attributes->create([
"title" => $value->label
]);
if($value->type == 'att'):
$this->m_lightspeed->saveAttributes($value->attribute_id, NULL, $attribute['id']);
else:
$this->m_lightspeed->saveAttributes(NULL, $value->attribute_id, $attribute['id']);
endif;
endforeach;
redirect('dashboard');
}
Jan Koehoorn op 21/12/2017 22:24:59:
Deze opmerking is onjuist, doorgaans gaat het goed als het aantal kolommen per select maar gelijk is zodat de union klopt. De inhoud of kolomnamen hoeven niet hetzelfde te zijn, ook de tabel kan per per select in de union een andere zijn. Of dat zinvol is hangt af van de informatiebehoefte en datamodellering uiteraard. Daarnaast kan er wel een issue zijn met het datatype. Maar het kan werken!Die UNION ziet er eigenaardig uit. De onderdelen van een UNION moeten precies dezelfde kolommen teruggeven, anders zal het niet werken.
Gewijzigd op 23/12/2017 20:50:20 door Aad B