Ask your WordPress questions! Pay money and get answers fast! (more info)

Custom WP_Query post ordering based on semi-alphabetical order

I have posts with titles like the following, which I want to be in the order shown:

Post 1A
Post 1B
Post 1C
....
Post 1Z
Post 1AA
Post 1AB

and

Post 1.1
Post 1.2
Post 1.3
...
Post 1.9
Post 1.10
Post 1.11
Post 1.12


These are similar to alphabetical order, but not quite—if I order the posts alphabetically, Post 1AA comes directly after Post 1A, post 1.10 comes directly after Post 1.1, etc.

Is there a way to use a filter or hook to automatically order these the way I show them above I want?

My only thought is that they could be made alphabetical on the fly with a filter that uses preg_replace() to temporarily change 1.1 to 1.01, 1.2 to 1.02, etc., and 1A to 1-A, etc (since 1-Z comes before 1AA).

I have hundreds of posts like this, so I'm not looking for a solution that involves manual editing, or a custom post order plugin.

Answers (4)

2012-09-05

Romel Apuya answers:

is Post 1A,Post 1B a title?

can you provide the code of your query?
y not add them sequentially and then order them ASC by date?


web559 comments:

They're already added. I updated my question to indicate I'm not looking for a solution that involves manual editing.

The query (or sorting mechanism) is what I'm asking for.


Romel Apuya comments:

can you provide your current query?


web559 comments:

I don't have a specific query. Assume it's default archive page query.

2012-09-05

Arnav Joy answers:

in your posts title it is fixed "Post "

like Post 1A , Post 1B .....

so very post's title will start in this way "Post "

2012-09-06

Hai Bui answers:

WP_Query won't work in this case but this can be done with a custom SQL query. (no different in result)
Let me know if you are interested.


web559 comments:

What would the SQL query be? It could potentially still be hooked into WP_Query, using a filter such as posts_orderby hook (which adds to the SQL). This is quite close to a solution: http://wordpress.stackexchange.com/questions/35773/order-by-second-word-in-title/35784#35784


Hai Bui comments:

yeah, we can use posts_orderby hook, I didn't think about that. This will work:
add_filter( 'posts_orderby', 'custom_order_by' );

function custom_order_by() {
global $wpdb;
return "LENGTH({$wpdb->posts}.post_title) ASC, {$wpdb->posts}.post_title ASC";
}


web559 comments:

Those are dummy titles in my question—the actual titles are like this:

shortString | Note 1.27 | Any length title here
shortString | Image 1W | Any length title here

So could it be done with some variation on this? (This wouldn't work but perhaps SUBSTRING and CHARINDEX would be used)

return "SUBSTRING({$wpdb->posts}.post_title, CHARINDEX(' | ', ' ') + 1, 100)


Hai Bui comments:

You should have told me earlier. It's totally different and much harder to solve...


web559 comments:

Can't something be done by finding the second (last) instance of " | "? That format is always consistent.

Suppose you start with these titles:
shortString | Note 1.27 | Any length title here
shortString | Image 1W | Any length title here

1. Get substring starting after the " | ":
Note 1.27 | Any length title here
Image 1W | Any length title here

2. Get substring starting after the first " ":
1.27 | Any length title here
1W | Any length title here

3. Get substring up to the first " ":
1.27
1W

4. Replace "." with "":
127
1W

5. (I don't know how to do this part:) If there are 3 characters left, insert a "0" (zero) after the first character.
127
10W
(This would cause a 1.4 to be changed to a 104, and a 1AX would be left as 1AX.)

Then the strings would be in alphabetical order. Is there a way to do step 5 in SQL?

2012-09-06

Duncan O'Neill answers:

I think WP_Query will work. All you need is a filter on the pre_get_posts function, from these bare bones;


<?php

function xxx_alter_posts($query){

// do your stuff in here below an example
if (is_archive)
$query->set( 'author', '-5');
}

add_action( 'pre_get_posts', 'xxx_alter_posts' );

?>


I haven't yet figured out the details of how you'd manipulate the return set within the function ( working on it ), but I'd be surprised if it couldn;t be done.

best,


web559 comments:

As a note, those are dummy titles in my question—the actual titles are like this:

shortString | Note 1.27 | Any length title here
shortString | Image 1W | Any length title here

See my note with Hai above, for a possible query idea.


Duncan O'Neill comments:

Yep, already understood, thanks.