Несколько запросов в $db->setQuery. Как ? - страница 2 - Модификации расширений для Joomla

CREATE TABLE records(
  id INTEGER (11) DEFAULT NULL,
  article VARCHAR (20) DEFAULT NULL
)
CREATE TABLE `values`(
  record_id INT (11) DEFAULT NULL,
  value_type INT (11) DEFAULT NULL,
  value VARCHAR (20) DEFAULT NULL
)

INSERT INTO records VALUES (0, 'about sun');
INSERT INTO records VALUES (1, 'about water');
INSERT INTO records VALUES (2, 'about earth')

INSERT INTO `values` VALUES (0, 1, 'gray');
INSERT INTO `values` VALUES (1, 1, 'red');
INSERT INTO `values` VALUES (2, 1, 'green');
INSERT INTO `values` VALUES (0, 2, 'thin');
INSERT INTO `values` VALUES (1, 2, 'toll');
INSERT INTO `values` VALUES (2, 2, 'fat');

SELECT
  r.article, max(v.value) as color, max(v2.value) as `geometry`
FROM
  records r
  LEFT JOIN `values` v ON (v.record_id = r.id) && (v.value_type = 1)
  LEFT JOIN `values` v2 ON (v2.record_id = r.id) && (v2.value_type = 2)
GROUP BY r.article;

USE testground;

CREATE TEMPORARY TABLE tmp_values(
  id INT (11) NOT NULL,
  `value` VARCHAR (20)
);
INSERT
INTO tmp_values (id, `value`)
SELECT
  record_id, `value`
FROM
  `values`
WHERE
  value_type = 1;

CREATE TEMPORARY TABLE tmp_values2(
  id INT (11) NOT NULL,
  `value` VARCHAR (20)
);
INSERT
INTO tmp_values2 (id, `value`)
SELECT
  record_id, `value`
FROM
  `values`
WHERE
  value_type = 2;

SELECT
  r.id, r.article, v.`value`, v2.`value`
FROM
  records r
  LEFT JOIN tmp_values v ON v.id = r.id
  LEFT JOIN tmp_values2 v2 ON v2.id = r.id

use testground
SELECT
  r.id, r.article, MAX(v.value) AS color, MAX(v2.value) AS `geometry`
FROM
  records r
  LEFT JOIN `values` v ON (v.record_id = r.id) && (v.value_type = 1)
  LEFT JOIN `values` v2 ON (v2.record_id = r.id) && (v2.value_type = 2)
GROUP BY
  r.id, r.article;

INSERT INTO records VALUES (0, 'about sun');
INSERT INTO records VALUES (1, 'about water');
INSERT INTO records VALUES (2, 'about earth');
INSERT INTO records VALUES (3, 'test');
INSERT INTO records VALUES (4, 'test');
INSERT INTO records VALUES (5, 'test');
INSERT INTO records VALUES (6, 'test');
INSERT INTO records VALUES (7, 'test');
INSERT INTO records VALUES (8, 'test');
INSERT INTO records VALUES (9, 'test');
INSERT INTO records VALUES (10, 'test');
INSERT INTO records VALUES (11, 'about water');
INSERT INTO records VALUES (12, 'about earth');
INSERT INTO records VALUES (13, 'test');
INSERT INTO records VALUES (14, 'test');
INSERT INTO records VALUES (15, 'test');
INSERT INTO records VALUES (16, 'test');
INSERT INTO records VALUES (17, 'test');
INSERT INTO records VALUES (18, 'test');
INSERT INTO records VALUES (19, 'test');
INSERT INTO records VALUES (21, 'about water');
INSERT INTO records VALUES (22, 'about earth');
INSERT INTO records VALUES (23, 'test');
INSERT INTO records VALUES (24, 'test');
INSERT INTO records VALUES (25, 'test');
INSERT INTO records VALUES (26, 'test');
INSERT INTO records VALUES (27, 'test');
INSERT INTO records VALUES (28, 'test');
INSERT INTO records VALUES (29, 'test');
INSERT INTO records VALUES (20, 'about sun');
INSERT INTO records VALUES (31, 'about water');
INSERT INTO records VALUES (32, 'about earth');
INSERT INTO records VALUES (33, 'test');
INSERT INTO records VALUES (34, 'test');
INSERT INTO records VALUES (35, 'test');
INSERT INTO records VALUES (36, 'test');
INSERT INTO records VALUES (37, 'test');
INSERT INTO records VALUES (38, 'test');
INSERT INTO records VALUES (39, 'test');
INSERT INTO records VALUES (30, 'about sun');

INSERT INTO `values` VALUES (1, 1, 'red');
INSERT INTO `values` VALUES (2, 1, 'green');
INSERT INTO `values` VALUES (0, 1, 'thin');
INSERT INTO `values` VALUES (1, 2, 'toll');
INSERT INTO `values` VALUES (39, 2, 'fhgh');
INSERT INTO `values` VALUES (38, 2, 'casdfrer,kjj');
INSERT INTO `values` VALUES (28, 2, 'ddd');
INSERT INTO `values` VALUES (20, 2, 'xxxxx');
INSERT INTO `values` VALUES (19, 2, 'zzzzzz');
INSERT INTO `values` VALUES (0, 2, 'red');
INSERT INTO `values` VALUES (29, 2, 'dcsdfc');
INSERT INTO `values` VALUES (4, 2, 'esde');
INSERT INTO `values` VALUES (5, 1, 'fdf');
INSERT INTO `values` VALUES (5, 2, 'fdvxcz');
INSERT INTO `values` VALUES (19, 1, 'dsf');
INSERT INTO `values` VALUES (6, 2, 'dsadf.skamd');
INSERT INTO `values` VALUES (7, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (17, 2, 'xcdsw');
INSERT INTO `values` VALUES (8, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (8, 2, 'dsadf.skamd');
INSERT INTO `values` VALUES (10, 2, 'red');
INSERT INTO `values` VALUES (11, 1, 'green');
INSERT INTO `values` VALUES (11, 2, 'thin');
INSERT INTO `values` VALUES (12, 1, 'toll');
INSERT INTO `values` VALUES (12, 2, 'fat');
INSERT INTO `values` VALUES (13, 1, 'c');
INSERT INTO `values` VALUES (13, 2, 'x');
INSERT INTO `values` VALUES (14, 1, 'cvxc');
INSERT INTO `values` VALUES (14, 2, 'esde');
INSERT INTO `values` VALUES (15, 1, 'fdf');
INSERT INTO `values` VALUES (15, 2, 'fdvxcz');
INSERT INTO `values` VALUES (16, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (16, 2, 'dsadf.skamd');
INSERT INTO `values` VALUES (17, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (10, 1, 's');
INSERT INTO `values` VALUES (18, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (18, 2, 'dsadf.skamd');
INSERT INTO `values` VALUES (20, 1, 'red');
INSERT INTO `values` VALUES (21, 1, 'green');
INSERT INTO `values` VALUES (21, 2, 'thin');
INSERT INTO `values` VALUES (22, 1, 'toll');
INSERT INTO `values` VALUES (22, 2, 'fat');
INSERT INTO `values` VALUES (23, 1, 'c');
INSERT INTO `values` VALUES (23, 2, 'x');
INSERT INTO `values` VALUES (24, 1, 'cvxc');
INSERT INTO `values` VALUES (24, 2, 'esde');
INSERT INTO `values` VALUES (25, 1, 'fdf');
INSERT INTO `values` VALUES (25, 2, 'fdvxcz');
INSERT INTO `values` VALUES (26, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (26, 2, 'dsadf.skamd');
INSERT INTO `values` VALUES (27, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (27, 2, 'dsadf.skamd');
INSERT INTO `values` VALUES (28, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (29, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (30, 1, 'gray');
INSERT INTO `values` VALUES (30, 2, 'red');
INSERT INTO `values` VALUES (31, 1, 'green');
INSERT INTO `values` VALUES (31, 2, 'thin');
INSERT INTO `values` VALUES (32, 1, 'toll');
INSERT INTO `values` VALUES (32, 2, 'fat');
INSERT INTO `values` VALUES (33, 1, 'c');
INSERT INTO `values` VALUES (33, 2, 'x');
INSERT INTO `values` VALUES (34, 1, 'cvxc');
INSERT INTO `values` VALUES (34, 2, 'esde');
INSERT INTO `values` VALUES (35, 1, 'fdf');
INSERT INTO `values` VALUES (35, 2, 'fdvxcz');
INSERT INTO `values` VALUES (36, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (36, 2, 'dsadf.skamd');
INSERT INTO `values` VALUES (37, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (37, 2, 'dsadf.skamd');
INSERT INTO `values` VALUES (38, 1, 'dsadf.skamd');
INSERT INTO `values` VALUES (39, 1, 'dsadf.skamd');