LLMS_REST_Enrollments_Controller::get_objects_query( array $query_args, WP_REST_Request $request = null )

Get enrollments query.


Parameters Parameters

$query_args

(array) (Required) Array of collection arguments.

$request

(WP_REST_Request) (Optional) Full details about the request.

Default value: null


Top ↑

Return Return

(stdClass) An object with two fields: 'items' an array of OBJECT result of the query; 'found_results' the total found items.


Top ↑

Source Source

File: libraries/lifterlms-rest/includes/server/class-llms-rest-enrollments-controller.php

					unset( $query_args['orderby'] );
					break;
			}
		}

		$query_args['is_students_route'] = $request ? false !== stristr( $request->get_route(), '/students/' ) : true;

		return $query_args;

	}

	/**
	 * Get enrollments query.
	 *
	 * @since 1.0.0-beta.1
	 * @since 1.0.0-beta.4 Enrollment's post_id and student_id casted to integer.
	 * @since 1.0.0-beta.10 Added subquery to retrieve the enrollments trigger.
	 * @since 1.0.0-beta.18 Fixed wrong trigger retrieved when multiple trigger were present for the same user,post pair.
	 *
	 * @param  array           $query_args Array of collection arguments.
	 * @param  WP_REST_Request $request    Optional. Full details about the request. Default null.
	 * @return stdClass An object with two fields: 'items' an array of OBJECT result of the query; 'found_results' the total found items.
	 */
	protected function get_objects_query( $query_args, $request = null ) {

		global $wpdb;

		// Maybe limit the query results depending on the page param.
		if ( isset( $query_args['page'] ) ) {
			$skip  = $query_args['page'] > 1 ? ( $query_args['page'] - 1 ) * $query_args['per_page'] : 0;
			$limit = $wpdb->prepare(
				'LIMIT %d, %d',
				array(
					$skip,
					$query_args['per_page'],
				)
			);
		} else {
			$limit = $wpdb->prepare(
				'LIMIT %d',
				$query_args['per_page']
			);
		}

		/**
		 * List enrollments of the current student_id or post_id.
		 * Depends on the endpoint route.
		 */
		if ( $query_args['is_students_route'] ) {
			$id_column = 'user_id';
		} else {
			$id_column = 'post_id';
		}

		/**
		 * Filter the enrollments by user_id or post_id param
		 */
		if ( isset( $query_args['student'] ) ) {
			$filter = sprintf( ' AND upm.user_id IN ( %s )', implode( ', ', $query_args['student'] ) );
		} elseif ( isset( $query_args['post'] ) ) {
			$filter = sprintf( ' AND upm.post_id IN ( %s )', implode( ', ', $query_args['post'] ) );
		} else {
			$filter = '';
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$updated_date_status = $wpdb->prepare(
			"(
				SELECT DISTINCT user_id, post_id, updated_date, meta_value
				FROM {$wpdb->prefix}lifterlms_user_postmeta as upm
				WHERE upm.{$id_column} = %d
				$filter AND upm.meta_key = '_status'
				AND upm.updated_date = (
					SELECT MAX( upm2.updated_date )
					FROM {$wpdb->prefix}lifterlms_user_postmeta AS upm2
					WHERE upm2.meta_key = '_status'
					AND upm2.post_id = upm.post_id
					AND upm2.user_id = upm.user_id
				)
			)",
			array(
				$query_args['id'],
			)
		);

		// Trigger.
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$trigger = $wpdb->prepare(
			"(
				SELECT DISTINCT user_id, post_id, meta_value
				FROM {$wpdb->prefix}lifterlms_user_postmeta as upm
				WHERE upm.{$id_column} = %d
				$filter AND upm.meta_key = '_enrollment_trigger'
				AND upm.updated_date = (
					SELECT MAX( upm2.updated_date )
					FROM {$wpdb->prefix}lifterlms_user_postmeta AS upm2
					WHERE upm2.meta_key = '_enrollment_trigger'
					AND upm2.post_id = upm.post_id
					AND upm2.user_id = upm.user_id
				)
			)",
			array(
				$query_args['id'],
			)
		);

		if ( isset( $query_args['status'] ) ) {
			$filter .= $wpdb->prepare( ' AND upm2.meta_value = %s', $query_args['status'] );
		}

		if ( isset( $query_args['orderby'], $query_args['order'] ) ) {
			$order = sprintf( 'ORDER BY %1$s %2$s', esc_sql( $query_args['orderby'] ), esc_sql( $query_args['order'] ) );
		} else {
			$order = '';
		}

		$query = new stdClass();

		$select_found_rows = empty( $query_args['no_found_rows'] ) ? esc_sql( 'SQL_CALC_FOUND_ROWS' ) : '';

		// the query.
		$query->items = $wpdb->get_results(
			$wpdb->prepare(
				"
				SELECT {$select_found_rows} DISTINCT upm.post_id AS post_id, upm.user_id as student_id, upm.updated_date as date_created, upm2.updated_date as date_updated, upm2.meta_value as status, upm3.meta_value as etrigger
				FROM {$wpdb->prefix}lifterlms_user_postmeta AS upm
				JOIN {$updated_date_status} as upm2 ON upm.post_id = upm2.post_id AND upm.user_id = upm2.user_id
				JOIN {$trigger} as upm3 ON upm.post_id = upm3.post_id AND upm.user_id = upm3.user_id
				WHERE upm.meta_key = '_start_date'
				AND upm.{$id_column} = %d
				{$filter}
				{$order}
				{$limit};
				",


Top ↑

Changelog Changelog

Changelog
Version Description
1.0.0-beta.4 Enrollment's post_id and student_id casted to integer.
1.0.0-beta.18 Fixed wrong trigger retrieved when multiple trigger were present for the same user,post pair.
1.0.0-beta.10 Added subquery to retrieve the enrollments trigger.
1.0.0-beta.1 Introduced.

Top ↑

User Contributed Notes User Contributed Notes

You must log in before being able to contribute a note or feedback.