Kategoriler :
Genel Motosiklet Komik Security System Windows Siyasi Tatar Yemek Mutfak HTML Javascript Müzik CSS ASP Yaşam Sağlık SQL VBScript

Online: 1 ziyaretçi
69754 kere ziyaret edildi

Recursive Product Count or Recursive Product Search Etiketler : ASP,SQL

Eklenme tarihi : 12.10.2008 13:20:49 Güncellenme tarihi : 12.10.2008 13:32:53

You want a recursive product search or display product count under a category tree.This article contains information about postgresql, pgsql, postgres, MS Access

Let's suppose that you have categories table like below;

cat_id [serial NOT NULL]	parent_id [numeric]	category_name [character varying]
1	0	Electrical & Electronical
2	1	Capacitor
3	1	Inductor
4	0	Tools & Hardware
5	4	Hand Tools
6	4	Pneumatic Tools

...and let's suppose that you have a products table like below;

product_id [numeric]	cat_id [numeric]	product_name [character varying]
1	2	Capacitor 1
2	2	Capacitor 2
3	3	Inductor 1
4	3	Inductor 2
5	5	Hand Tool1
6	5	Hand Tool2
7	6	Pneumatic Tool 1
8	6	Pneumatic Tool 2

You just only have to do is adding a new column cat_path [character varying] at each table and write a script to find the category paths and put them on the new column with a delimiter character (I used ".")

cat_id [serial NOT NULL]	parent_id [numeric]	category_name [character varying]	cat_path
1	0	Electrical & Electronical	.0.1.
2	1	Capacitor	.0.1.2.
3	1	Inductor	.0.1.3.
4	0	Tools & Hardware	.0.4.
5	4	Hand Tools	.0.4.5.
6	4	Pneumatic Tools	.0.4.6.

product_id [numeric]	cat_id [numeric]	product_name [character varying]	cat_path
1	2	Capacitor 1	.0.1.2.
2	2	Capacitor 2	.0.1.2.
3	3	Inductor 1	.0.1.3.
4	3	Inductor 2	.0.1.3.
5	5	Hand Tool1	.0.4.5.
6	5	Hand Tool2	.0.4.5.
7	6	Pneumatic Tool 1	.0.4.6.
8	6	Pneumatic Tool 2	.0.4.6.

SQL (MS Access™) query for product count under "capacitor" category:
SELECT * FROM products WHERE cat_path ~~ '%.2.%'",cnn,1,3
count = objRs.RecordCount

SQL (POSTGRESQL) query for product count under "capacitor" category:
SELECT count(*) FROM products WHERE cat_path ~~ '%.2.%'",cnn,1,3
count = objRs("count")

SQL (POSTGRESQL) query for product search under "capacitor" category:
SELECT count(*) FROM products WHERE product_name ~~ ''%blah blah%' AND cat_path ~~ '%.2.%'",cnn,1,3
count = objRs("count")

Sample cat_path script written in vbscript (may load CPU to 100%):

Set cnn = CreateObject("ADODB.Connection") cnn.Open "dsn=ODBC_DSN_NAME" Set rs = CreateObject("ADODB.Recordset") Call catpath(0,".0.") Set rs = Nothing cnn.Close Set cnn = Nothing Wscript.Echo "Finished !" Function catpath(cid,cpth) cids = Array() cpth = cpth rs.Open "SELECT cat_id FROM categories WHERE parent_id=" & cid,cnn,1,3 Do Until rs.EOF s = cpth & rs("cat_id") & "." cid = rs("cat_id") Set rs2 = CreateObject("ADODB.Recordset") rs2.Open "UPDATE products SET cat_path='" & s & "' WHERE cat_id=" & cid,cnn,1,3 rs2.Open "UPDATE categories SET cat_path='" & s & "' WHERE cat_id=" & cid,cnn,1,3 Set rs2 = Nothing Redim Preserve cids(UBound(cids)+1) cids(UBound(cids)) = Array(cid,s) rs.MoveNext Loop rs.Close For i = LBound(cids) To UBound(cids) Call catpath(cids(i)(0),cids(i)(1)) Next End Function

20 kere görüntülendi...
Yorum Ekle