Support

Search results for ""

Sorry, no results found. Perhaps you would like to search the documentation?
All Topics
Tony Gonzalez

Custom Field Date Column smart filtering between dates.

Hi,
I’m trying to add a (smart)filterable date column to woo commerce for “Date Completed”.

The data is stored in the _date_completed custom field – but its stored as a unix timestamp. I know AC likes ymd format.

I’ve made a custom field plugin and it shows, but the smart filter doent work. Always no results.

Do you have – or can you supply – code example for using the between smart filter?

I tried this code:

<?php
namespace net1on1_name\SmartFiltering;
class order_completed extends \ACP\Search\Comparison {
	public function __construct() {
		$operators = new \ACP\Search\Operators( [
			\ACP\Search\Operators::EQ,
			\ACP\Search\Operators::BETWEEN,
		] );
		$value = \ACP\Search\Value::DATE;
		parent::__construct( $operators, $value );
	}

	protected function create_query_bindings( $operator, \ACP\Search\Value $value ) {
		$binding = new \ACP\Search\Query\Bindings\Post();
		if (strtolower($operator) == "between") {
			$binding->meta_query( [
				'key'     => '_date_completed',
				'value'   => $value->get_value(),
				'compare' => 'BETWEEN',
				'type'    => 'DATE'
			] );
		} else {
			$binding->meta_query( [
				'key'     => '_date_completed',
				'value'   => $value->get_value(),
				'compare' => '=',
				'type'    => 'DATE'
			] );
		}
		return $binding;
	}
}

And then this

<?php
namespace net1on1_name\SmartFiltering;
class order_completed extends \ACP\Search\Comparison {
	public function __construct() {
		$operators = new \ACP\Search\Operators( [
			\ACP\Search\Operators::EQ,
			\ACP\Search\Operators::BETWEEN,
		] );
		$value = \ACP\Search\Value::DATE;
		parent::__construct( $operators, $value );
	}

	protected function create_query_bindings( $operator, \ACP\Search\Value $value ) {
		$binding = new \ACP\Search\Query\Bindings\Post();
		global $wpdb;
		$binding->join( " INNER JOIN {$wpdb->postmeta} AS my_postmeta ON {$wpdb->posts}.ID = my_postmeta.post_id" );
		if (strtolower($operator) == "between") {
			$v = $value->get_value();
			$d1 = $v[0];
			$d2 = $v[1];
			$binding->where( $wpdb->prepare( " AND my_postmeta.meta_key = '_date_completed' AND left(FROM_UNIXTIME(my_postmeta.meta_value),10) >=  %s  and  left(FROM_UNIXTIME(my_postmeta.meta_value),10) <= %s ", $d1, $d2 ) );
		} else {
			$binding->where( $wpdb->prepare( " AND my_postmeta.meta_key = '_date_completed' AND left(FROM_UNIXTIME(my_postmeta.meta_value),10) = %s  ", $value->get_value() ) );
		}
		return $binding;
	}
}

But both still give no results.

I checked the SQL for the latter manually – and it works in phpmyadmin…

Any clues what im doing wrong?

1 month, 2 weeks ago
Stefan
Developer

You already came quite far with your attempts.
But we do have a Meta comparison that you can use in order to smart filter on a meta value.
You could have a look at this comparison that does exactly what you want if I’m correct:

ACP\Search\Comparison\Meta\DateTime\ISO.php

The only difference might be that the ISO comparison expects a datetime format and you might have a date only.
In that case, the mapping of the EQ operator might not be necessary.
You can extend that class or write your own class and extend the meta comparison since it already does a lot for you.

1 month, 2 weeks ago

You must be logged in to reply to this topic.