Labākais variants iekš MySQL

[signis]

Hola!

 

Problēma ir sekojoša un pagaidām teorētiska. Vienkārši, varbūt kāds ir redzējis vai zin tās risinājumu.

Ir viena tabula ar ierakstiem. Piemēram, preces. Viena prece, viena rinda.

Otra tabula ar katras preces tagiem. Viens tags, viena rinda. Katrai precei var būt vairākas rindas (tagi).

Vai ir kāds veids kā ar MySQL līdzekļiem veikli atlasīt tās preces kurām:

*) noteikti ir tagi A, B, C

*) tikai ir tagi A, B, C

Ar PHP mācētu, bet interesē vai nav MySQL viltības šādai lietai.

[Gints Plivna]

Rupji nedomājot neko par ātrdarbību var šādi.

Izveidojam datus:

mysql> create table preces (id int, name varchar(10));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into preces values (1, '3tagider');
Query OK, 1 row affected (0.02 sec)

mysql> insert into preces values (2, '3tagineder');
Query OK, 1 row affected (0.01 sec)

mysql> insert into preces values (3, '4tagider');
Query OK, 1 row affected (0.03 sec)

mysql> insert into preces values (4, '1tags');
Query OK, 1 row affected (0.03 sec)

mysql> create table tagi (tg_id int, tg_prc_id int, tags varchar(1));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into tagi values (1, 1, 'a');
Query OK, 1 row affected (0.02 sec)

mysql> insert into tagi values (2, 1, 'b');
Query OK, 1 row affected (0.03 sec)

mysql> insert into tagi values (3, 1, 'c');
Query OK, 1 row affected (0.03 sec)

mysql> insert into tagi values (4, 2, 'a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into tagi values (5, 2, 'b');
Query OK, 1 row affected (0.03 sec)

mysql> insert into tagi values (6, 2, 'z');
Query OK, 1 row affected (0.03 sec)

mysql> insert into tagi values (7, 3, 'a');
Query OK, 1 row affected (0.03 sec)

mysql> insert into tagi values (8, 3, 'b');
Query OK, 1 row affected (0.02 sec)

mysql> insert into tagi values (9, 3, 'c');
Query OK, 1 row affected (0.03 sec)

mysql> insert into tagi values (10, 3, 'z');
Query OK, 1 row affected (0.03 sec)

mysql> insert into tagi values (11, 4, 'a');
Query OK, 1 row affected (0.01 sec)

 

Tos kam ir vismaz tagi a, b un c

 

mysql> select name from preces
   -> where exists (select 1 from tagi where tg_prc_id = id and tags = 'a')
   -> and exists (select 1 from tagi where tg_prc_id = id and tags = 'b')
   -> and exists (select 1 from tagi where tg_prc_id = id and tags = 'c');
+----------+
| name     |
+----------+
| 3tagider |
| 4tagider |
+----------+
2 rows in set (0.02 sec)

 

Tos kam ir tieši 3 tagi a, b un c.

 

mysql> select name from preces
   -> where exists (select 1 from tagi where tg_prc_id = id and tags = 'a')
   -> and exists (select 1 from tagi where tg_prc_id = id and tags = 'b')
   -> and exists (select 1 from tagi where tg_prc_id = id and tags = 'c')
   -> and 3 = (select count(*) from tagi where tg_prc_id = id);
+----------+
| name     |
+----------+
| 3tagider |
+----------+
1 row in set (0.00 sec)

Protams panesās jautājumi, kas ir ja tagi dublējas - saprātīgi jau būtu lai nedublējās, prasās pēc unikālās atslēgas uz taga un norādes uz preces.

Vēl protams ir neproduktīvi, ka n reizes skanē tagu tabulu.

 

Šis atgriež tos pašus kam ir vismaz tagi a, b un c un iespējams varētu būt mazliet labāks jo skanē tagu tabulu tikai 1reiz un (nezinu gan vai MySQL to ļauj) teorētiski ja tagu daudz un dažādi var atlasīt pēc indeksa tieši tikai tos ierakstus, kam šie tagi. Šis jo sevišķi ir prasīgs pēc tā, lai tagi būtu tikai 1reiz, jo citādi ja tags a būs 2reiz un c vienreiz, tad arī tāda prece derēs.

 

mysql> select name from preces where id in (
   ->   select tg_prc_id from tagi where tags in ('a', 'b', 'c')
   ->   group by tg_prc_id
   ->   having count(*) = 3);
+----------+
| name     |
+----------+
| 3tagider |
| 4tagider |
+----------+
2 rows in set (0.02 sec)

 

Bet pusnaktī neko vairāk gribēt nevar :)

 

Gints Plivna

http://datubazes.wordpress.com

[signis]

Cepuri nost, tiešām pavisam nost.

[black]

Varbūt šitā?

 

SELECT p.name

FROM preces AS p, tagi AS t

WHERE t.tg_prc_id = p.id AND t.tags IN ('a', 'b', 'c')

 

SELECT p.name

FROM preces AS p, tagi AS t

LEFT OUTER JOIN tagi AS tn ON tn.id<>t.id AND tn.tg_prc_id = p.id

WHERE t.tg_prc_id = p.id AND t.tags IN ('a', 'b', 'c')

HAVING tn.id IS NULL

 

EDIT:

Tikko pamanīju Ginta pēdējo posta daļu. Mans pirmais kverijs sanāca dikti līdzīgs.

Edited August 7, 2008 by black

[Mr.Key]

Varbūt šitā?

 

SELECT p.name

FROM preces AS p, tagi AS t

WHERE t.tg_prc_id = p.id AND t.tags IN ('a', 'b', 'c')

 

SELECT DISTINCT .. citādi būs 3 vienādi name

 

 

Un variants ar EXISTS ir pareizs, bet nebūs optimāls, pie liela ierakstu skaita būs čau, bet nu autors jau pats uz to norādīja.

Edited August 7, 2008 by Mr.Key

[black]

tagad dienasgaismä arì mans otrais kverijs izskatäs slikti

[Gints Plivna]

Nu re un tagad jau man ir skaidrs, kā vienā piegājienā pār tagu tabulu atlasīt visus vajadzīgos. Pasākums ir pardāīts uz Oracle, jo MySQL man pašlaik nav pieejams, bet kā izskatās CASE ir arī MySQLā, tikai beigās jāliek nevis end, bet end case un apakšvaicājumiem kaut kad laikam tur vajadzēja obligāti aliasus. Bet varbūt tā bija SQL Serverī, kas to lai visu atceras:)

Anyway tātad domā tāda- skatamies ko rāda šis selekts:

SQL> select tg_prc_id, sum(modif_tags), count(*) from (
 2	select tg_prc_id, (case when tags in  ('a', 'b', 'c') then 1 else 0 end) modif_tags
 3	from tagi
 4  )
 5  group by tg_prc_id
 6  /

	   TG_PRC_ID	  SUM(MODIF_TAGS)			 COUNT(*)
-------------------- -------------------- --------------------
			   1					3					3
			   2					2					3
			   4					1					1
			   3					3					4

 

sum(modif_tags) rāda cik daudz vajadzīgos tagus atlasīja (nevajadzīgajiem, kā redzams ir svars 0). Savukārt count(*) rāda cik tagu ir vispār katrai šai precei.

 

Un tagad būs tā - ja kopējo tagu skaits nav svarīgs, tad vaicājums ir šāds (kas principā ir tas pats kas iepriekšējā reizē tikai mazliet samudīts):

SQL> select name from preces where id in (
 2	select tg_prc_id from (
 3	  select tg_prc_id, (case when tags in  ('a', 'b', 'c') then 1 else 0 end) modif_tags
 4	  from tagi
 5	)
 6	group by tg_prc_id
 7	having sum(modif_tags) = 3
 8  )
 9  /

NAME
----------
3tagider
4tagider

Savukārt, ja ir svarīgs arī kopējo tagu skaits tad jāpievieno vēl nosacījums uz kopējiem tagiem:

SQL> select name from preces where id in (
 2	select tg_prc_id from (
 3	  select tg_prc_id, (case when tags in  ('a', 'b', 'c') then 1 else 0 end) modif_tags
 4	  from tagi
 5	)
 6	group by tg_prc_id
 7	having sum(modif_tags) = 3
 8	  and count(*) = 3
 9  )
10  /

NAME
----------
3tagider

 

No ātrdarbības viedokļa runājot - īsumā mana hipotēze ir tāda, ka, ja ir jau kāds nopietns filtrs uz precēm, tad tas brīnums ar exists ir labāks, jo tad tikai dažām precēm tas būs jāpārbauda. Savukārt, ja pasākums jābauda visām precēm bez izņēmuma, tad jāsāk braukt pa tagiem, jo tas ir vienīgais kritērijs, un ja visi jāskatās, tad labāk to darīt ar full skanu, attiecīgi šis pasākums varētu būt labāks. Enjoy! :)

 

Gints Plivna

http://datubazes.wordpress.com

 

P.S. Piezīme andrisp - sql tags iekš atbildes nav tāpēc, ka tas kaut kādā veidā pamanās pazaudēt new line simbolus, un samest dažas rindas vienkopus. Attiecīgi code šoreiz ir labāks :)