Page 1 of 1

Giant query, Weird Error

Posted: Fri Mar 23, 2007 8:16 pm
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?

Re: Giant query, Weird Error

Posted: Sat Mar 24, 2007 3:36 am
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 ) ...

Posted: Sat Mar 24, 2007 5:45 am
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.

Posted: Sat Mar 24, 2007 4:48 pm
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' >_>

Posted: Mon Mar 26, 2007 10:42 am
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