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

Reply via email to