| Autor | Zpráva | ||
|---|---|---|---|
| forum@konicek.net Profil * |
#1 · Zasláno: 4. 2. 2008, 02:05:34
Zdravim vsechny,
mam problem. Na strane backendu (PHP) volam dotaz (ulozenou proceduru). Soucasti dotazu jsou i parametry pro klauzuli LIMIT, ktere slouzi pro strankovani vypisu. Dotaz propojuje 3 tabulky. Vse funguje, az na jednu vec. Ucelem dotazu je vybrat uzivatele a dalsi data vcetne neceho, co se jmenuje oblast dotazu. To se deje, jenze ve vice radcich vysledneho SELECTu. Muj zamer je udelat dotaz, kde na kazdem vracenem radku bude unikatni uzivatel a k nemu (opet ve stejne radce) v textovem retezci vsechny prislusne qa_name(viz alias v dotazu). Premyslel jsem o CURSORu, ale mam pocit, ze to neni uplne spravne reseni kvuli strankovani. Muzete se prosim na to nekdo mrknout? Diky predem. Jakub PS: Nasleduje vypis dotazu:
/***************/
/* user_select */
/***************/
DELIMITER $$
DROP PROCEDURE IF EXISTS `jmeno_databaze`.`user_select` $$
CREATE PROCEDURE `user_select`(_fk_question_area INT(11),
_pager_offset INT(11), _pager_count INT(11))
BEGIN
SET @stmt='SELECT u.pk AS u_pk, u.firstname AS u_firstname, u.surname AS u_surname, u.login AS u_login,
u.datetime_last_login AS u_datetime_last_login, s.qa_pk AS qa_pk, u.email AS u_email,
s.qa_comment AS qa_comment, s.qa_description AS qa_description, s.qa_name AS qa_name,
(SELECT COUNT(*) FROM question q WHERE q.fk_user=u.pk AND fk_question_state=1) AS count_unanswered,
(SELECT COUNT(*) FROM question q WHERE q.fk_user=u.pk AND fk_question_state=2) AS count_answered,
(SELECT COUNT(*) FROM question q WHERE q.fk_user=u.pk AND fk_question_state=3) AS count_published,
(SELECT COUNT(*) FROM question q WHERE q.fk_user=u.pk AND fk_question_state=4) AS count_deleted,
s2.uga_fk_group AS uga_fk_group
FROM user u
LEFT JOIN (
SELECT qa.pk AS qa_pk, qa.comment AS qa_comment, qa.description AS qa_description, qa.name AS qa_name, uqaa.fk_user AS uqaa_fk_user,
uqaa.fk_question_area AS uqaa_fk_question_area
FROM question_area qa
LEFT JOIN user_question_area_application uqaa ON fk_question_area=qa.pk
) s ON u.pk=s.uqaa_fk_user
LEFT JOIN (
SELECT uga.fk_group AS uga_fk_group, uga.fk_user AS uga_fk_user
FROM user_group_application uga
) s2 ON u.pk=s2.uga_fk_user';
IF _fk_question_area <> 0 THEN
SET @stmt:=CONCAT(@stmt, ' WHERE s.uqaa_fk_question_area=', _fk_question_area);
END IF;
SET @stmt:=CONCAT(@stmt, ' ORDER BY u.surname, u.firstname, u.login, s.qa_name');
IF _pager_count <> 0 THEN
SET @stmt:=CONCAT(@stmt, ' LIMIT ', _pager_offset, ', ', _pager_count);
END IF;
PREPARE stmt FROM @stmt;
EXECUTE stmt;
END $$
DELIMITER ;
|
||
| Kajman_ Profil * |
#2 · Zasláno: 4. 2. 2008, 09:11:07
Třeba by se dal použít
... group_concat(qa_name) group by u_pk |
||
| james Profil |
#3 · Zasláno: 4. 2. 2008, 14:57:59
Ok. Diky. Mrknu na to a dam vedet, jak to dopadlo.
|
||
| james Profil |
#4 · Zasláno: 4. 2. 2008, 17:03:11
Tak GROUP_CONCAT v kombinaci s GROUP_BY funguje na 100%. Diky moc, Kajmane. Jsi trida :-).
Jinak moje reseni, ale o hodne pomalejsi, byl databazovy kurzor s temporary tabulkou. Tohle je ale o hodne rychlejsi.
/***************/
/* user_select */
/***************/
DELIMITER $$
DROP PROCEDURE IF EXISTS `jmeno_databaze`.`user_select` $$
CREATE PROCEDURE `user_select`(_fk_question_area INT(11),
_pager_offset INT(11), _pager_count INT(11))
BEGIN
SET @stmt='SELECT u.pk AS u_pk, u.firstname AS u_firstname, u.surname AS u_surname, u.login AS u_login,
u.datetime_last_login AS u_datetime_last_login, s.qa_pk AS qa_pk, u.email AS u_email,
s.qa_comment AS qa_comment, s.qa_description AS qa_description,
GROUP_CONCAT(s.qa_name ORDER BY s.qa_name ASC SEPARATOR \'<br/>\') AS qa_name,
(SELECT COUNT(*) FROM question q WHERE q.fk_user=u.pk AND fk_question_state=1) AS count_unanswered,
(SELECT COUNT(*) FROM question q WHERE q.fk_user=u.pk AND fk_question_state=2) AS count_answered,
(SELECT COUNT(*) FROM question q WHERE q.fk_user=u.pk AND fk_question_state=3) AS count_published,
(SELECT COUNT(*) FROM question q WHERE q.fk_user=u.pk AND fk_question_state=4) AS count_deleted,
s2.uga_fk_group AS uga_fk_group
FROM user u
LEFT JOIN (
SELECT qa.pk AS qa_pk, qa.comment AS qa_comment, qa.description AS qa_description, qa.name AS qa_name, uqaa.fk_user AS uqaa_fk_user,
uqaa.fk_question_area AS uqaa_fk_question_area
FROM question_area qa
LEFT JOIN user_question_area_application uqaa ON fk_question_area=qa.pk
) s ON u.pk=s.uqaa_fk_user
LEFT JOIN (
SELECT uga.fk_group AS uga_fk_group, uga.fk_user AS uga_fk_user
FROM user_group_application uga
) s2 ON u.pk=s2.uga_fk_user';
IF _fk_question_area <> 0 THEN
SET @stmt:=CONCAT(@stmt, ' WHERE s.uqaa_fk_question_area=', _fk_question_area);
END IF;
SET @stmt:=CONCAT(@stmt, ' GROUP BY u.pk');
SET @stmt:=CONCAT(@stmt, ' ORDER BY u.surname, u.firstname, u.login');
IF _pager_count <> 0 THEN
SET @stmt:=CONCAT(@stmt, ' LIMIT ', _pager_offset, ', ', _pager_count);
END IF;
PREPARE stmt FROM @stmt;
EXECUTE stmt;
END $$
|
||
|
Časová prodleva: 16 let
|
|||
0