A fun multitable problem. Can't get syntax right.

[yeago]

I have two tables.

 

Sites

 

id, site_info, etc.

 

Categories

 

site_id, category

 

Sites can be in multiple categories, new entry for each one.

 

1,Bad

1,Ugly

1,Obnoxious

2,Good

2,Wonderful

 

Say a user comes along and asks for all sites that are 'Ugly' and 'Obnoxious' but not 'Wonderful'' or 'Bad'

 

Now, for relevancy purposes I also get the number of times site_id appears in Categories, so that sites that match the most user included categories rise to the top.

 

First I used the following multitable query to get both the number of times site appears in category, but also the site info.

 

$q = "SELECT sites. * , categories.site_id, count( categories.category )

FROM categories, sites

WHERE categories.site_id = sites.id

GROUP BY categories.site_id, sites.id";

 

Then I thought: well why not get ONLY sites that share one or more of the categories that users want with this same query? Isn't it better just to get it all done now? So I...

 

$q = "SELECT sites. * , categories.site_id, count( categories.category )

FROM categories, sites

WHERE categories.site_id = sites.id and (category = "ugly" or category = "good")

GROUP BY categories.site_id, sites.id";

 

Then I thought why not just knock off the sites the user wants to exclude now? So I....

 

$q = "SELECT sites. * , categories.site_id, count( categories.category )

FROM categories, sites

WHERE categories.site_id = sites.id and (category = "ugly" or category = "good") and (category != "wonderful" and category !="hideous")

GROUP BY categories.site_id, sites.id";

 

--

 

My current problem: Sites that are wonderful and hideous are still showing up! Ideas?

[fenway]

Well, I don't really understand how your last query has any effect -- if you're already only including results "WHERE category IN ('ugly','good')", then the other ones would necessarily be excluded! Maybe you can shed some light on this.

[yeago]

[!--quoteo(post=330189:date=Dec 24 2005, 02:14 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 24 2005, 02:14 PM) 330189[/snapback][/div][div class=\'quotemain\'][!--quotec--]

Well, I don't really understand how your last query has any effect -- if you're already only including results "WHERE category IN ('ugly','good')", then the other ones would necessarily be excluded! Maybe you can shed some light on this.

 

 

User makes a list of sites heshe wants:

 

Include('good','bad','ugly')

 

And doesn't want:

 

Exclude('wonderful','hideous')

 

Category table goes like:

 

1,good

1,bad

2,good

2,wonderful

3,ugly

4,otherworldy

 

According to the user's include/exclude list, Site 2 has something they want but is excluded because it is also 'wonderful' which was excluded. Site 1 and 3 appear. Site 4 wasn't mentioned, and so it is not included either.

 

Howto?

[fenway]

It can't work that way, because you have a GROUP BY clause, so you're collapsing all of the categories for each site into a single row so that you can get your counts -- unless you do a GROUP_CONCAT() and then search for each category string in the list.

 

You can't include & exclude sites in the same query, since the WHERE clause applies to a _single_ record, so it you ask for "category='good'", then obviously it's not equal to anything else! You need to run two queries -- or use a subquery -- to first include the desired ones but not the ones where they are sites that are undesired. So you do an "include" query -- without the GROUP BY -- and then add something like "AND NOT site_id IN ( SELECT site_id FROM categories WHERE category IN ('wonderful','hideous') )".

 

You'll have to do the counts separately. Hope that helps.

[yeago]

 

You know, I had a feeling that was the case. Thank you.

 

SELECT sites. * , categories.site_id, count( categories.category ) FROM categories, sites WHERE categories.site_id = sites.id and ( categories.category = 'bad') and not site_id in( select site_id from categories where category in ('good','bad') )

 

I get a syntax error at select 'site_id...'

 

 

[fenway]

Are you using a version of MySQL that supports subqueries (4.1+)? Otherwise, you'll have to do it in middleware, or use a temporary table.

[yeago]

Gonna do it using temp table I guess since MySQL version is only 4.0.25 . I don't know how efficient it will be.

 

One thing: how can I make count(categories.category) more friendly? Do I use 'AS'?

 

Instead of $r[categories.category] I'd prefer $r[relevancy].

[fenway]

Yup, use "AS" [ count(categories.category) AS relevancy ] -- IMHO, you should also use table aliases (categories AS c) so that you can write "c.category" -- makes everything more legible, and gives the parser have less to parse.