Query woocommerce booking by availability date

Question

I use Woothemes booking for my woocommerce site

As i build custom search for products, i have to search for availability also, here is how i should manage availability from B.O and how the plugin stores to database

a:8:{i:0;a:4:{s:4:"type";s:4:"days";s:8:"bookable";s:3:"yes";s:4:"from";s:1:"3";s:2:"to";s:1:"3";}i:1;a:4:{s:4:"type";s:6:"custom";s:8:"bookable";s:3:"yes";s:4:"from";s:10:"2015-07-09";s:2:"to";s:10:"2015-07-09";}i:2;a:4:{s:4:"type";s:6:"custom";s:8:"bookable";s:3:"yes";s:4:"from";s:10:"2015-07-08";s:2:"to";s:10:"2015-07-08";}i:3;a:4:{s:4:"type";s:6:"custom";s:8:"bookable";s:3:"yes";s:4:"from";s:10:"2015-07-10";s:2:"to";s:10:"2015-07-10";}i:4;a:4:{s:4:"type";s:6:"custom";s:8:"bookable";s:3:"yes";s:4:"from";s:10:"2015-07-15";s:2:"to";s:10:"2015-07-15";}i:5;a:4:{s:4:"type";s:6:"custom";s:8:"bookable";s:3:"yes";s:4:"from";s:10:"2015-07-16";s:2:"to";s:10:"2015-07-16";}i:6;a:4:{s:4:"type";s:6:"custom";s:8:"bookable";s:3:"yes";s:4:"from";s:10:"2015-07-17";s:2:"to";s:10:"2015-07-17";}i:7;a:4:{s:4:"type";s:6:"months";s:8:"bookable";s:2:"no";s:4:"from";s:1:"8";s:2:"to";s:1:"8";}}

I can retrieve these values from wordpress built-in function get_post_meta,

$avail = get_post_meta($product->id, '_wc_booking_availability');

and the result is :

Array
(
[0] => Array
    (
        [type] => days
        [bookable] => yes
        [from] => 3
        [to] => 3
    )

[1] => Array
    (
        [type] => custom
        [bookable] => yes
        [from] => 2015-07-09
        [to] => 2015-07-09
    )

[2] => Array
    (
        [type] => custom
        [bookable] => yes
        [from] => 2015-07-08
        [to] => 2015-07-08
    )

[3] => Array
    (
        [type] => custom
        [bookable] => yes
        [from] => 2015-07-10
        [to] => 2015-07-10
    )

[4] => Array
    (
        [type] => custom
        [bookable] => yes
        [from] => 2015-07-15
        [to] => 2015-07-15
    )

[5] => Array
    (
        [type] => custom
        [bookable] => yes
        [from] => 2015-07-16
        [to] => 2015-07-16
    )

[6] => Array
    (
        [type] => custom
        [bookable] => yes
        [from] => 2015-07-17
        [to] => 2015-07-17
    )

[7] => Array
    (
        [type] => months
        [bookable] => no
        [from] => 8
        [to] => 8
    )

)

As you can see user is able to specify if product is bookable or not in a range of day or month, so my question is how i can do sql query to check if a date variable is available for this product, i think meta_query will do the job (as below) but how i can specify for unavailable date? how do you think?

if($_GET['when']){

        /* checkIsAValidDate() >> check date format */
        if ( checkIsAValidDate( $_GET['when'] ) ){
            $quand = $_GET['when'];
            $available = array(
                "post_type" => "product",
                "meta_query" => array(
                    "relation"  => "AND",
                    "key"       => '_wc_booking_availability',
                    "value"     => $when,
                    'compare'   => 'IN'
                )
            );

        }
    }

Answer 1

Since the meta value is an array, it may not be possible to use WP_Meta_Query. While I agree that using WP_Meta_Query seems like the more elegant way to do this, I've always found this function really confusing, and since you can get the array of bookable objects, it seems like it would be straightforward to write a function like:

/**
 * Returns an array of bookable products according to supplied criteria
 * 
 * @param  int    $pid      the $product->ID of the bookable object
 * @param  mixed  $when     a date in YYYY-MM-DD format or integer representing a day or month
 * @param  string $type     a value of 'day', 'month', or 'custom'
 * @param  string $bookable whether the bookable object is bookable or not ('yes' or 'no')
 * @return array            an array of bookable objects for the product
 */
function getBookables( $pid, $when, $type, $bookable = 'yes' ) {
    $result = array();

    // is $when in the YYYY-MM-DD format?
    if ( 'custom' == $type ) {
        // it's a custom date so convert $when to DateTime
        $when = DateTime::createFromFormat( 'Y-m-d', $when );
    }

    $availability = get_post_meta( $pid, '_wc_booking_availability' );
    foreach ( $availability as $a ) {
        if ( $a[ 'bookable' ] == $bookable ) {
            // is it in the YYYY-MM-DD format?
            if ( $when instanceof DateTime ) {
                // it's a custom date so use date compare
                $from = DateTime::createFromFormat( 'Y-m-d', $a[ 'from' ] );
                $to   = DateTime::createFromFormat( 'Y-m-d', $a[ 'to'   ] );
                if ( $when >= $from && $when <= $to ) {
                    $result[] = $a;
                }
            } else {
                // it is an integer value (day or month)
                if ( $type == $a[ 'type' ] && ( $when >= $from && $when <= $to ) ) {
                    $result[] = $a;
                }
            }
        }
    }
    return $result;
}

Notice that it is probably necessary to pass the type to the search function since even though I can distinguish YYYY-MM-DD values from int values, there is no easy way to distinguish month from day values. As such you might use the above function:

if ( $_GET[ 'when' ] && $_GET[ 'type' ] ) {
    $when = $_GET[ 'when' ];
    $type = $_GET[ 'type' ];
    $bookables = getBookables( $product->ID, $when, $type );
}

Also note that if you pass the string 'no' as the fourth parameter to the function, you can get the list of unavailable times.

Hope this helps!