I have a problem coming up with a certain query.
I need to do a search in a database that consists of 2 tables, one 'pages' and one 'content'. Content has a relation with pages, 1 pages contains multiple content, and 1 content has 1 page as a parent. Pages can also have parents, which are also parents.
pages: uid,pid,title,type (pid points to 'pages')
content: uid,pid,title,text (pid point to 'pages')
I need to do a search query. I want the results to be grouped by pages, and with a count() that tells me how many times a certain searchterm exists in a certain page. (it's okay if it counts every 'content' record as 1). It has to be sorted by this count() so that the top result will be the result with the most hits per page. Only records associated with pagetype=1 need to be selected.
It also has to support multiple search terms. All terms have to be on a page but not necessarily in the same content record.