Title: Reverse wordpress SQL CASE Expression
Last modified: September 28, 2018

---

# Reverse wordpress SQL CASE Expression

 *  [madebymt](https://wordpress.org/support/users/madebymt/)
 * (@madebymt)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/)
 * Hi everyone, I was working on the search result and got some answers from the
   support, so I add the code inside the function.php which is working fine, but
   the search result is kind of odd.
 * **Here’s the code:**
 *     ```
       add_filter( 'posts_orderby', 'order_search_by_posttype', 10, 2 );
       function order_search_by_posttype( $orderby, $wp_query ){
           if( ! $wp_query->is_admin && $wp_query->is_search ) :
               global $wpdb;
               $orderby =
                   "
                   CASE WHEN {$wpdb->prefix}posts.post_type = 'page' THEN '1' 
                        WHEN {$wpdb->prefix}posts.post_type = 'post' THEN '2' 
                   ELSE {$wpdb->prefix}posts.post_type END ASC, 
                   {$wpdb->prefix}posts.post_title ASC";
           endif;
           return $orderby;
       }
       ```
   
 * **1. How do I hide homepage on the search result?
    2. My first search result 
   list only had a title no excerpt. why? 3. Can this reverse? Will this really 
   affect WordPress DB? if I take the code out will it back to original?
 * Thank you so much for the help.

Viewing 11 replies - 1 through 11 (of 11 total)

 *  [Prashant Singh](https://wordpress.org/support/users/prashantvatsh/)
 * (@prashantvatsh)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10732658)
 * 1. Yes, we can remove the homepage from the search like this:
 *     ```
       function exclude_home_from_search($query) {
         if ( !is_admin() && $query->is_main_query() ) {
           if ($query->is_search) {
             $query->set('post__not_in', array('home_page_id'));
           }
         }
       }
   
       add_action('pre_get_posts','exclude_home_from_search',11,1);
       ```
   
 * In place of home_page_id please write the ID of the homepage.
 * 2. It depends on the search page template your theme providing, they must have
   not included the excerpt. You can check in their demo once.
 * 3. Yes, everything will be back to original if you will remove the code.
 * Thanks
 *  Thread Starter [madebymt](https://wordpress.org/support/users/madebymt/)
 * (@madebymt)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10733148)
 * [@prashantvatsh](https://wordpress.org/support/users/prashantvatsh/)
 * Thank you so much for answers my question.
    Is possible add that function in 
   the WordPress SQL CASE Expression function I had? I not quite sure how I add 
   it in.
 * Thanks!
    -  This reply was modified 7 years, 8 months ago by [madebymt](https://wordpress.org/support/users/madebymt/).
 *  Thread Starter [madebymt](https://wordpress.org/support/users/madebymt/)
 * (@madebymt)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10733673)
 * Tried to add this after if statement, but it’s not working
 * Thanks!
 *     ```
       add_filter( 'posts_orderby', 'order_search_by_posttype', 10, 2 );
       function order_search_by_posttype( $orderby, $wp_query ){
         if( ! $wp_query->is_admin && $wp_query->is_search ) :
           $wp_query->set( 'post__not_in', array( pade_id ) ); 
               global $wpdb;
               $orderby =
                   "
                   CASE WHEN {$wpdb->prefix}posts.post_type = 'page' THEN '1' 
                        WHEN {$wpdb->prefix}posts.post_type = 'post' THEN '2' 
                   ELSE {$wpdb->prefix}posts.post_type END ASC, 
                   {$wpdb->prefix}posts.post_title ASC";
           endif;
           return $orderby;
       }
       ```
   
 * Thanks!
    -  This reply was modified 7 years, 8 months ago by [madebymt](https://wordpress.org/support/users/madebymt/).
    -  This reply was modified 7 years, 8 months ago by [madebymt](https://wordpress.org/support/users/madebymt/).
 *  [Prashant Singh](https://wordpress.org/support/users/prashantvatsh/)
 * (@prashantvatsh)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10733885)
 * `$wp_query->set( 'post__not_in', array( pade_id ) );`
 * I have asked to write page id and that’s integer id, you have to go in dashboard
   and click on edit page(obviously for front page only) then in URL you can see
   that id and also write it in single quotes like this:
 * `$wp_query->set( 'post__not_in', array( 'your page id here') );`
 * If you are still not able to figure out the page id then please activate this
   plugin [https://wordpress.org/plugins/reveal-ids-for-wp-admin-25/](https://wordpress.org/plugins/reveal-ids-for-wp-admin-25/)
   and go to pages in dashboard and check what’s the ID there on frontpage.
 * Thanks
 *  Thread Starter [madebymt](https://wordpress.org/support/users/madebymt/)
 * (@madebymt)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10735946)
 * Hi [@prashantvatsh](https://wordpress.org/support/users/prashantvatsh/)
    I tried
   to put the code in the function, got the post id form the backend, but it’s still
   not working. Did I miss something?
 * `$wp_query->set( 'post__not_in', array( '20') );`
 * Thank you so much!
 *  [Prashant Singh](https://wordpress.org/support/users/prashantvatsh/)
 * (@prashantvatsh)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10737672)
 *     ```
       function exclude_home_from_search($query) {
         if ( !is_admin() && $query->is_main_query() ) {
           if ($query->is_search) {
             $query->set('post__not_in', array('20'));
           }
         }
       }
   
       add_action('pre_get_posts','exclude_home_from_search',9999,1);
       ```
   
 * Can you paste this directly, instead of pasting a part in that function, please.
 * Thanks
 *  Thread Starter [madebymt](https://wordpress.org/support/users/madebymt/)
 * (@madebymt)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10737886)
 * [@prashantvatsh](https://wordpress.org/support/users/prashantvatsh/)
 * Thank you so much for answer my question and be so patient with me.
    Is possible
   make your function to make search result show page first then post? That because
   I’m use SQL CASE expression.
 * Thank you again.
 *  Thread Starter [madebymt](https://wordpress.org/support/users/madebymt/)
 * (@madebymt)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10741327)
 * If anyone has any ideas, please help! Thank you all.
 *  [Prashant Singh](https://wordpress.org/support/users/prashantvatsh/)
 * (@prashantvatsh)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10741606)
 * Hi,
 * No, because both have different hooks and both have to return different results.
   So you have to use them separately.
 * One of the simple solution to get pages first will be this: [https://wordpress.stackexchange.com/questions/41691/how-do-i-filter-the-search-results-order](https://wordpress.stackexchange.com/questions/41691/how-do-i-filter-the-search-results-order)
 * Thanks
 *  Thread Starter [madebymt](https://wordpress.org/support/users/madebymt/)
 * (@madebymt)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10748122)
 * [@prashantvatsh](https://wordpress.org/support/users/prashantvatsh/)
 * Thank you so much for your help, seriously!
    I put your function before the DB
   search filter, works perfectly.
 * Thank you so much taking your time answering my questions.
 *  [Prashant Singh](https://wordpress.org/support/users/prashantvatsh/)
 * (@prashantvatsh)
 * [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10751166)
 * Pleasure is all mine 🙂

Viewing 11 replies - 1 through 11 (of 11 total)

The topic ‘Reverse wordpress SQL CASE Expression’ is closed to new replies.

## Tags

 * [database](https://wordpress.org/support/topic-tag/database/)
 * [function](https://wordpress.org/support/topic-tag/function/)
 * [query](https://wordpress.org/support/topic-tag/query/)

 * In: [Fixing WordPress](https://wordpress.org/support/forum/how-to-and-troubleshooting/)
 * 11 replies
 * 2 participants
 * Last reply from: [Prashant Singh](https://wordpress.org/support/users/prashantvatsh/)
 * Last activity: [7 years, 8 months ago](https://wordpress.org/support/topic/reverse-wordpress-sql-case-expression/#post-10751166)
 * Status: not resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics