<?php
/**
* Class Sensei_Db_Query_Learners
*
* Helper to fetch learners.
*/
class Sensei_Db_Query_Learners {
/**
* Number of items to return per page.
*
* @var int
*/
public $per_page;
/**
* Offset to start from.
*
* @var int
*/
public $offset;
/**
* Course ID.
*
* @var int
*/
public $course_id;
/**
* Lesson ID.
*
* @var int
*/
public $lesson_id;
/**
* Order by field.
*
* @var string
*/
public $order_by;
/**
* Order direction.
*
* @var string
*/
public $order_type;
/**
* Search term.
*
* @var string
*/
public $search;
/**
* Filter by course ID.
*
* @var int
*/
public $filter_by_course_id;
/**
* Filter type.
*
* @var string
*/
public $filter_type;
/**
* Total number of items.
*
* @var int
*/
public $total_items;
/**
* Sensei_Db_Query_Learners constructor.
*
* @param array $args Arguments to build query.
*/
public function __construct( $args ) {
$this->per_page = isset( $args['per_page'] ) ? absint( $args['per_page'] ) : 25;
$this->offset = isset( $args['offset'] ) ? absint( $args['offset'] ) : 0;
$this->course_id = isset( $args['course_id'] ) ? intval( $args['course_id'] ) : 0;
$this->lesson_id = isset( $args['lesson_id'] ) ? intval( $args['lesson_id'] ) : 0;
$this->order_by = isset( $args['orderby'] ) ? $args['orderby'] : 'learner';
$this->order_type = isset( $args['order'] ) ? strtoupper( $args['order'] ) : 'ASC';
$this->search = isset( $args['search'] ) ? $args['search'] : '';
$this->filter_by_course_id = isset( $args['filter_by_course_id'] ) ? absint( $args['filter_by_course_id'] ) : 0;
$this->filter_type = isset( $args['filter_type'] ) ? $args['filter_type'] : 'inc';
$this->total_items = 0;
}
/**
* Build the SQL query for getting users.
*
* @return string
*/
private function build_query() {
global $wpdb;
$matching_user_ids = null;
if ( is_multisite() || ! empty( $this->search ) ) {
$user_query_args = array();
if ( ! empty( $this->search ) ) {
$user_query_args['search'] = '*' . sanitize_text_field( $this->search ) . '*';
}
$user_query_args['fields'] = 'ids';
$user_query_args['number'] = -1;
$user_query = new WP_User_Query( $user_query_args );
$matching_user_ids = $user_query->get_results();
}
if ( ! empty( $this->filter_by_course_id ) ) {
$eq = ( 'inc' === $this->filter_type ) ? '=' : '!=';
$sql = "
SELECT
`cf`.`user_id`
FROM `{$wpdb->comments}` AS `cf`
WHERE `cf`.`comment_type` = 'sensei_course_status'
AND `cf`.comment_post_ID {$eq} {$this->filter_by_course_id}
AND `cf`.comment_approved IS NOT NULL";
$results = $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
$user_ids = wp_list_pluck( $results, 'user_id' );
if ( ! empty( $matching_user_ids ) ) {
$matching_user_ids = array_intersect( $user_ids, $matching_user_ids );
} else {
$matching_user_ids = $user_ids;
}
}
/*
* Return empty string for `course_statuses` and zero for `course_count` for backward compatibility.
*/
$sql = "
SELECT SQL_CALC_FOUND_ROWS
`u`.`ID` AS 'user_id',
`u`.`user_nicename`,
`u`.`user_login`,
`u`.`user_email`,
'' AS 'course_statuses',
0 AS 'course_count'
FROM `{$wpdb->users}` AS `u`";
$sql .= ' WHERE 1=1';
if ( null !== $matching_user_ids ) {
$matching_user_ids = array_map( 'absint', $matching_user_ids );
$user_id_in = empty( $matching_user_ids ) ? 'false' : implode( ',', $matching_user_ids );
$sql .= " AND u.ID IN ({$user_id_in})";
}
$sql .= ' GROUP BY `u`.`ID`';
if ( ! empty( $this->order_by ) && in_array( $this->order_type, array( 'ASC', 'DESC' ), true ) ) {
$order_type = $this->order_type;
$order_by = $this->order_by;
// Switch case to be used when the value in the 'order_by' param needs modifying to work in the db.
switch ( $this->order_by ) {
case 'learner':
$order_by = 'u.user_login';
break;
default:
break;
}
$sql .= " ORDER BY {$order_by} {$order_type}";
}
$sql .= $wpdb->prepare( ' LIMIT %d OFFSET %d ', array( $this->per_page, $this->offset ) );
return $sql;
}
/**
* Get last activity date by users.
*
* @param int[] $user_ids User IDs to get the last activity date.
*
* @return array Last activity date array.
*/
private function get_last_activity_date_by_users( $user_ids ) {
global $wpdb;
if ( empty( $user_ids ) ) {
return [];
}
$in_placeholders = implode( ', ', array_fill( 0, count( $user_ids ), '%s' ) );
// phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
$results = $wpdb->get_results(
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholders created dinamically.
$wpdb->prepare(
"
SELECT cm.user_id, MAX(cm.comment_date_gmt) AS last_activity_date
FROM {$wpdb->comments} cm
WHERE cm.user_id IN ( {$in_placeholders} )
AND cm.comment_approved IN ('complete', 'passed', 'graded')
AND cm.comment_type = 'sensei_lesson_status'
GROUP BY user_id", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Placeholders created dinamically.
$user_ids
),
// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
OBJECT_K
);
if ( ! $results ) {
return [];
}
return $results;
}
/**
* Get the results of the query.
*
* @return array
*/
public function get_all() {
global $wpdb;
$sql = $this->build_query();
/**
* Filter the query to get learners based on the current search arguments.
*
* @since 4.11.0
*
* @hook sensei_learners_query
*
* @param {string} $sql SQL query.
* @return {Sensei_Db_Query_Learners} Query builder instance.
*/
$sql = apply_filters( 'sensei_learners_query', $sql, $this );
$results = $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- Created inside the build_query method.
$this->total_items = intval( $wpdb->get_var( 'SELECT FOUND_ROWS()' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
$user_ids = wp_list_pluck( $results, 'user_id' );
$last_activity_date_by_users = $this->get_last_activity_date_by_users( $user_ids );
$results = array_map(
function( $row ) use ( $last_activity_date_by_users ) {
$user_id = $row->user_id;
$row->last_activity_date = ! empty( $last_activity_date_by_users[ $user_id ] )
? $last_activity_date_by_users[ $user_id ]->last_activity_date
: null;
return $row;
},
$results
);
return $results;
}
}