includes/class.llms.student.query.php
<?php
/**
* Query LifterLMS Students for a given course / membership
*
* @package LifterLMS/Classes
*
* @since 3.4.0
* @version 6.0.0
*/
defined( 'ABSPATH' ) || exit;
/**
* LLMS_Student_Query
*
* @since 3.4.0
* @since 3.13.0 Unknown.
*/
class LLMS_Student_Query extends LLMS_Database_Query {
/**
* Identify the extending query
*
* @var string
*/
protected $id = 'student';
/**
* Retrieve default arguments for a student query
*
* @since 3.4.0
* @since 4.10.2 Drop usage of `this->get_filter( 'default_args' )` in favor of `'llms_student_query_default_args'`.
*
* @return array
*/
protected function get_default_args() {
global $post;
$post_id = ! empty( $post->ID ) ? $post->ID : array();
$args = array(
'post_id' => $post_id,
'sort' => array(
'date' => 'DESC',
'status' => 'ASC',
'last_name' => 'ASC',
'first_name' => 'ASC',
'id' => 'ASC',
),
'statuses' => array_keys( llms_get_enrollment_statuses() ),
);
$args = wp_parse_args( $args, parent::get_default_args() );
/**
* Filters the student query default args
*
* @since 3.4.0
*
* @param array $args Array of default arguments to set up the query with.
* @param LLMS_Student_Query $student_query Instance of LLMS_Student_Query.
*/
return apply_filters( 'llms_student_query_default_args', $args, $this );
}
/**
* Retrieve an array of LLMS_Students for the given set of students returned by the query
*
* @since 3.4.0
* @since 3.8.0 Unknown.
* @since 4.10.2 Drop usage of `this->get_filter( 'get_students' )` in favor of `'llms_student_query_get_students'`.
*
* @return array
*/
public function get_students() {
$students = array();
$results = $this->get_results();
if ( $results ) {
foreach ( $results as $result ) {
$students[] = new LLMS_Student( $result->id );
}
}
if ( $this->get( 'suppress_filters' ) ) {
return $students;
}
/**
* Filters the list of students
*
* @since Unknown
* @since 4.10.2 Pass this query instance as second parameter.
*
* @param LLMS_Student[] $students Array of LLMS_Student instances.
* @param LLMS_Student_Query $student_query Instance of LLMS_Student_Query.
*/
return apply_filters( 'llms_student_query_get_students', $students, $this );
}
/**
* Parses data passed to $statuses
*
* Convert strings to array and ensure resulting array contains only valid statuses
* If no valid statuses, returns to the default.
*
* @since 3.4.0
* @since 3.13.0 Unknown.
*
* @return void
*/
protected function parse_args() {
$statuses = $this->arguments['statuses'];
// Allow strings to be submitted when only requesting one status.
if ( is_string( $statuses ) ) {
$statuses = array( $statuses );
}
// Ensure only valid statuses are used.
$statuses = array_intersect( $statuses, array_keys( llms_get_enrollment_statuses() ) );
// No statuses should return original default.
if ( ! $statuses ) {
$statuses = array_keys( llms_get_enrollment_statuses() );
}
$this->arguments['statuses'] = $statuses;
// Allow numeric strings & ints to be passed instead of an array.
$post_ids = $this->arguments['post_id'];
if ( ! is_array( $post_ids ) && is_numeric( $post_ids ) && $post_ids > 0 ) {
$post_ids = array( $post_ids );
}
foreach ( $post_ids as $key => &$id ) {
$id = absint( $id ); // Verify we have ints.
if ( $id <= 0 ) { // Remove anything negative or 0.
unset( $post_ids[ $key ] );
}
}
$this->arguments['post_id'] = $post_ids;
}
/**
* Prepare the SQL for the query.
*
* @since 3.4.0
* @since 3.13.0 Unknown.
* @since 4.10.2 Demands to `$this->sql_select()` to determine whether or not `SQL_CALC_FOUND_ROWS` statement is needed.
* @since 6.0.0 Renamed from `preprare_query()`.
*
* @return string
*/
protected function prepare_query() {
global $wpdb;
$vars = array();
if ( $this->get( 'search' ) ) {
$search = '%' . $wpdb->esc_like( $this->get( 'search' ) ) . '%';
$vars[] = $search;
$vars[] = $search;
$vars[] = $search;
}
$vars[] = $this->get_skip();
$vars[] = $this->get( 'per_page' );
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $vars is an array with the correct number of items.
$sql = $wpdb->prepare(
"SELECT {$this->sql_select()}
FROM {$wpdb->users} AS u
{$this->sql_joins()}
{$this->sql_search()}
{$this->sql_having()}
{$this->sql_orderby()}
LIMIT %d, %d;",
$vars
);
// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
return $sql;
}
/**
* Determines if a field should be selected/joined based on searching and sorting arguments
*
* @since 3.13.0
*
* @param string $field Field name/key.
* @return bool
*/
private function requires_field( $field ) {
// Get the fields we're sorting by to see if we need to select them for the sorting.
$sort_fields = array_keys( $this->get( 'sort' ) );
if ( in_array( $field, $sort_fields ) ) {
return true;
}
if ( $this->get( 'search' ) ) {
$search_fields = array( 'last_name', 'first_name', 'user_email' );
if ( in_array( $field, $search_fields ) ) {
return true;
}
}
return false;
}
/**
* Retrieve prepared SQL for the HAVING clause
*
* @since 3.4.0
* @since 3.13.0 Unknown.
* @since 4.10.2 Drop usage of `this->get_filter( 'having' )` in favor of `'llms_student_query_having'`.
*
* @return string
*/
private function sql_having() {
global $wpdb;
$sql = "HAVING status IS NOT NULL AND {$this->sql_status_in()}";
if ( $this->get( 'suppress_filters' ) ) {
return $sql;
}
/**
* Filters the query HAVING clause
*
* @since Unknown
*
* @param string $sql The HAVING clause of the query.
* @param LLMS_Student_Query $student_query Instance of LLMS_Student_Query.
*/
return apply_filters( 'llms_student_query_having', $sql, $this );
}
/**
* Setup joins based on submitted sort and search args
*
* @since 3.13.0
* @since 4.10.2 Drop usage of `this->get_filter( 'join' )` in favor of `'llms_student_query_join'`.
*
* @return string
*/
private function sql_joins() {
global $wpdb;
$joins = array();
$fields = array(
'first_name' => "JOIN {$wpdb->usermeta} AS m_first ON u.ID = m_first.user_id AND m_first.meta_key = 'first_name'",
'last_name' => "JOIN {$wpdb->usermeta} AS m_last ON u.ID = m_last.user_id AND m_last.meta_key = 'last_name'",
'overall_progress' => "JOIN {$wpdb->usermeta} AS m_o_p ON u.ID = m_o_p.user_id AND m_o_p.meta_key = 'llms_overall_progress'",
'overall_grade' => "JOIN {$wpdb->usermeta} AS m_o_g ON u.ID = m_o_g.user_id AND m_o_g.meta_key = 'llms_overall_grade'",
);
// Add the fields to the array of fields to select.
foreach ( $fields as $key => $statement ) {
if ( $this->requires_field( $key ) ) {
$joins[] = $statement;
}
}
$sql = implode( ' ', $joins );
if ( $this->get( 'suppress_filters' ) ) {
return $sql;
}
/**
* Filters the query JOIN clause
*
* @since 3.13.0
*
* @param string $sql The JOIN clause of the query.
* @param LLMS_Student_Query $student_query Instance of LLMS_Student_Query.
*/
return apply_filters( 'llms_student_query_join', $sql, $this );
}
/**
* Retrieve the prepared SEARCH query for the WHERE clause
*
* @since 3.4.0
* @since 3.8.0 Unknown.
* @since 4.10.2 Drop usage of `this->get_filter( 'search' )` in favor of `'llms_student_query_search'`.
*
* @return string
*/
private function sql_search() {
$sql = '';
if ( $this->get( 'search' ) ) {
global $wpdb;
$sql .= ' AND (
m_last.meta_value LIKE %s
OR m_first.meta_value LIKE %s
OR u.user_email LIKE %s
)';
}
if ( $this->get( 'suppress_filters' ) ) {
return $sql;
}
/**
* Filters the part of the SQL query that performs the search.
*
* @since Unknown
*
* @param string $sql The SQL part that performs the search.
* @param LLMS_Student_Query $student_query Instance of LLMS_Student_Query.
*/
return apply_filters( 'llms_student_query_search', $sql, $this );
}
/**
* Set up the SQL for the select statement.
*
* @since 3.13.0
* @since 4.10.2 Drop usage of `this->get_filter( 'select' )` in favor of `'llms_student_query_select'`.
* Use `$this->sql_select_columns({columns})` to determine additional columns to select.
* @since 5.10.0 Add a subquery for completed date.
*
* @return string
*/
private function sql_select() {
$selects = array();
// Always select the ID.
$selects[] = 'u.ID as id';
// Always add the subqueries for enrollment status.
$selects[] = "( {$this->sql_subquery( 'meta_value' )} ) AS status";
// All the possible fields.
$fields = array(
'completed' => "( {$this->sql_subquery( 'updated_date', '_is_complete' )} ) AS completed",
'date' => "( {$this->sql_subquery( 'updated_date' )} ) AS `date`",
'last_name' => 'm_last.meta_value AS last_name',
'first_name' => 'm_first.meta_value AS first_name',
'email' => 'u.user_email AS email',
'registered' => 'u.user_registered AS registered',
'overall_progress' => 'CAST( m_o_p.meta_value AS decimal( 5, 2 ) ) AS overall_progress',
'overall_grade' => 'CAST( m_o_g.meta_value AS decimal( 5, 2 ) ) AS overall_grade',
);
// Add the fields to the array of fields to select.
foreach ( $fields as $key => $statement ) {
if ( $this->requires_field( $key ) ) {
$selects[] = $statement;
}
}
$sql = implode( ', ', $selects );
$sql = $this->sql_select_columns( $sql );
if ( $this->get( 'suppress_filters' ) ) {
return $sql;
}
/**
* Filters the query SELECT clause
*
* @since 3.13.0
*
* @param string $sql The SELECT clause of the query.
* @param LLMS_Student_Query $student_query Instance of LLMS_Student_Query.
*/
return apply_filters( 'llms_student_query_select', $sql, $this );
}
/**
* Generate an SQL IN clause based on submitted status arguments
*
* @since 3.13.0
*
* @param string $column Name of the column.
* @return string
*/
private function sql_status_in( $column = 'status' ) {
global $wpdb;
$comma = false;
$statuses = array();
$sql = '';
foreach ( $this->get( 'statuses' ) as $status ) {
$sql .= $comma ? ',%s' : '%s';
$statuses[] = $status;
$comma = true;
}
$sql = $wpdb->prepare( $sql, $statuses ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
return "{$column} IN ( {$sql} )";
}
/**
* Generate an SQL subquery for the meta key in the main query.
*
* @since 3.13.0
* @since 5.10.0 Add `$meta_key` argument.
*
* @param string $column Column name.
* @param string $meta_key Optional meta key to use in the WHERE condition. Defaults to '_status'.
* @return string
*/
private function sql_subquery( $column, $meta_key = '_status' ) {
global $wpdb;
$post_ids = $this->get( 'post_id' );
if ( $post_ids ) {
$post_ids = implode( ',', $post_ids );
$and = "AND post_id IN ( {$post_ids} )";
} else {
$and = "AND {$this->sql_status_in( 'meta_value' )}";
}
return "SELECT {$column}
FROM {$wpdb->prefix}lifterlms_user_postmeta
WHERE meta_key = '{$meta_key}'
AND user_id = id
{$and}
ORDER BY updated_date DESC
LIMIT 1";
}
}