Query for posts based on first letter of post title

[salesman]

Hi,

I have >100 custom post type articles in Metabox that serve as dictionary articles. I now want to build one page which links all articles based on their initial letters in the title (aka a table of content for this dictionary)

Example

Articles starting with A
Article A1
Article A2
Article A3

Articles starting with B
Article B1
Article B2
Article B3

My options

1. Create a custom field and add the initial letter there. Then create 26 different sections. Sounds not right
2. Custom query (I cannot figure out how to use a regex or sth similar in the query)

Can one point me to the right direction?

Thank you!

[aslotta]

I think your best bet is creating a custom query type / custom query types.

A starting point:

1. Create a helper function to fetch the posts from the DB (adjust the parameters like post_type to your needs).

function get_dictionary_posts( $letter ) {
    global $wpdb;
    return $wpdb->get_results( "
        SELECT * FROM {$wpdb->prefix}posts
        WHERE post_type = 'post' AND post_status = 'publish' AND post_title LIKE '{$letter}%'
        ORDER BY post_title ASC
    ", OBJECT );
}

2. Create query types for each letter.

add_filter( 'bricks/setup/control_options', function( $control_options ) {
    foreach ( range( 'A', 'Z' ) as $letter ) {
        $control_options['queryTypes']["dictionary_{$letter}"] = esc_html__( "Dictionary ({$letter})", 'as' );
    }
    
    return $control_options;
} );

3. Extract the letter from the query type and call the helper function to fetch the matching posts.

add_filter( 'bricks/query/run', function( $results, $query_obj ) {
    if ( strpos( $query_obj->object_type, 'dictionary_' ) !== 0 ) {
        return $results;
    }
    
    $letter = explode( '_', $query_obj->object_type )[1];
    $results = get_dictionary_posts( $letter );
    
    return $results;
}, 10, 2 );

4. Add the remaining Bricks hooks to make the custom queries work as expected.

add_filter( 'bricks/query/loop_object', function( $loop_object, $loop_key, $query_obj ) {
    if ( strpos( $query_obj->object_type, 'dictionary_' ) !== 0 ) {
        return $loop_object;
    }
    
    global $post;
    $post = $loop_object;
    setup_postdata( $post );
    
    return $loop_object;
}, 10, 3 );

add_filter( 'bricks/query/loop_object_type', function( $object_type, $object, $query_id ) {
    $query_object_type = \Bricks\Query::get_query_object_type( $query_id );
    
	if ( strpos( $query_object_type, 'dictionary_' ) !== 0 ) {
		return $object_type;
	}
	
    return 'post';
}, 10, 3 );

Hope it helps as a starting point.

[salesman]

Wow, great input. Thank you so much, I will work through it!

[aslotta]

I created another version with just two query types: One for the letters (outer query) and another one for the posts starting with a specific letter (inner query). So you just need to nest two query loops like this:

To output the letter in the outer (letters) query you can use the custom get_letter function like this:

And this is the full code that is needed. Hope it helps.

function get_posts_starting_with_letter( $letter ) {
    global $wpdb;
    return $wpdb->get_results( "
        SELECT * FROM {$wpdb->prefix}posts
        WHERE post_type = 'post' AND post_status = 'publish' AND post_title LIKE '{$letter}%'
        ORDER BY post_title ASC
    ", OBJECT );
}

function get_letter() {
    if ( \Bricks\Query::get_loop_object_type() !== 'letters' ) {
        return false;
    }
    
    return \Bricks\Query::get_loop_object();
}

add_filter( 'bricks/setup/control_options', function( $control_options ) {
    $control_options['queryTypes']["letters"] = esc_html__( "Letters", 'as' );
    $control_options['queryTypes']["letter_posts"] = esc_html__( "Posts (Current Letter)", 'as' );
    
    return $control_options;
} );

add_filter( 'bricks/query/run', function( $results, $query_obj ) {
    switch ( $query_obj->object_type ) {
        case 'letters':
            $results = range( 'A', 'Z' );
            break;
        case 'letter_posts':
            if ( $letters_query_id = \Bricks\Query::is_any_looping() ) {
                $letter = \Bricks\Query::get_loop_object( $letters_query_id );
                $results = get_posts_starting_with_letter( $letter );
            }
            break;
    }
    
    return $results;
}, 10, 2 );

add_filter( 'bricks/query/loop_object', function( $loop_object, $loop_key, $query_obj ) {
    if ( $query_obj->object_type !== 'letter_posts' ) {
        return $loop_object;
    }
    
    global $post;
    $post = $loop_object;
    setup_postdata( $post );
    
    return $loop_object;
}, 10, 3 );

add_filter( 'bricks/query/loop_object_type', function( $object_type, $object, $query_id ) {
    $query_object_type = \Bricks\Query::get_query_object_type( $query_id );
    
    switch ( $query_object_type ) {
        case 'letters':
            return $query_object_type;
        case 'letter_posts':
            return 'post';
    }
	
    return $object_type;
}, 10, 3 );

[salesman]

Thank you for providing the input!

It is not yet working, (at least the custom queries are not showing up in the UI) so I am trying to understand the script better to understand my error.

1. I changed the post_type = ‘b2b-sales-dictionary’ as registered through Metaxbox.

From Metabox: register_post_type( ‘b2b-sales-dictionary’, $args );

2. Can I add function and filter all into one PHP snippet? I would think so. Any caveat with all the semicolons?
3. What does the second parameter in 10, 2 do?

• $query_obj is an instance of the \Bricks\Query class object

Thank you

[aslotta]

1. Changing the post_type is perfectly fine – actually it is needed if you want to fetch a CPT instead of regular posts.
2. Yes. I added all the code within one snippet (created with WPCodeBox).
3. The second argument (which is 1 by default) has to match the number of arguments the function is retrieving. See add_filter() | Function | WordPress Developer Resources.

[bjoern]

Thanks a lot for the code!
However, for some reason the outer query does not show the letters.

Any ideas?

Many thanks!

[aslotta]

Hey @bjoern,

guess you have to explicitly whitelist the custom function as described here.

Best,

André

[bjoern]

Hi @aslotta ,

Thanks a lot for the quick reply!
Yes, that was it!
At first I thought it wasn’t the solution, because I had saved it in WP Code Box 2 after your message and it did not work.
But then I saved it directly in the Bricks child theme and it worked. Many, many thanks! Now I can avoid 25 loops on the page

Just 2 more questions:

1. What is ‘as’ in this line? Is it optional?
   $control_options[‘queryTypes’][“letters”] = esc_html__( “Letters”, ‘as’ );

2. How can I ensure that the quotation marks at the beginning of sources are ignored?
   E.G.
   B1 Source
   “B2 Source”
   B3 Source

EDIT: Found the answer to question 2 - you just have to change the function get_posts_starting_with_letter:

function get_posts_starting_with_letter( $letter ) {
    global $wpdb;
    return $wpdb->get_results( $wpdb->prepare("
        SELECT * FROM {$wpdb->prefix}posts
        WHERE post_type = 'post' // has to be adapted if CPT
        AND post_status = 'publish' 
        AND (
            post_title LIKE %s
            OR post_title LIKE %s
        )
        ORDER BY post_title ASC
    ", 
    $letter . '%',
    '"' . $letter . '%'
    ), OBJECT );
}