rcl_query

/*старый порядок*/
$courses             = new OCS_Company_Courses();
$creators            = new WPUsers();
$access              = new OCS_WAU_Access();
$learnAccess         = new OCS_Product_Access();
$sections            = new OCS_Sections();
$modules             = new OCS_Modules();

$args = array(
    'post_type'          => 'courses',
    'post_status__in'    => isset( $_REQUEST['course_status'] ) ? $_REQUEST['course_status'] : null,
    'post_title__like'   => isset( $_REQUEST['course_name'] ) ? $_REQUEST['course_name'] : null,
    'ID'                 => isset( $_REQUEST['course_id'] ) && $_REQUEST['course_id'] ? $_REQUEST['course_id'] : null,
    'offset'             => $this->pager->offset,
    'number'             => $this->pager->number,
    'groupby'            => 'posts.ID',
    'fields'             => array(
        'post_title'     => array(
            'as' => 'course_name'
        ),
        'ID'             => array(
            'as' => 'course_id'
        ),
        'post_status'    => array(
            'as' => 'course_status'
        )
    ),
    'join_query'         => array(
        array(
            'table'      => $courses->query['table'],
            'on_ID'      => 'course_id',
            'join'       => 'LEFT',
            'fields'     => array(
                //'course_id'        => 'course_id',
                'company_id'     => 'company_id',
                'custom_query'   => array(
                    '('
                    . 'SELECT COUNT( DISTINCT ' . $modules->query['table']['as'] . '.module_id) '
                    . 'FROM ' . $modules->query['table']['name'] . ' AS ' . $modules->query['table']['as'] . ' '
                    . 'WHERE ' . $modules->query['table']['as'] . '.section_id IN ('
                    . 'SELECT section_id FROM ' . $sections->query['table']['name'] . ' '
                    . 'WHERE ' . $sections->query['table']['name'] . '.course_id =  ' . $courses->query['table']['as'] . '.course_id'
                    . ')'
                    . ') AS modules_count',
                    '(SELECT COUNT(DISTINCT user_id) '
                    . 'FROM ' . $learnAccessLearn->query['table']['name'] . ' '
                    . 'WHERE ' . $learnAccessLearn->query['table']['name'] . '.course_id =  ' . $courses->query['table']['as'] . '.course_id '
                    . 'AND access_status = "1" '
                    . 'AND progress IN ("wait","work")) as members_learn',
                    '(SELECT COUNT(DISTINCT user_id) '
                    . 'FROM ' . $learnAccessLearn->query['table']['name'] . ' '
                    . 'WHERE ' . $learnAccessLearn->query['table']['name'] . '.course_id =  ' . $courses->query['table']['as'] . '.course_id '
                    . 'AND progress = "course_end") as members_end'
                )
            ),
            'join_query' => array(
                array(
                    'table'              => $creators->query['table'],
                    'on_company_id'      => 'ID',
                    'join'               => 'LEFT',
                    'user_email__like'   => isset( $_REQUEST['creator_email'] ) ? $_REQUEST['creator_email'] : null,
                    'fields'             => array(
                        'user_email' => array(
                            'as' => 'creator_email'
                        )
                    )
                ),
                array(
                    'table'          => $access->query['table'],
                    'join'           => 'LEFT',
                    'on_company_id'  => 'user_id',
                    'end_status__in' => isset( $_REQUEST['account_status'] ) ? $_REQUEST['account_status'] : null,
                    'fields'         => array(
                        'end_status' => array(
                            'as' => 'account_status'
                        )
                    )
                ),
                array(
                    'table'          => $sections->query['table'],
                    'on_course_id'   => 'course_id',
                    'join'           => 'LEFT',
                    'fields'         => false
                ),
                array(
                    'join'           => 'LEFT',
                    'table'          => $learnAccess->query['table'],
                    'on_course_id'   => 'course_id',
                    'fields'         => array(
                        'custom_query' => array(
                            'COUNT(DISTINCT ' . $learnAccess->query['table']['as'] . '.user_id) as members_all'
                        )
                    )
                )
            )
        )
    )
);

$posts->set_query( $args );

if ( isset( $_REQUEST['modules_count'] ) ) {
    $posts->query['having'][] = "modules_count BETWEEN '" . $_REQUEST['modules_count'][0] . "' AND '" . $_REQUEST['modules_count'][1] . "'";
}

if ( isset( $_REQUEST['members_all'] ) ) {
    $posts->query['having'][] = "members_all BETWEEN '" . $_REQUEST['members_all'][0] . "' AND '" . $_REQUEST['members_all'][1] . "'";
}

if ( $this->orderby )
    $posts->query['orderby'] = $this->orderby;

$posts->query['order'] = $this->order;

return $posts;

/*новый порядок*/
$courses             = new OCS_Company_Courses();

$query = RQ::tbl( new WPPosts() )
    ->select( [
        'course_name'    => 'post_title',
        'course_id'      => 'ID',
        'course_status'  => 'post_status',
    ] )
    ->join(
        [ 'ID', 'course_id', 'LEFT' ], RQ::tbl( $courses )
        ->select( [
            'company_id'     => 'company_id',
            'modules_count'  => RQ::tbl( new OCS_Modules() )
            ->distinct( ['count' => ['module_id' ] ] )
            ->where( [
                'section_id__in' => RQ::tbl( new OCS_Sections() )->select( ['section_id' ] )->where( array(
                    'course_id' => $courses->get_colname( 'course_id' )
                ) )
            ] ),
            'members_learn'  => RQ::tbl( new OCS_Product_Access( 'access1' ) )
            ->distinct( ['count' => [ 'user_id' ] ] )
            ->where( [
                'course_id'      => $courses->get_colname( 'course_id' ),
                'access_status'  => 1,
                'progress__in'   => ['wait', 'work' ]
            ] ),
            'members_end'    => RQ::tbl( new OCS_Product_Access( 'access2' ) )
            ->distinct( ['count' => [ 'user_id' ] ] )
            ->where( [
                'course_id'  => $courses->get_colname( 'course_id' ),
                'progress'   => 'course_end'
            ] )
        ] )
        ->join(
            ['company_id', 'ID', 'LEFT' ], RQ::tbl( new WPUsers() )
            ->select( ['creator_email' => 'user_email' ] )
            ->where( ['user_email__like' => isset( $_REQUEST['creator_email'] ) ? $_REQUEST['creator_email'] : null ] )
        )
        ->join(
            ['company_id', 'user_id', 'LEFT' ], RQ::tbl( new OCS_WAU_Access() )
            ->select( ['account_status' => 'end_status' ] )
            ->where( ['end_status__in' => isset( $_REQUEST['account_status'] ) ? $_REQUEST['account_status'] : null ] )
        )
        ->join(
            ['course_id', 'course_id', 'LEFT' ], RQ::tbl( new OCS_Sections() )
        )
        ->join(
            ['course_id', 'course_id', 'LEFT' ], RQ::tbl( new OCS_Product_Access( 'access3' ) )
            ->distinct( ['count' => ['members_all' => 'user_id' ] ] )
        )
    )->where( [
        'post_type'          => 'courses',
        'post_status__in'    => isset( $_REQUEST['course_status'] ) ? $_REQUEST['course_status'] : null,
        'post_title__like'   => isset( $_REQUEST['course_name'] ) ? $_REQUEST['course_name'] : null,
        'ID'                 => isset( $_REQUEST['course_id'] ) && $_REQUEST['course_id'] ? $_REQUEST['course_id'] : null,
    ] )
    ->groupby( 'posts.ID' )
    ->orderby( $this->orderby ? $this->orderby : 'ID', $this->order )
    ->limit( $this->pager->number, $this->pager->offset );

if ( isset( $_REQUEST['modules_count'] ) ) {
    $query->add_having( "modules_count BETWEEN '" . $_REQUEST['modules_count'][0] . "' AND '" . $_REQUEST['modules_count'][1] . "'" );
}

if ( isset( $_REQUEST['members_all'] ) ) {
    $query->add_having( "members_all BETWEEN '" . $_REQUEST['members_all'][0] . "' AND '" . $_REQUEST['members_all'][1] . "'" );
}

return $query;

/*результат*/
SELECT posts.post_title AS course_name, posts.ID AS course_id, posts.post_status AS course_status, ocs_companies_courses.company_id AS company_id, (SELECT COUNT( DISTINCT ocs_modules.module_id) FROM cms_wp_rcl_ocs_modules AS ocs_modules WHERE ocs_modules.section_id IN (SELECT ocs_sections.section_id FROM cms_wp_rcl_ocs_sections AS ocs_sections WHERE ocs_sections.course_id = ocs_companies_courses.course_id)) AS modules_count, (SELECT COUNT( DISTINCT access1.user_id) FROM cms_wp_rcl_ocs_product_access AS access1 WHERE access1.course_id = ocs_companies_courses.course_id AND access1.access_status = '1' AND access1.progress IN ('wait','work')) AS members_learn, (SELECT COUNT( DISTINCT access2.user_id) FROM cms_wp_rcl_ocs_product_access AS access2 WHERE access2.course_id = ocs_companies_courses.course_id AND access2.progress = 'course_end') AS members_end, users.user_email AS creator_email, ocs_wau_access.end_status AS account_status, COUNT( DISTINCT access3.user_id) AS members_all FROM cms_wp_posts AS posts LEFT JOIN cms_wp_rcl_ocs_companies_courses AS ocs_companies_courses ON posts.ID = ocs_companies_courses.course_id LEFT JOIN cms_wp_users AS users ON ocs_companies_courses.company_id = users.ID LEFT JOIN cms_wp_rcl_ocs_wau_access AS ocs_wau_access ON ocs_companies_courses.company_id = ocs_wau_access.user_id LEFT JOIN cms_wp_rcl_ocs_sections AS ocs_sections ON ocs_companies_courses.course_id = ocs_sections.course_id LEFT JOIN cms_wp_rcl_ocs_product_access AS access3 ON ocs_companies_courses.course_id = access3.course_id WHERE posts.post_type = 'courses' GROUP BY posts.ID ORDER BY posts.ID DESC LIMIT 0,30