Keskustelu: Nettisivujen teko: MySQL: Order By -ongelma

Pitäisi saada listattua tiedot prioriteetin mukaan niin, että prioriteetti * parentin prioriteetti. Alla tämän hetkinen SQL -lausekkeeni. Ymmärrän ongelman, mutta en tiedä mitä pitäisi tehdä.

SELECT name, priority FROM taulu ORDER BY priority * (SELECT priority FROM taulu WHERE id = parent) DESC;

Käyttämäni taulun rakenne on yksinkertaistettuna seuraavanlainen:

+---------------+----+--------+----------+
| name          | id | parent | priority |
+---------------+----+--------+----------+
| etusivu       | 1  | 0      | 1.0      |
| artikkeli     | 2  | 0      | 0.5      |
| ala artikkeli | 3  | 2      | 0.7      |
| alin sivu     | 4  | 3      | 0.9      |
| testisivu     | 5  | 0      | 0.8      |
+---------------+----+--------+----------+

Yllä olevan taulun tulisi tulostua:

Etusivu 1.0
Testisivu 0.8
Artikkeli 0.5
Ala artikkeli 0.35
Alin sivun 0.315

Muodosta kysely tällaisessa muodossa:

SELECT
  t1.x * t2.x AS y
FROM t AS t1
JOIN t AS t2 ON t1.parent = t2.id;

Metabolix kirjoitti:

Muodosta kysely tällaisessa muodossa:

SELECT
  t1.x * t2.x AS y
FROM t AS t1
JOIN t AS t2 ON t1.parent = t2.id;

Tämä ei kuitenkaan taida toimia kuvailemallani tavalla. Esimerkiksi "Alin sivu" -sivun prioriteetiksi muodostuisi 0.63, vaikka oikean tuloksen pitäisi olla 0.315.

Oikeanlainen kaavahan näyttäisi tältä:

Artikkeli * Ala artikkeli
(0.5 * 0.7) = 0.35
Ala artikkeli * Alin sivu
(0.35 * 0.9) = 0.315

Kun se nyt näyttää tältä

Ala artikkeli * Alin sivu
(0.7 * 0.9) = 0.63

Itse laskisin tuon tietokannan ulkopuolella; rekursiolla, tallettaen matkalla laskettujen arvot myös, jottei tarvitse laskea samoja uudestaan.

Itse varmaan lisäisin uuden kentän tuohon tauluun, johon laskisin oikean prioriteetin aina kun tasokohtainen prioriteetti muuttuu. Aina kun tasokohtainen prioriteetti muuttuu niin pitää sen ja sen lapsielementtien oikeat pririteetit päivittää rekursiivisesti. Olettaen, että prioriteetit ei kovin usein vaihdu niin tämä on varmaan aika tehokas ratkaisu, koska hakulausettakin voi yksinkertaistaa.

FooBatin ajatusta jatkan: Päivittäminen onnistuisi ehkä näppärästi triggerillä. Voisit AFTER UPDATE -triggerissä päivittää kaikki lapsirivit, jolloin triggeriä kutsuttaisiin rekursiivisesti taas niille jne. Vastaavasti voisit BEFORE INSERT -triggerillä laskea alkuarvon.