Priekšteča noteikšana nested set modelim.

[Maris-S]

Saskāros ar tādu lietu ka jānosaka priekšteci pēc nested set modeļa veidotā datubāzē. Tā kā nācās pie tā mazliet spēcīgāk padomāt, nolēmu risinājumu ierakstīt šeit, ja nu kādam noder.

 

Tātad pēc nested set modeļa pamatdomas datu iegūšanas vaicājums ir aptuveni tāds:

 

select
node.*,
count(parent.id)-1 as depth
from
web_sections node,
web_sections parent
where
node.lft between parent.lft and parent.rgt
group by
node.lft
order by
node.lft

 

Tā kā tieši parent.id kā koloniņu mēs iegūt nevaram, jo tiek veikta grupēšana un nevar norādīt kuru tieši priekšteču tabulas rindiņu jāskatās, tad ir jātaisa papildus vaicājums:

 

select
node.*,
count(parent.id) - 1 as depth,
(select id from web_sections where lft < node.lft and rgt > node.rgt order by lft desc limit 1) parent_id
from
web_sections node,
web_sections parent
where
node.lft >= parent.lft and node.rgt <= parent.rgt
group by
node.id
order by
node.lft

 

Šajā gadījumā tiek meklēti visi mezgla priekšteči, sakārtoti dilstoši un tātad sanāk tiek izvadīts tieši pēdējais priekštecis visā ceļā.

 

Šādai pieejai vienīgais, kas jāuzlabo ir ātrdarbība. Balstoties uz jau izrunātām lietām, pie diskusijas par valsts noteikšanu pēc ip adreses, izmēģināju izveidot funkciju priekšteča noteikšanai, ātrdarbība uzreiz pieauga, pie tam ļoti jūtami.

 

CREATE DEFINER=`root`@`localhost` FUNCTION `get_parent`(i_lft int, i_rgt int) RETURNS int(11)
   NO SQL
   DETERMINISTIC
BEGIN
       RETURN (select id from web_sections where lft < i_lft and rgt > i_rgt order by lft desc limit 1);
END

 

Izskatās MySql pie šādiem vaicājumiem bez funkcijas neizsauc atsevišķi katru reizi vaicājumu, bet mēģina kaut kādu joinu veidot, bet nezinu, iespējams arī ir citi iemesli šādai veiktspējas atšķirībai.

 

Būtībā, ja ir nepieciešams priekštecis, tad pareizāk būtu pielikt papildus koloniņu ar priekšteča id un likt jaunus mezglus kopā ar priekšteča id. Noteikt priekštečus ar jau esošiem priekšteču id būs daudz ātrāk. Tātad, ja ir nepieciešams jau esošo tabulu papildināt ar priekšteču id, tad to var izdarīt, izmantojot iepriekš aprakstītos vaicājumus.

 

update
web_sections node,
(
	select
		node.id,
		(select id from web_sections where lft < node.lft and rgt > node.rgt order by lft desc limit 1) as parent_id
	from
		web_sections node,
		web_sections parent
	where
		node.lft >= parent.lft and node.lft <= parent.rgt
	group by
		node.id
) parent
set
node.parent_id = parent.parent_id
where
node.id = parent.id

 

Man kolēģis izveidoja vēl vienu priekšteča noteikšanas metodi, tā strādā ātrāk, jo neizmanto papildus vaicājumu, bet gan left join.

 

select
N.id,
 N.lft,
 N.rgt,
 IF(ISNULL(P.id), 0, MIN(CONVERT(CONCAT(N.lft - P.lft, '.', P.id * 10 + 1), DECIMAL(16, 8)))) as parent
from
web_sections N
   left join	web_sections P
   on (
		N.lft > P.lft and
		N.rgt < P.rgt and
     N.id <> P.id)
group by
N.id
order by
N.lft

 

Šis vaicājums nav pilnīgs, jo rezultātā tiek atgriezts skaitlis, pēc kura var noteikt priekšteča id, šajā gadījumā id ir decimālskaitļa decimālā daļa, kurai jānoņem pēdējās nulles un pēdējais 1. Uz ātro neatradām kā MySql varētu no skaitļa atgriezt tieši decimālo daļu, bet principā to varētu izdarīt konvertējot skaitli uz rakstzīmju virkni un iegūt to no rakstzīmju virknes. Metodes būtība ir tāda, ka tā meklē mazāko starpību no mezgla lft vērtības līdz priekšteča lft vērtībai, mazākā starpība tad norāda uz priekšteci. Vaicājums ir uzbūvēts tā, lai joina rezultātā nebūtu rindiņa, kurai sakrīt mezgla un priekšteča id. Mazāko starpību varētu meklēt arī priekšteču lft un rgt starpībai.

Edited January 7, 2011 by Maris-S