Julien
What you're trying to do isn't that uncommon, and I've met the same
problems in two major systems that I've built over the last year.
My solution was to use SQL up to a point (i.e. limit the complexity
of the query) to protect the performance of the server. So, for example,
you can read every record from the StoryCategories table into either a
Perl script (if you want fast development) or a C program (if you want
fast CGI performance) and examine then accept/reject each as you meet
it.
It takes a bit more programming skill, but the server will survive far
more simultaneous enquiries that way than it will with a single complex
SQL query.
Hope this helps some.
The Hooker
--
"I've had a perfectly wonderful evening, but this wasn't it"
> I am in the process of building a search engine on a database. I have two
> tables that have a many-to-many relationship: A story table and a category
> table.
>
> Story Table:
> -----------
> id | story|
> -----------
> 1 | Alice in Wonderland
> 2 | Peter Pan
> 3 | Pokemon's adventure
> 4 | Tale of Two cities
> 5 | War and Peace
>
> ..etc..
>
>
>
> Category Table:
> --------------
> id | category|
> --------------
> 1 | Children
> 2 | Classics
> 3 | Tolstoy
> ..etc..
> `
>
>
> Storycategories Table:
> ----------------------
> storyid | catid |
> ---------------------
> 1 | 1
> 1 | 2
> 2 | 1
> 2 | 2
> 3 | 1
> 4 | 2
> 5 | 2
> 5 | 3
> ..etc..
>
>
> >From my (admittedly dim) understanding of SQL, this is how one is supposed
> to organize a many-to-many relationship. So peter pan is listed as 1
> (childrens) and 2 (classics).
>
> I am writing a web interface to do searches for stories. In my web
> interface, you have something like this:
>
> ---------------------------------------------------------------
>
> []Classics []Childrens []Tolstoy []Fiction ...
>
> Search Categories:
> (x) match all categories
> ( ) match any category
>
> ---------------------------------------------------------------
>
> In order to build a query that matches ALL categories, I use aliasing. To do
> a search on stories that are Children AND Classics, I make two instances of
> each table as shown in the example below.
>
> select
> S.story
> from
> stories_tbl S,
> categories_tbl C1, storycategories_tbl O1,
> categories_tbl C2, storycategories_tbl O2
> where
> S.id = O1.storyid and O1.catid = C1.id and
> C1.category like "Children" and
> S.id = O2.storyid and O2.catid = C2.id and
> C2.category like "Classic"
> group by
> story
>
>
> This query works fine. My problem arises when I have many categories to
> choose from. If I check many of the categories boxes, and select match all
> categories, my program builds a query with a large amount of tables, I get
> an error like this:
>
> Error 1116: Too many tables. MySQL can only use 32 tables in a join
>
> My guess is that there is a limit of 32 tables in a join, and that my use of
> aliasing here ends up violating the 32 table limit.
>
> Even worse is when I make a query that has a little less than 32 tables.
> This won't give an error, but will leave a process running on the machine
> that consumes 100% cpu. After a while, this process will render the database
> unusable, and no one can use the database until the process is killed!
>
>
> So to clarify, the query that ends up being built looks like this:
> select
> S.story
> from
> stories_tbl S,
> categories_tbl C1, storycategories_tbl O1,
> categories_tbl C2, storycategories_tbl O2
> ....
> categories_tbl Cn, storycategories_tbl On
>
> where
> S.id = O1.storyid and O1.catid = C1.id and
> C1.category like "Children" and
> S.id = O2.storyid and O2.catid = C2.id and
> C2.category like "Classic"
> ...
> S.id = On.storyid and On.catid = Cn.id and
> Cn.category like "xxxx"
> group by
> story
>
>
> This query fails when "n" is around 16.
>
> My question is, how can I build a query that will support an arbitrary
> number of ANDs on a many to many relationship? I'd like to be able to do a
> search on stories that are "Children AND Classics AND Tolstoy AND....
> Category N" where N is fairly large (hundred or so)..
>
> Is there any way to do this? Am I using aliasing incorrectly?
>
> Those of you that have made it in reading this far, thank you :). I
> appreciate your attention and any assistance you might lend me in this
> matter.
>
> Julien Beasley
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
