adjacent_image_link() needs optimization

[markjaquith]

adjacent_image_link() is really slow and stupid. It queries for all of the images attached to that post. And then it picks the previous or next one in PHP. And there's no caching, so you'll typically get this happening three times on an attachment page, (image [linked to next], prev link, next link).

We should actually just make the query grab the ONE image we want.

[markjaquith]

Talked with Nacin about this. This may end up being moot if we solve the bigger issue of determining the correct prev/next image, considering that images don't have to be in parent-post-bounded galleries.

In the meantime, this is the plugin I wrote to make it so that it doesn't query everything:

​https://gist.github.com/4357848

[obenland]

#22903 was marked as a duplicate.

[hardyy]

Added an optional argument in get_children() so that it returns numerical array containing only IDs of elements. Easier to return than array of objects.
Also makes it easier to search through in-built array functions.
in_array() is added to make sure loop don't skip in some cases.
If i have done any mistake, feel free to notify me.

[westi]

As well as the major optimisation of an improved query here there is a simple optimisation we could use to improve the performance of this on sites with a large number of images attached to a post.

If we switch from get_children to get_posts we can avoid calling update_post_cache on every attachment post for every query.

[SergeyBiryukov]

Related: #24688

[andy]

Replying to markjaquith:

We should actually just make the query grab the ONE image we want.

Newly attached patch does exactly that.

[wonderboymusic]

+1 to Skelton patch, I added a few doc block things

[westi]

Andy's patch looks neat - last time I optimised this I ended up with up to 6 simple queries to populate post__in to limit the get_children call which was much more consistently scalable but still pretty crappy with regard to the number of queries required.

I think it would be good to add some simple unit tests that prove this sort correctly when there are multiple images with the same menu_order in both directions and the same as the current image.

It isn't that I doubt it does it is just that is the one thing that tripped me up before and was how I ended up with 6 queries because I failed to find a simple single query which reliably worked for all cases ;)

[nacin]

Cool.

[andy]

Before trying my patch above please read #24688. The patch depends on #24687 which was closed as a duplicate of #17065 (Independent ASC/DESC in multiple ORDER BY statement.)

[nacin]

Patch is dependent on #17065.

[kitchin]

return "$where AND ... should be return "( $where ) AND ...