Index: openacs-4/packages/ecommerce/www/product-search-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-search-oracle.xql,v
diff -u -r1.3 -r1.3.4.1
--- openacs-4/packages/ecommerce/www/product-search-oracle.xql 30 Nov 2002 17:31:29 -0000 1.3
+++ openacs-4/packages/ecommerce/www/product-search-oracle.xql 10 Jun 2005 08:59:49 -0000 1.3.4.1
@@ -8,35 +8,89 @@
- select p.product_name, p.product_id, p.dirname,
- p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+ select r.*
+ from (
+ select rownum as r_rownum, q.*
+ from (
+ select p.product_name, p.product_id, p.dirname,
+ p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+ from ec_products_searchable p, ec_category_product_map c
+ where c.category_id=:category_id
+ and p.product_id=c.product_id
+ and pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) > 0
+ order by score desc
+ ) q
+ ) r
+ where r_rownum > :start_row
+ and r_rownum <= :start_row + :rows_per_page
+
+
+
+
+
+ select count(*)
from ec_products_searchable p, ec_category_product_map c
where c.category_id=:category_id
and p.product_id=c.product_id
and pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) > 0
- order by score desc
+
- select p.product_name, p.product_id, p.dirname,
- p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+ select r.*
+ from (
+ select rownum as r_rownum, q.*
+ from (
+ select p.product_name, p.product_id, p.dirname,
+ p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+ from ec_products_searchable p, ec_subcategory_product_map c
+ where c.subcategory_id=:subcategory_id
+ and p.product_id=c.product_id
+ and pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) > 0
+ order by score desc
+ ) q
+ ) r
+ where r_rownum > :start_row
+ and r_rownum <= :start_row + :rows_per_page
+
+
+
+
+
+ select count(*)
from ec_products_searchable p, ec_subcategory_product_map c
where c.subcategory_id=:subcategory_id
and p.product_id=c.product_id
and pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) > 0
- order by score desc
+
- select p.product_name, p.product_id, p.dirname,
- p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+ select r.*
+ from (
+ select rownum as r_rownum, q.*
+ from (
+ select p.product_name, p.product_id, p.dirname,
+ p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
+ from ec_products_searchable p
+ where pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) > 0
+ order by score desc
+ ) q
+ ) r
+ where r_rownum > :start_row
+ and r_rownum <= :start_row + :rows_per_page
+
+
+
+
+
+ select count(*)
from ec_products_searchable p
where pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) > 0
- order by score desc
Index: openacs-4/packages/ecommerce/www/product-search-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-search-postgresql.xql,v
diff -u -r1.3 -r1.3.4.1
--- openacs-4/packages/ecommerce/www/product-search-postgresql.xql 30 Nov 2002 17:31:29 -0000 1.3
+++ openacs-4/packages/ecommerce/www/product-search-postgresql.xql 10 Jun 2005 08:59:49 -0000 1.3.4.1
@@ -14,10 +14,20 @@
where c.category_id=:category_id
and p.product_id=c.product_id
and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) > 0
- order by score desc
+ order by score desc limit :rows_per_page offset :start_row
+
+
+ select count(*) as search_count
+ from ec_products_searchable p, ec_category_product_map c
+ where c.category_id=:category_id
+ and p.product_id=c.product_id
+ and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) > 0
+
+
+
select p.product_name, p.product_id, p.dirname, p.one_line_description,
@@ -26,18 +36,36 @@
where c.subcategory_id=:subcategory_id
and p.product_id=c.product_id
and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) > 0
- order by score desc
+ order by score desc limit :rows_per_page offset :start_row
+
+
+ select count(*) as search_count
+ from ec_products_searchable p, ec_subcategory_product_map c
+ where c.subcategory_id=:subcategory_id
+ and p.product_id=c.product_id
+ and pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) > 0
+
+
+
select p.product_name, p.product_id, p.dirname, p.one_line_description,
pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) as score
from ec_products_searchable p
where pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) > 0
- order by score desc
+ order by score desc limit :rows_per_page offset :start_row
+
+
+ select count(*) as search_count
+ from ec_products_searchable p
+ where pseudo_contains(coalesce(p.product_name, '') || coalesce(p.one_line_description, '') || coalesce(p.detailed_description, '') || coalesce(p.search_keywords,''), :search_text) > 0
+
+
+
Index: openacs-4/packages/ecommerce/www/product-search.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/product-search.tcl,v
diff -u -r1.6.4.4 -r1.6.4.5
--- openacs-4/packages/ecommerce/www/product-search.tcl 22 Apr 2005 09:33:36 -0000 1.6.4.4
+++ openacs-4/packages/ecommerce/www/product-search.tcl 10 Jun 2005 08:59:49 -0000 1.6.4.5
@@ -52,6 +52,9 @@
set subcategory_id [lindex [split $combocategory_id "|"] 1]
}
+# filter extra spaces
+regsub -all -- {\s+} $search_text { } search_text]
+set search_text "[string trim $search_text]"
# filter overflow attempts from really long search strings
set search_text "[string range $search_text 0 100 ]"
@@ -72,37 +75,42 @@
if { ![empty_string_p $subcategory_id] && $subcategory_id > 0} {
set query_string [db_map search_subcategory]
+ set query_count_string [db_map search_count_subcategory]
# select p.product_name, p.product_id, p.dirname, p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
# from ec_products_searchable p, ec_subcategory_product_map c
# where c.subcategory_id=:subcategory_id
# and p.product_id=c.product_id
# and pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) > 0
- # order by score desc
+ # order by score desc limit :rows_per_page offset :start_row
} else {
if { ![empty_string_p $category_id] } {
set query_string [db_map search_category]
+ set query_count_string [db_map search_count_category]
# select p.product_name, p.product_id, p.dirname, p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
# from ec_products_searchable p, ec_category_product_map c
# where c.category_id=:category_id
# and p.product_id=c.product_id
# and pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) > 0
- # order by score desc
+ # order by score desc limit :rows_per_page offset :start_row
} else {
set query_string [db_map search_all]
+ set query_count_string [db_map search_count_all]
# select p.product_name, p.product_id, p.dirname, p.one_line_description,pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) as score
# from ec_products_searchable p
# where pseudo_contains(p.product_name || p.one_line_description || p.detailed_description || p.search_keywords, :search_text) > 0
- # order by score desc
+ # order by score desc limit :rows_per_page offset :start_row
}
}
set search_string ""
-set search_count 0
+set page_count 0
+# make search_count equal to count(*) of query
+# getting the count of search results requires a second db hit,
+# which is magnitudes better than the tcl filtering that used to be in this loop
+db_1row get_search_count $query_count_string
set have_how_many_more_p "f"
set end_row_of_next_page [expr $start_row + (2 * $rows_per_page)]
db_foreach get_product_listing_from_search $query_string {
-
- if { $search_count >= $start_row && [expr $search_count - $start_row] < $rows_per_page } {
append search_string "
@@ -117,14 +125,14 @@
| [ec_linked_thumbnail_if_it_exists $dirname "t" "t"] |
"
- set last_row_this_page [expr $search_count + 1]
- }
- incr search_count
- if { $search_count > $end_row_of_next_page } {
+
+ incr page_count
+}
+set last_row_this_page [expr $page_count + $start_row ]
+if { $search_count > $end_row_of_next_page } {
# we know there are at least how_many more items to display next time
set have_how_many_more_p "t"
- }
-}
+}
if { $start_row >= $rows_per_page } {
set prev_link "Previous $rows_per_page"
@@ -135,7 +143,7 @@
if { [string equal $have_how_many_more_p "t"] } {
set next_link "Next $rows_per_page"
} else {
- set number_of_remaining_products [expr $search_count - $start_row - $rows_per_page]
+ set number_of_remaining_products [expr $search_count - $start_row - $rows_per_page ]
if { $number_of_remaining_products > 0 } {
set next_link "Next $number_of_remaining_products"
} else {