MYSQL: Query the last conversion on chat messages and sort by latest message

I am having issue on query sorting of last convo messages of each users. Here is my current record on my 2 tables:

enter image description here

enter image description here

I want to achieve below result:

enter image description here

My current query is:

 SELECT `messages`.*, `users`.`username`, `u2`.`username` as `to_username`,  CASE WHEN to_user_id = 1     THEN `users`.`username`     ELSE u2.username  END as participant  FROM `messages`   LEFT JOIN `users` ON `messages`.`from_user_id`=`users`.`id`   LEFT JOIN `users` as `u2` ON `messages`.`to_user_id`=`u2`.`id`   INNER JOIN (SELECT max(id) as lastmsgId FROM messages where to_user_id = 1 or from_user_id =1 GROUP BY to_user_id) m2 ON `messages`.`id`=`m2`.`lastmsgId`   GROUP BY participant    ORDER BY `messages`.`created_at` DESC 

And the result of my query is: enter image description here

The 4th and 5th message is not sorted properly I am expecting to display the 4th message instead of 5th message. It displays the older message instead of latest one. I couldn’t find the solution on how will be display the right sorting.

Any help and suggestion is very much appreciated.

Postgresql: sort by value position in array column, then by secondary order

I’m not quite sure what the best way to phrase this is…

So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from most important to providing that value for their clients, to least important.

I’m using PostGIS to query providers in a specific area, and want to return providers ordered first by the pillar that a client selected they were looking for, then by closest location.

so if the pillars all have values ['a', 'b', 'c', 'd'], in any order depending on what providers selected, and the client selected pillar c

the results of the query would preferably return any/all providers that have pillar c at array index 0 first, ordered by distance to geopoint, then by providers that have pillar c at array index 1 second ordered by distance to client geopoint, then idx 2, then idx 3

I’m really only looking for the top 3 results in all cases, and providers with pillar c at idx 1 would only be needed if there were less than 3 results for index 0

Is this possible to pull off in a single query? or should I just run it with a where clause and check the results length until I have 3 results?

The pillars column is indexed with a gin index btw

sort CPT on Custom Taxonomy Archive page

I have created a custom post type called "al_product" and attached to it a custom taxonomy called "product_category".

In my archive-product_category.php I have this (relevant) code:

            if ( $  termchildren  ) {                 // we have kids...just show the terms.                            $  i=1;                 foreach ( $  termchildren as $  child ) {                     if ($  i % 2 == 0) {                         echo '<div class="tax-entry flex">';                     } else {                         echo '<div class="tax-entry flex" style="flex-direction: row-reverse;">';                     }                     ?>                         <figure class="wp-block-media-text__media">                             <?php if ( get_field('product_category_img', $  child) ) echo wp_get_attachment_image( get_field('product_category_img', $  child), 'full' ) ;?>                         </figure>                         <div style="flex:60% 0 0;" class="wp-block-media-text__content">                             <h2><?php echo $  child->name ;?></h2>                             <p>                                 <?php                                 if (get_field('short_description', $  child )){                                      echo get_field('short_description', $  child );                                 }else{                                     echo $  child->description;                                 }?>                             </p>                             <?php                             // Get (at most) 2 "product" posts in the child term.                             $  posts = get_posts( array(                                 'post_type'      => 'al_product',                                 'posts_per_page' => 2,                                 $  taxonomy_name   => $  child->slug,                             ) );                              // If there's exactly 1 post, use the post permalink.                             if ( 1 === count( $  posts ) ) : ?>                                 <a class="button" href="<?php the_permalink( $  posts[0] ); ?>">See this Product</a>                             <?php // Else, use the term link.                             else : ?>                                 <a class="button" href="<?php echo esc_url( get_term_link( $  child, $  taxonomy_name ) ); ?>">See this Series</a>                             <?php endif; ?>                          </div>                     </div> <!-- END FLEX -->                     <?php                     $  i++;                 }             } else {                  // no the products                 echo '<div class="productCategories grid">';                 while ( have_posts() ) : the_post();                     ?>                      <div class="product_cat">                         <a href="<?php the_permalink();?>">                             <?php the_post_thumbnail('small');?>                             <h2><?php the_title();?></h2>                             <?php                             $  subtitle = get_field('subtitle');                             if ( $  subtitle ) {                              echo '<h3 class="entry-subtitle">'.$  subtitle.'</h3>';                             }                             ?>                         </a>                         <?php //if (get_field('specs')['part_number']) echo '<span>'.get_field('specs')['part_number'].'</span>';?>                     </div>                  <?php                 endwhile;                 echo '</div>';             } 

The section I’m questioning is when there are no kids so we show products.

Currently this is returning the products correctly but sorting by title. I would like to sort by menu_order.

I added this to my functions.php:

add_action( 'pre_get_posts', 'rt_change_product_sort_order');  function rt_change_product_sort_order($  query){     if(get_query_var( 'product_category' )):        //Set the order ASC or DESC        $  query->set( 'order', 'DESC' );        //Set the orderby        $  query->set( 'orderby', 'menu_order' );     endif;     }; 

To no avail. I’ve also tried replacing the "If" statement with each of these:

if(is_post_type_archive( 'product_category' )): if(get_query_var( 'product_category' )): if(get_query_var( 'al_product' )): 

What am I missing here?

Is there any sort of precedent for training to learn / being taught Feats?

I’m interested in acquiring a specific Feat more quickly that just from levelling. It’s a Mastery Feat for the weapon I’ve just portentously received and am proficient with, but would like to become excellent with.

I know that you can gain proficiencies in entire weapons classes as "training", but that’s not quite the same as learning a Feat.

Is there an established process for stuff like this? Naturally, I’m going to talk to my DM about it, but I’m curious to know whether there’s any precedent for it.

Some specifics:

  • Character: Half-Orc Barbarian.
  • Weapon: Spear. (With bonus flavour stuff, but mechanically just a spear most of the time)
  • Feat: Either Polearm Master (errata’d to include Spears) or Spear Mastery (from UA)

How to display products sort by Tags for Woocommerce

I have tried to display products by tags on shop page, i found this code and it workes perfectly

            $  args = array(                 'number'     => $  number,                 'orderby'    => 'title',                 'order'      => 'ASC',                 'hide_empty' => $  hide_empty,                 'include'    => $  ids             );             $  product_tags = get_terms( 'product_tag', $  args );             $  count = count($  product_tags);             if ( $  count > 0 ){                 foreach ( $  product_tags as $  product_tag ) {                     echo '<h4>' . $  product_tag->name . '</a></h4>';                     $  args = array(                         'posts_per_page' => -1,                         'tax_query' => array(                             'relation' => 'AND',                             array(                                 'taxonomy' => 'product_tag',                                 'field' => 'slug',                                 // 'terms' => 'white-wines'                                 'terms' => $  product_tag->slug,                                 'product_tag'=> "latest-product"                             )                         ),                         'post_type' => 'product',                         'orderby' => 'title,'                     );                     $  products = new WP_Query( $  args );                     woocommerce_product_loop_start();                     while ( $  products->have_posts() ) {                         $  products->the_post();                         do_action( 'woocommerce_shop_loop' );                         wc_get_template_part( 'content', 'product' );                         ?>                                                      <?php                     }                     woocommerce_product_loop_end();                 }             }          ?> 

But when I tried to filter resaults it wont apply filters, by the way i m using Annasta Filter Plugin


Does mysql “order by” guarantees to give the same order for the same query every time if the sort key is equal?

I’m planning to write a query that sorts the result by a value s and then paginate the result. Let’s say I have ten items that match the query and all the items have the same s. In the first query, I sort the value by s and get the first five items. Then in the next query, I sort the value by s and get the sixth to tenth items. Is it possible that the items that appear on my first query will appear again in my second query, and that some items will not show up in either query?

How to sort wordpress posts already selected by WP_QUERY

English is not my first language, sorry 🙂

I need differend per paginate page sorting.

For exemple, I created the loop:

 $  qargs = array(                     'posts_per_page' => 15,                     'no_found_rows'  => true,                     'order' => 'DESC',                     'post_type' => 'post',                     'suppress_filters' => false,                     'orderby' => 'post_views', // here I order my posts by views                     'fields' => '',                     'date_query' => array(                         array(                             'after'     => '-7 days',                             'column' => 'post_date',                         ),                     ), posts_query = new WP_Query( $  qargs ); 

And now I can display 15 selected posts sorted by views like this:

if ( $  posts_query->have_posts() ): ?>                 while ( $  posts_query->have_posts() ):                     $  posts_query->the_post(); ....... 

But I need to select 15 most viewed posts and after that to sort them by date.

Is it possible?

Thank you!

Sort Records in Original Order After UNION ALL

I have a source table including column StartTime, EndTime, and a couple of other columns as you can see below.

My goal is to create a query that returns a dataset in which StartTime and EndTime are merged into a single Time column (so that each record appears twice, once with the StartTime as Time, once with the EndTime as Time).

I can easily achieve this with a subquery returning StartTime for the Time column, another subquery returning the EndTime for the Time column, the merge the results by UNION ALL. However, I can’t seem to figure out how to sort the new dataset so that the order is

  • This record, StartTime as Time
  • This record, EndTime as Time
  • Next record, StartTime as Time
  • Next record, EndTime as Time

and so on……

Your help would be greatly appreciated!

Sample below – Thank you!

Source Table: (LogID is an auto-increment PK in no particular order, so it can’t be used for sorting)

LogID Description StartTime EndTime
1 Travel to new site 2019-7-31 05:30 2019-07-31 06:30
2 Meeting 2019-07-31 06:30 2019-07-31 07:00
3 Presentation 2019-07-31 07:00 2019-07-31 7:30
4 Travel to new site 2019-07-31 7:30 2019-07-31 12:00
5 Setup 2019-07-31 12:00 2019-07-31 13:15

Desired Result:

LogID Description Time
1 Travel to new site 2019-7-31 05:30
1 Travel to new site 2019-07-31 06:30
2 Meeting 2019-07-31 06:30
2 Meeting 2019-07-31 07:00
3 Presentation 2019-07-31 07:00
3 Presentation 2019-07-31 7:30
4 Travel to new site 2019-07-31 7:30
4 Travel to new site 2019-07-31 12:00
5 Setup 2019-07-31 12:00
5 Setup 2019-07-31 13:15

Sort custom post type by custom taxonomy

Im using this code to sort my custom post type by one of its custom taxonomies:

   <?php         $  valores = get_terms('valor', array('order' => 'DESC',));         foreach($  valores as $  valor) {             $  posts = get_posts(array(                     'post_type' => 'propriedade',                     'tax_query' => array(                         array(                             'taxonomy' => 'valor',                             'field' => 'slug',                             'terms' => $  valor->slug                             )                         ),                     'numberposts' => -1                 ));             foreach($  valores as $  valor) {                 print_r($  valor);             }         }     ?> 

but the list seems not to be ordered as DESC:

WP_Term Object (     [term_id] => 28     [name] => 40000     [slug] => 40000     [term_group] => 0     [term_taxonomy_id] => 28     [taxonomy] => valor     [description] =>      [parent] => 0     [count] => 1     [filter] => raw ) WP_Term Object (     [term_id] => 32     [name] => 2000000     [slug] => 2000000     [term_group] => 0     [term_taxonomy_id] => 32     [taxonomy] => valor     [description] =>      [parent] => 0     [count] => 1     [filter] => raw ) WP_Term Object (     [term_id] => 27     [name] => 2000     [slug] => 2000     [term_group] => 0     [term_taxonomy_id] => 27     [taxonomy] => valor     [description] =>      [parent] => 0     [count] => 1     [filter] => raw ) 

What I need is to make name or slug start from the lowest value to the higher value.

Search results sort order failing: set by date only

Hi the default wordpress search seems to be for "piano"

 SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%piano%') OR (wp_posts.post_excerpt LIKE '%piano%') OR (wp_posts.post_content LIKE '%piano%'))) AND wp_posts.post_type IN ('post', 'page', 'attachment') AND (wp_posts.post_status = 'publish' OR wp_posts.post_author = 5 AND wp_posts.post_status = 'private') ORDER BY wp_posts.post_title LIKE '%piano%' DESC, wp_posts.post_date DESC LIMIT 0, 25  

however I need them all sorted by date first. My search results by date are skewed:

2020-10-25   2020-10-22   2020-10-20   2020-10-20   2020-10-16   2020-10-13   2020-10-10   2020-10-07   2020-10-06   2020-10-02   2020-09-30   2020-09-28   2020-09-02   2020-08-09   2020-07-21   2020-07-21   2020-05-21   2020-04-24   2020-03-06   2019-06-02  <- here it is skewed onwards 2019-06-01   2020-10-25   2020-10-24   2020-10-23   2020-10-23 

So I asume it is because the SQL query sorts by title first.

So I tried this solution:

 function changeSearchSort( $  orderby, $  query ){     global $  wpdb;      if(!is_admin() && is_search()) {         $  orderby =  $  wpdb->prefix."posts.post_date ASC";     }     return  $  orderby; } add_filter('posts_orderby','changeSearchSort',10,2);  

from here How do I change WP search results order?

and it does not work.

I have tried this:
a) disabled all plugins
b) different themes

still not working.

Help appreciated