[wp-hackers] Order posts by time of their last comment

[Christian Foster]

Thanks for all that - the key thing that I am trying to achieve is to
use a standard query (so I can do simple pagination of thousands of
posts using the inbuilt functions) but just change the ordering.

At the moment the best way seems to be to use the meta key and hook
into the wp_insert_comment or post_comment hooks so that they update a
meta field, and then use that for the ordering. The post gets the meta
value set to the time of publishing when published so even if there
are no comments it still appears in list.

Seems to work pretty well, the idea for us is that posts with a new
comment always rise to the top of the loop.

On Tue, Sep 28, 2010 at 11:12 PM, Otto <otto at ottodestruct.com> wrote:
> On Tue, Sep 28, 2010 at 4:50 PM, Mike Schinkel
> <mikeschinkel at newclarity.net> wrote:
>> On Sep 28, 2010, at 10:25 AM, Christian Foster wrote:
>>> I am not sure of the consequence of ordering a large number of items
>>> but in the docs it down mention you can sort using a numerical value
>>> in a post meta key - orderby=meta_value_num. I haven't looked through
>>> the code as yet to see what difference it makes.
>>
>> There's no difference in the way WordPress stores meta_values when "orderby=meta_value_num" vs. when "orderby=meta_value"; WordPress just adds a 0 to meta_value. In a nutshell it just does this:
>>
>> SELECT post_id FROM wp_postmeta ORDER BY meta_value+0
>
> This trick basically makes MySQL treat the values as numbers instead
> of as strings.
>
> Difference:
> ORDER BY meta_value: 200 comes before 40.
> ORDER BY meta_value+0: 40 comes before 200.
>
> The storage is the same. The ordering is not.
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>