[MySQL] Subquery op basis van velden in hoofdquery

[gvanh]

Hallo,

Om met een voorbeeld te beginnen, dit mag niet van MySQL:

SQL:

SELECT O1.*
 FROM
    objects AS O1,
    (
       SELECT O2.*
       FROM objects AS O2
       WHERE O2.parentID = O1.objectID
    ) AS children

De foutmelding die je daarmee krijgt is "Unknown table 'O1' in where clause".

In gegeven voorbeeld kan de subquery uiteraard vervangen worden door een eenvoudige JOIN, maar de query staat hier slechts ter illustratie, in het uiteindelijk probleem kan ik niet zonder subquery.

De vraag is nu of het mogelijk is om op een andere manier de subquery toch afhankelijk te maken van velden in de buitenste (hoofd)query. Wellicht met variabelen of op een andere manier. Ik ben inmiddels zo ongeveer 3 jaar bezig met deze query, en ik heb het nog steeds niet voor elkaar gekregen. Net ook het boek van Celko gelezen over boomstructuren, maar ook daar mijn antwoord niet gevonden.

Alvast dank voor het meedenken!

[kokx]

Misschien is het een idee om de query waar je zelf mee bezig bent even hier te posten, en ook de exacte bedoeling van die query. Dan zouden wij makkelijker mee kunnen denken over die query, en eventueel kijken of er een andere oplossing mogelijk is.

[Sh0ckTr00per]

Ik heb iets vergelijkbaars opgelost in mysql door temporary tables te gebruiken, maar zoals al gezegd is het lastig bepalen op basis van voorbeeldcode wat een mogelijke oplossing is.

[gvanh]

Het is vrij lastig uit te leggen, maar als jullie mee willen denken, dan heel graag natuurlijk.

Het gaat hoofdzakelijk om twee tabellen:

Tabel 1 voor opslag van alle objecten in de database
[code=sql]
CREATE TABLE `cms_objects` (
`cms_objectID` int(11) NOT NULL auto_increment,
`type` varchar(10) NOT NULL default '',
`parentID` int(11) NOT NULL default '0',
`lft` int(11) NOT NULL default '0',
`rgt` int(11) NOT NULL default '0',
`depth` int(11) NOT NULL default '0',
PRIMARY KEY (`cms_objectID`),
UNIQUE KEY `lft` (`lft`),
UNIQUE KEY `rgt` (`rgt`),
KEY `parentID` (`parentID`),
KEY `type` (`type`),
KEY `depth` (`depth`)
) TYPE=MyISAM
[/code=sql]

Tabel 2 voor rechten op de tabel, afhankelijk van de gebruiker of diens rol

SQL:

CREATE TABLE `cms_objects2operations` (
  `cms_objectID` int(11) NOT NULL default '0',
  `auth_roleID` int(11) NOT NULL default '0',
  `auth_userID` int(11) NOT NULL default '0',
  `auth_operationID` int(11) NOT NULL default '0',
  `value` int(2) NOT NULL default '0',
  PRIMARY KEY  (`cms_objectID`,`auth_roleID`,`auth_userID`,`auth_operationID`),
  KEY `value` (`value`),
  KEY `auth_operationID` (`auth_operationID`)
) TYPE=MyISAM;

Hoofddoel is om - in één query - objecten uit de cms_objects tabel te kunnen halen die aan bepaalde voorwaarden voldoen EN waarvoor de gebruiker gerechtigd is.

- De rechten kunnen zijn ingesteld op het object zelf of op één van de nested-set-parents van het object.
- Wanneer er op verschillende parents rechten zijn ingesteld voor huidige gebruiker of één van diens rollen, dan moeten de rechten op de "diepste" nested node worden aangehouden. Voorts is het zo dat wanneer een gebruiker rechten toegekend/ontzegd heeft gekregen op zijn auth_userID zelf, dat die dan altijd prevaleren boven rechten op diens rol(len).
- Wanneer er geen rechten op de gebruiker zelf zijn, worden de rechten op één van zijn rollen aangehouden.
- Wanneer rol 1 van gebruiker rechten ontzegd, terwijl rol 2 van gebruiker rechten toekend, dan prevaleert het toekennen van de rechten, óók wanneer deze rechten op een "minder diepe" parent zijn ingesteld.

Ik heb nu een query die werkt ... maar die is te traag om toegepast te kunnen worden in een LIVE omgeving:

SQL:

SELECT
    O.*
FROM
    cms_objects AS O
WHERE
    O.lft > 1 AND O.lft < 88578
    AND O.type = 'message'
    AND
        (
        SELECT
            o2o.value
        FROM
            cms_objects2operations AS o2o, cms_objects as parents
        WHERE
            o2o.cms_objectID = parents.cms_objectID
            AND parents.lft <= O.lft
            AND parents.rgt >= O.lft
            AND o2o.auth_operationID = 1
            AND (
                o2o.auth_userID = 11
                OR o2o.auth_roleID IN (2, 3)
            )
            
            AND CONCAT_WS('#', o2o.auth_userID, o2o.auth_roleID, parents.depth) IN
            (
                SELECT
                    CONCAT_WS('#',
                        o2o2.auth_userID, o2o2.auth_roleID, max(parents2.depth)) AS str
                FROM
                    cms_objects as parents2, cms_objects2operations AS o2o2
                WHERE
                    o2o2.cms_objectID = parents2.cms_objectID
                    AND parents2.lft <= O.lft
                    AND parents2.rgt >= O.lft
                    AND o2o2.auth_operationID = 1
                    AND (
                        o2o2.auth_userID = 11
                        OR o2o2.auth_roleID IN (2,3)
                    )
                GROUP BY auth_roleID, auth_userID
            )
            ORDER BY
            o2o.auth_userID DESC, o2o.value DESC
            LIMIT 0, 1
        ) = 1
ORDER BY O.lft
LIMIT 0, 50

Nog even de explain van de query erbij, voor zover dat enigszins te ontcijferen valt:

SQL:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     O   ref     lft,type    type    10  const   7204    Using where; Using filesort
2   DEPENDENT SUBQUERY  o2o     ref     PRIMARY,auth_operationID    auth_operationID    4   const   18  Using where; Using filesort
2   DEPENDENT SUBQUERY  parents     eq_ref  PRIMARY,lft,rgt     PRIMARY     4   sentimento.o2o.cms_objectID     1   Using where
3   DEPENDENT SUBQUERY  o2o2    ref     PRIMARY,auth_operationID    auth_operationID    4   const   18  Using where; Using temporary; Using filesort
3   DEPENDENT SUBQUERY  parents2    eq_ref  PRIMARY,lft,rgt     PRIMARY     4   sentimento.o2o2.cms_objectID    1   Using where

[ Voor 10% gewijzigd door gvanh op 07-08-2007 13:53 . Reden: explain toegevoegd. ]