Autor Zpráva
forum@konicek.net
Profil *
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 *
Třeba by se dal použít
... group_concat(qa_name) group by u_pk
james
Profil
Ok. Diky. Mrknu na to a dam vedet, jak to dopadlo.
james
Profil
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 $$

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: