Jump to content


Photo

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


  • Please log in to reply
7 replies to this topic

#1 yeago

yeago
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 24 December 2005 - 06:47 AM

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 December 2005 - 02:14 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 yeago

yeago
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 24 December 2005 - 05:50 PM

[!--quoteo(post=330189:date=Dec 24 2005, 02:14 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Dec 24 2005, 02:14 PM) View Post[/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.
[/quote]


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?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 December 2005 - 10:29 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 yeago

yeago
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 24 December 2005 - 11:43 PM


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...'



#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 December 2005 - 01:27 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 yeago

yeago
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 25 December 2005 - 07:02 PM

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].

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 December 2005 - 10:52 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users