Giant query, Weird Error

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Giant query, Weird Error

Post by Skara »

Code: Select all

SELECT images.*, COUNT(tags.tag) as `tagtablecount` FROM `images` 
LEFT JOIN `tags` ON images.id = tags.fileid 
GROUP BY images.id WHERE (
`tagtablecount` > '8' OR 
(`tagtablecount` = '8' AND images.rating > '87') OR 
(`tagtablecount` = '8' AND images.rating = '87' AND images.views > '35') OR 
(`tagtablecount` = '8' AND images.rating = '87' AND images.views = '35' AND images.id > '586')
) ORDER BY `tagtablecount` ASC, images.rating ASC, images.views ASC, images.id ASC 
LIMIT 2;

Code: Select all

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (`tagtablecount` > '8' OR (`tagtablecount` = '8' AND images.rating > '87') OR (' at line 1
I thought.... maybe the GROUP BY needs to be after the WHERE... so..

Code: Select all

SELECT images.*, COUNT(tags.tag) as `tagtablecount` FROM `images` 
LEFT JOIN `tags` ON images.id = tags.fileid 
 WHERE (
`tagtablecount` > '8' OR 
(`tagtablecount` = '8' AND images.rating > '87') OR 
(`tagtablecount` = '8' AND images.rating = '87' AND images.views > '36') OR 
(`tagtablecount` = '8' AND images.rating = '87' AND images.views = '36' AND images.id > '586')
) GROUP BY images.id ORDER BY `tagtablecount` ASC, images.rating ASC, images.views ASC, images.id ASC 
LIMIT 2;

Code: Select all

Unknown column 'tagtablecount' in 'where clause'
grr. so...maybe.. I thought I could add tagtablecount to one of the tables....?

Code: Select all

SELECT images.*, COUNT(tags.tag) as tags.tagtablecount FROM `images` 
LEFT JOIN `tags` ON images.id = tags.fileid 
 WHERE (
tags.tagtablecount > '8' OR 
(tags.tagtablecount = '8' AND images.rating > '87') OR 
(tags.tagtablecount = '8' AND images.rating = '87' AND images.views > '39') OR 
(tags.tagtablecount = '8' AND images.rating = '87' AND images.views = '39' AND images.id > '586')
) GROUP BY images.id ORDER BY tags.tagtablecount ASC, images.rating ASC, images.views ASC, images.id ASC 
LIMIT 2;

Code: Select all

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.tagtablecount FROM `images` LEFT JOIN `tags` ON images.id = tags.fileid  WHERE ' at line 1
grrrrr....

I'm generating a list of images sorted by how many tags each has. (And then sorted by rating, views, and id).
THIS code is to figure out the two previous entries in the list (assuming you're moving down the list).

The list generation query is:

Code: Select all

SELECT {$_CATTABLE}.*, COUNT({$_TAGTABLE}.tag) as `tagtablecount` FROM `{$_CATTABLE}`
LEFT JOIN `{$_TAGTABLE}` ON {$_CATTABLE}.id = {$_TAGTABLE}.fileid GROUP BY {$_CATTABLE}.id
ORDER BY `tagtablecount` DESC, {$_CATTABLE}.rating DESC, {$_CATTABLE}.views DESC, {$_CATTABLE}.id DESC
LIMIT 100;
and this works fine.

tables:

`images`
id, file, rating, views

`tags`
id, fileid, tag


Ideas?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Giant query, Weird Error

Post by timvw »

Your first query is invalid ( SELECT ... FROM ... WHERE .. GROUP BY ... HAVING ... LIMIT ... )

The second query is invalid, because when the engine processes the WHERE clause it doesn't know about the aliases in the SELECT clause yet
(Order of processing: FROM, WHERE, GROUP BY, HAVING, SELECT, LIMIT)

Instead of using the alias you can use the original name:

Code: Select all

... WHERE ( COUNT(tags.tag) > 8 OR ( COUNT(tags.tag) = 8 AND images.rating > 87 ) ...
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post by aaronhall »

You can use the alias for comparison, but comparisons against those aggregated values (those derived from COUNT, MAX, MIN, etc.) should be placed in the HAVING clause.
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

hm. Putting it in the HAVING clause would make the query not work. COUNT(tags.tag) can be >= to 'num_tags', but it won't sort right unless it's grouped the way it is.

count > numtags OR
count = numtags AND ...>... OR
count = numtags AND ...=... AND ...>... OR
etc.

..I get a 'Invalid use of Group By Function' when I don't put it in the HAVING clause. So. The following... doesn't give any errors.

Code: Select all

SELECT {$_CATTABLE}.*, COUNT({$_TAGTABLE}.tag) as tagtablecount FROM `{$_CATTABLE}`
LEFT JOIN `{$_TAGTABLE}` ON {$_CATTABLE}.id = {$_TAGTABLE}.fileid 
WHERE ({$_CATTABLE}.id != '{$imageid}') 
GROUP BY {$_CATTABLE}.id 
HAVING COUNT({$_TAGTABLE}.tag) <= $numtags 
ORDER BY tagtablecount DESC, {$_CATTABLE}.rating DESC, {$_CATTABLE}.views DESC, {$_CATTABLE}.id DESC 
LIMIT 2;
BUT.. It doesn't grab the right images, either. Example:

Code: Select all

723id   8 tags    84 rating   25 views
  37id   7 tags   120 rating   15 views
1146id   7 tags   100 rating   11 views
 683id   7 tags    65 rating   10 views
^ is the correct sorting.

723 and 37 grab the correct two next images.
1146 grabs 37 and 683 as the two next images. !!!
683 grabs 37 and 1146 as the two next images. !!!!


The problem is they have the same number of tags, obviously.
The rating may not be lower on the next two images, so I can't simply say 'WHERE rating < thisrating.' BUT, the rating will be lower--or equal, if the number of tags is equal. If the number of tags and the rating are both equal, the number of views will be less than or equal.

A simpler solution would be to sort by tags DESC, id DESC, but given the volume of images (currently over 2000), this precise sorting is really how it needs to be. A few hundred images may all have the same number of tags. A few dozen may have the same number of tags and the same rating...

bottom line
I really need to put the aggregate function in the WHERE clause rather than the HAVING clause. Is there some way I can do this with subqueries, or some other method?

...erk....btw, this post is the 'next two' query. the previous post was the 'previous two' >_>
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

What you need to do is give each record a row number or rank or something. Then select the two rows with a rank or row less than the the target row.

You can look at this viewtopic.php?t=65387 for an example of rownumber. Then you can do some sort of HAVING rownum < otherRowNum
Post Reply