WordPress at a glance
function is not described

Automattic\WooCommerce\Admin\API\Reports

DataStore::update_intervals_sql_params() protected WC 1.0

Updates the LIMIT query part for Intervals query of the report.

If there are less records in the database than time intervals, then we need to remap offset in SQL query to fetch correct records.

{} It's a method of the class: DataStore{}

No Hooks.

Return

null. Nothing.

Usage

// protected - for code of main (parent) or child class
$result = $this->update_intervals_sql_params( $query_args, $db_interval_count, $expected_interval_count, $table_name );
$query_args(array) (required)
Query arguments.
$db_interval_count(int) (required)
Database interval count.
$expected_interval_count(int) (required)
Expected interval count on the output.
$table_name(string) (required)
Name of the db table relevant for the date constraint.

Code of DataStore::update_intervals_sql_params() WC 5.2.2

<?php
protected function update_intervals_sql_params( &$query_args, $db_interval_count, $expected_interval_count, $table_name ) {
	if ( $db_interval_count === $expected_interval_count ) {
		return;
	}

	$params   = $this->get_limit_params( $query_args );
	$local_tz = new \DateTimeZone( wc_timezone_string() );
	if ( 'date' === strtolower( $query_args['orderby'] ) ) {
		// page X in request translates to slightly different dates in the db, in case some
		// records are missing from the db.
		$start_iteration = 0;
		$end_iteration   = 0;
		if ( 'asc' === strtolower( $query_args['order'] ) ) {
			// ORDER BY date ASC.
			$new_start_date    = $query_args['after'];
			$intervals_to_skip = ( $query_args['page'] - 1 ) * $params['per_page'];
			$latest_end_date   = $query_args['before'];
			for ( $i = 0; $i < $intervals_to_skip; $i++ ) {
				if ( $new_start_date > $latest_end_date ) {
					$new_start_date  = $latest_end_date;
					$start_iteration = 0;
					break;
				}
				$new_start_date = TimeInterval::iterate( $new_start_date, $query_args['interval'] );
				$start_iteration ++;
			}

			$new_end_date = clone $new_start_date;
			for ( $i = 0; $i < $params['per_page']; $i++ ) {
				if ( $new_end_date > $latest_end_date ) {
					break;
				}
				$new_end_date = TimeInterval::iterate( $new_end_date, $query_args['interval'] );
				$end_iteration ++;
			}
			if ( $new_end_date > $latest_end_date ) {
				$new_end_date  = $latest_end_date;
				$end_iteration = 0;
			}
			if ( $end_iteration ) {
				$new_end_date_timestamp = (int) $new_end_date->format( 'U' ) - 1;
				$new_end_date->setTimestamp( $new_end_date_timestamp );
			}
		} else {
			// ORDER BY date DESC.
			$new_end_date        = $query_args['before'];
			$intervals_to_skip   = ( $query_args['page'] - 1 ) * $params['per_page'];
			$earliest_start_date = $query_args['after'];
			for ( $i = 0; $i < $intervals_to_skip; $i++ ) {
				if ( $new_end_date < $earliest_start_date ) {
					$new_end_date  = $earliest_start_date;
					$end_iteration = 0;
					break;
				}
				$new_end_date = TimeInterval::iterate( $new_end_date, $query_args['interval'], true );
				$end_iteration ++;
			}

			$new_start_date = clone $new_end_date;
			for ( $i = 0; $i < $params['per_page']; $i++ ) {
				if ( $new_start_date < $earliest_start_date ) {
					break;
				}
				$new_start_date = TimeInterval::iterate( $new_start_date, $query_args['interval'], true );
				$start_iteration ++;
			}
			if ( $new_start_date < $earliest_start_date ) {
				$new_start_date  = $earliest_start_date;
				$start_iteration = 0;
			}
			if ( $start_iteration ) {
				// @todo Is this correct? should it only be added if iterate runs? other two iterate instances, too?
				$new_start_date_timestamp = (int) $new_start_date->format( 'U' ) + 1;
				$new_start_date->setTimestamp( $new_start_date_timestamp );
			}
		}
		// @todo - Do this without modifying $query_args?
		$query_args['adj_after']  = $new_start_date;
		$query_args['adj_before'] = $new_end_date;
		$adj_after                = $new_start_date->format( TimeInterval::$sql_datetime_format );
		$adj_before               = $new_end_date->format( TimeInterval::$sql_datetime_format );
		$this->interval_query->clear_sql_clause( array( 'where_time', 'limit' ) );
		$this->interval_query->add_sql_clause( 'where_time', "AND {$table_name}.date_created <= '$adj_before'" );
		$this->interval_query->add_sql_clause( 'where_time', "AND {$table_name}.date_created >= '$adj_after'" );
		$this->clear_sql_clause( 'limit' );
		$this->add_sql_clause( 'limit', 'LIMIT 0,' . $params['per_page'] );
	} else {
		if ( 'asc' === $query_args['order'] ) {
			$offset = ( ( $query_args['page'] - 1 ) * $params['per_page'] ) - ( $expected_interval_count - $db_interval_count );
			$offset = $offset < 0 ? 0 : $offset;
			$count  = $query_args['page'] * $params['per_page'] - ( $expected_interval_count - $db_interval_count );
			if ( $count < 0 ) {
				$count = 0;
			} elseif ( $count > $params['per_page'] ) {
				$count = $params['per_page'];
			}

			$this->clear_sql_clause( 'limit' );
			$this->add_sql_clause( 'limit', 'LIMIT ' . $offset . ',' . $count );
		}
		// Otherwise no change in limit clause.
		// @todo - Do this without modifying $query_args?
		$query_args['adj_after']  = $query_args['after'];
		$query_args['adj_before'] = $query_args['before'];
	}
}