Styjun

I added the column with the highest value of the offer. And then I would like to make it sortable. I've prepared this:

// asl this will add extra column in the product list

add_filter( 'manage_edit-product_columns', array($this,'show_product_offers_amounts'),15 );
add_action( 'manage_product_posts_custom_column', array($this,'show_product_offers_amount_max'), 10, 2 );

// asl show the column

function show_product_offers_amounts($columns)
 $columns['orig_offer_amount'] = 'Amount';
 return $columns;
 

// asl add the datas to column

function show_product_offers_amount_max( $column, $postid ) 
 global $wpdb;
 if ( $column == 'orig_offer_amount' ) 
 $offers_max = $wpdb->get_var( " select max(meta_value)
 from ".$wpdb->postmeta."
 where meta_key='orig_offer_amount'
 and meta_value!=''
 and post_id in(
 select p.post_id
 from ".$wpdb->postmeta." as p
 where p.meta_key='orig_offer_product_id' and
 p.meta_value=".$postid.")" 
 );

 if($offers_max > 0)
 echo '<mark style="color: #3973aa;font-size: 13px;font-weight: 500;background: 0 0;line-height: 1;">'.$offers_max.'</mark>';
 
 else
 echo '<span class="na">–</span>';
 
 

 

// asl register the column as sortable

function price_column_register_sortable( $columns ) 
 $columns['orig_offer_amount'] = 'orig_offer_amount';

 return $columns;

add_filter( 'manage_edit-product_sortable_columns', 'price_column_register_sortable' );
function price_column_orderby( $vars ) 
 if ( isset( $vars['orderby'] ) && 'price' == $vars['orderby'] ) 

 $offers_max = $wpdb->get_var( " select max(meta_value)
 from ".$wpdb->postmeta."
 where meta_key='orig_offer_amount'
 and meta_value!=''
 and post_id in(
 select p.post_id
 from ".$wpdb->postmeta." as p
 where p.meta_key='orig_offer_product_id' and
 p.meta_value=".$postid.")" 
 );

 if($offers_max > 0)
 $offers_max = $offers_max;
 
 else
 $offers_max = 0;;
 


 $vars = array_merge( $vars, array(
 'meta_key' => 'orig_offer_amount',
 'orderby' => $offers_max
 ) );
 

 return $vars;

add_filter( 'request', 'price_column_orderby' );

This code makes that wordpress recognize the column as sortable. But it doesn't sort properly.

Any idea?

Using LoicTheAztec suggestion I prepared something like this:

add_action( 'save_post', 'new_postmeta_to_products' );
function new_postmeta_to_products($post_id)
 $post_type = get_post_type($post_id);

 if($post_type == 'products') 

 global $wpdb;
 $maxId = $wpdb->get_var( " select post_id
 from ".$wpdb->postmeta."
 where meta_key='orig_offer_amount'
 and meta_value!=''
 and post_id in(
 select p.post_id
 from ".$wpdb->postmeta." as p
 where p.meta_key='orig_offer_product_id' and
 p.meta_value=".$post_id.")
 order by meta_value desc limit 1" 
 ); 

 add_post_meta($post_id,'offer_max_id',$maxId);
 

But it doesn't work :( maybe because $post_id

Thanks to LoicTheAztec's hints I've prepared some snippet. First I've added new postmeta for product, when is created:

add_post_meta($post_id, 'offers_max_id', null);
add_post_meta($post_id, 'offers_max_value', null);
add_post_meta($post_id, 'offers_count', null);

Then, a few lines to set the new postmeta when an offer is created in www/wp-content/plugins/offers-for-woocommerce/public/class-offers-for-woocommerce.php in function new_offer_form_submit() :

global $post;
$_product = get_post_meta($parent_post_id, 'offer_product_id', true);

global $wpdb;
$offerMax = $wpdb->get_results( " select post_id, meta_value
 from ".$wpdb->postmeta."
 where meta_key='orig_offer_amount'
 and meta_value!=''
 and post_id in(
 select p.post_id
 from ".$wpdb->postmeta." as p
 where p.meta_key='orig_offer_product_id' and
 p.meta_value=".$_product.")
 order by meta_value desc limit 1" 
 ,ARRAY_N); 
update_post_meta($_product, 'offers_max_id', $offerMax[0][0]);
update_post_meta($_product, 'offers_max_value', $offerMax[0][1]);

 $offerCount = $wpdb->get_var( "select count(post_id) 
 from ".$wpdb->postmeta." 
 where meta_key='orig_offer_product_id' and
 meta_value=".$_product
 );
 update_post_meta($_product, 'offers_count', $offerCount);

Then, we must add new columns for products' admin panel in function.php:

// asl show the column
function my_cpt_columns( $columns ) 
 $columns["offermaxid"] = "OfferId";
 $columns["offercount"] = "Offers";
 $columns["offermaxvalue"] = "Amount";
 $columns["dateofend"] = "EndTime";
 return $columns;

add_filter('manage_edit-product_columns', 'my_cpt_columns');

// asl take a data to the column
function my_cpt_column( $colname, $cptid ) 
 if ( $colname == 'offermaxid')
 echo get_post_meta( $cptid, 'offers_max_id', true );
 if ( $colname == 'offercount')
 echo get_post_meta( $cptid, 'offers_count', true ); 
 if ( $colname == 'offermaxvalue')
 echo get_post_meta( $cptid, 'offers_max_value', true ); 
 if ( $colname == 'dateofend')
 echo date("j M y H:i:s", (get_post_meta( $cptid, '_sale_price_dates_to', true )+60*60) );
 if ( $colname == 'offerlefttime') 
 $atime = time();
 $d = (get_post_meta( $cptid, '_sale_price_dates_to', true ) - $atime)/(60*60*24);
 $h = ( get_post_meta( $cptid, '_sale_price_dates_to', true ) - $atime - ( (60*60*24) * explode(".", $d)[0] ) )/(60*60);
 $m = ( get_post_meta( $cptid, '_sale_price_dates_to', true ) - $atime - ( (60*60*24) * explode(".", $d)[0] ) - ( (60*60) * explode(".", $h)[0] ) )/(60);
 echo floor($d) . "d " . floor($h) . "h " . floor($m) . "m";
 

add_action('manage_product_posts_custom_column', 'my_cpt_column', 10, 2);

// asl sortowanie
add_filter('manage_edit-product_sortable_columns', 'my_cpt_columns');
function my_sort_metabox( $vars ) 
 if( array_key_exists('orderby', $vars )) 
 if('OfferId' == $vars['orderby']) 
 $vars['orderby'] = 'meta_value';
 $vars['meta_key'] = 'offers_max_id';
 
 if('Offers' == $vars['orderby']) 
 $vars['orderby'] = 'meta_value';
 $vars['meta_key'] = 'offers_count';
 
 if('Amount' == $vars['orderby']) 
 $vars['orderby'] = 'meta_value';
 $vars['meta_key'] = 'offers_max_value';
 
 if('EndTime' == $vars['orderby']) 
 $vars['order'] = 'ASC';
 $vars['orderby'] = 'meta_value';
 $vars['meta_key'] = '_sale_price_dates_to';
 
 
 return $vars;

add_filter('request', 'my_sort_metabox');

// asl dodanie kolumny wynikowej z czasem do końca
function my_cpt_column_time( $columns ) 
 $columns["offerlefttime"] = "LeftTime";
 return $columns;

add_filter('manage_edit-product_columns', 'my_cpt_column_time');

And everything nice but one thing doesn't work. I want to have the products sort by column 'EndTime' ASC.
Any suggestions?