On Thu, May 1, 2008 at 8:53 PM, James Keeline <[EMAIL PROTECTED]> wrote: > --- Phill Sparks <[EMAIL PROTECTED]> wrote: > >> Hi Group, >> >> I've just moved hosts and sadly they're running MySQL 4.1.22 and a >> query running on my previous host (MySQL 5.0.45) has stopped >> working... >> >> I've got a nice nested query (see http://www.milk-hub.net/pb/08 ) that >> gets related content based on tags; the outer query collates the uri >> (like an ID) and tags to get the best 5 matches. Can anyone suggest a >> way of running this on the new server, preferably all in one query. I >> could out source the outer query to PHP but the SQL here is much >> cleaner and returns only the results I need. >> >> Cheers, >> >> Phill >> >> ------------------------------------ >> > > Looking at your query: > > SELECT uri, COUNT(tag) AS related_tag_count > FROM ( > SELECT uri, tag > FROM milk_blog_tags prop > WHERE uri <> 'posts/2008/01/17/finding_the_character_map_in_mac' > AND tag IN ('software','Character Map','Character > Palette','for:lsucs','osx') > ) AS tags > GROUP BY uri > HAVING COUNT(tag) > 0 > ORDER BY COUNT(tag) DESC, uri DESC > LIMIT 0, 5 > > I wonder why you are using a subquery to supply values for the FROM part of > your clause. Are you consulting different tables? I've not seen it done this > way and I'm surprised it would work on any version since you are selecting > two > columns in the subquery. > > It would be helpful if you told us exactly what error was being returned by > MySQL. > > James >
Hi James, An example of the full query is... SELECT SQL_CALC_FOUND_ROWS uri, COUNT(tag) AS related_tag_count FROM ( SELECT subject AS uri, object AS tag FROM milk_properties prop INNER JOIN milk_posts model ON model.uri = prop.subject AND model.status = "published" AND model.parent = 'milk:blog' WHERE predicate="http://cuttlefish.com/schemas/cms/1.0/concepts/document/tags" AND prop.subject <> 'posts/2008/04/13/how_to_customise_your_ssh_config' AND prop.object IN ('SSH','config','unix','howto') ) AS tags GROUP BY uri HAVING COUNT(tag) > 0 ORDER BY COUNT(tag) DESC, uri DESC Which I had shortened for the sake of readability and perhaps also given myself the answer at the same time. We have a table called milk_properties that contains _everything_ (uri, subject, predicate and object make up absolutely everything we store) and index tables for each of the types of content, like the blog (milk_posts). I think I'm going to see if I can find a way of doing all this without the join, then I can remove the subquery and that should solve it? I wonder if I can make another index table for all the tags to live in, like my first SQL. The error was "Invalid use of group function" Phill
