I have a fairly complex query for a site search engine. Since only a portion of the results will be displayed on one page, I want to query the database first to get the number of results I'll have. But I'm having trouble figuring out what the COUNT expression I need is.
Here's the actual search query (explained below):
--------------------------------------------
SELECT p.file, p.qs, i.title, i.description, (k.weight * COUNT(p.id)) AS score, COUNT(p.id) AS word_count
FROM search_pages AS p
LEFT JOIN search_page_info AS i
ON CONCAT(p.file, p.qs) = CONCAT(i.file, i.qs),
search_keywords AS k
WHERE p.id = k.page_id
AND k.word IN ($keyword_str)
GROUP BY p.id
HAVING word_count >= $word_count
ORDER BY score DESC
LIMIT $f, $n
--------------------------------------------
For purposes of indexing, I keep page titles and descriptions in a table (search_page_info) separate from the actual page listings (search_pages). Not every page in search_pages has a title/description in search_page_info, hence the left join. Keywords are stored in table search_keywords, and each keyword-page pair has a "weight" (based mostly on the number of times that word appears in that page).
$keyword_str above represents a comma-delimited list of keywords in quotes ('foo','bar'). $word_count is the number of keywords. That having clause is only in there when the user chooses to display only results with all of the words. Without that feature, I can write a query to get the number of rows... but now I want to impliment that feature and I'm stuck.
Can anyone help me out?
Thanks,
Keegan
Here's the actual search query (explained below):
--------------------------------------------
SELECT p.file, p.qs, i.title, i.description, (k.weight * COUNT(p.id)) AS score, COUNT(p.id) AS word_count
FROM search_pages AS p
LEFT JOIN search_page_info AS i
ON CONCAT(p.file, p.qs) = CONCAT(i.file, i.qs),
search_keywords AS k
WHERE p.id = k.page_id
AND k.word IN ($keyword_str)
GROUP BY p.id
HAVING word_count >= $word_count
ORDER BY score DESC
LIMIT $f, $n
--------------------------------------------
For purposes of indexing, I keep page titles and descriptions in a table (search_page_info) separate from the actual page listings (search_pages). Not every page in search_pages has a title/description in search_page_info, hence the left join. Keywords are stored in table search_keywords, and each keyword-page pair has a "weight" (based mostly on the number of times that word appears in that page).
$keyword_str above represents a comma-delimited list of keywords in quotes ('foo','bar'). $word_count is the number of keywords. That having clause is only in there when the user chooses to display only results with all of the words. Without that feature, I can write a query to get the number of rows... but now I want to impliment that feature and I'm stuck.
Can anyone help me out?
Thanks,
Keegan