On Thu, May 1, 2008 at 10:49 PM, James Keeline <[EMAIL PROTECTED]> wrote:
> --- Phill Sparks <[EMAIL PROTECTED]> wrote:
>
>> 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
>>
> The SQL_CALC_FOUND_ROWS was new to me so I looked it up and found this page
>
> http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
>
> which suggests that it needs to be used in conjunction with a found_rows()
> function.
>
> I wonder if all of this is necessary or if it is being over engineered. Are
> there particular advantages you are trying to leverage with this syntax?
>
> Getting back to your initial issue, I don't know when some of these features
> were added and that could be confusing the older version of MySQL you are
> using. The PHP doc pages give better information on when a feature is added.
>
> James
>

Woo! I've fixed it! It would seem that it didn't like me using COUNT()
in the ORDER BY statement.  I changed this to use the alias
'related_tag_count' and it's working fine! :-)

MySQL 4 seems to have no problems with the SQL_CALC_FOUND_ROWS being
in there.  It's there so that I can find out the total number of
results available, useful for pagination and the such.

Thanks for your help!  Phill

Reply via email to