Order user search results by mu.startdate

Home Forums Product Support Forums Ajax Search Pro for WordPress Support Order user search results by mu.startdate

This topic contains 15 replies, has 2 voices, and was last updated by Ernest Marcinko Ernest Marcinko 3 years, 4 months ago.

Viewing 15 posts - 1 through 15 (of 16 total)
  • Author
    Posts
  • #22450
    sabbella
    sabbella
    Participant

    Hello,

    I’d like to know if is possible to order the users search results by mu.stardate , the date a user created a membership.

    I’ve got this (code from my users Directory):

    $sqlQuery = “SELECT SQL_CALC_FOUND_ROWS u.ID, u.user_login, u.user_email, u.user_nicename, u.display_name, UNIX_TIMESTAMP(u.user_registered) as joindate, mu.membership_id, mu.initial_payment, mu.billing_amount, mu.cycle_period, mu.cycle_number, mu.billing_limit, mu.trial_amount, mu.trial_limit, UNIX_TIMESTAMP(mu.startdate) as startdate, UNIX_TIMESTAMP(mu.enddate) as enddate, m.name as membership, umf.meta_value as first_name, uml.meta_value as last_name FROM $wpdb->users u LEFT JOIN $wpdb->usermeta umh ON umh.meta_key = ‘pmpromd_hide_directory’ AND u.ID = umh.user_id LEFT JOIN $wpdb->usermeta umf ON umf.meta_key = ‘first_name’ AND u.ID = umf.user_id LEFT JOIN $wpdb->usermeta uml ON uml.meta_key = ‘last_name’ AND u.ID = uml.user_id LEFT JOIN $wpdb->usermeta um ON u.ID = um.user_id LEFT JOIN $wpdb->pmpro_memberships_users mu ON u.ID = mu.user_id LEFT JOIN $wpdb->pmpro_membership_levels m ON mu.membership_id = m.id WHERE mu.status = ‘active’ AND (umh.meta_value IS NULL OR umh.meta_value <> ‘1’) AND mu.membership_id > 0 AND “;

    $sqlQuery .= “(u.user_login LIKE ‘%” . esc_sql($s) . “%’ OR u.user_email LIKE ‘%” . esc_sql($s) . “%’ OR u.display_name LIKE ‘%” . esc_sql($s) . “%’ OR um.meta_value LIKE ‘%” . esc_sql($s) . “%’) “;

    if($levels)
    $sqlQuery .= ” AND mu.membership_id IN(” . esc_sql($levels) . “) “;

    $sqlQuery .= “GROUP BY u.ID ORDER BY “. esc_sql($order_by) . ” ” . $order;
    }
    else
    {
    $sqlQuery = “SELECT SQL_CALC_FOUND_ROWS u.ID, u.user_login, u.user_email, u.user_nicename, u.display_name, UNIX_TIMESTAMP(u.user_registered) as joindate, mu.membership_id, mu.initial_payment, mu.billing_amount, mu.cycle_period, mu.cycle_number, mu.billing_limit, mu.trial_amount, mu.trial_limit, UNIX_TIMESTAMP(mu.startdate) as startdate, UNIX_TIMESTAMP(mu.enddate) as enddate, m.name as membership, umf.meta_value as first_name, uml.meta_value as last_name FROM $wpdb->users u LEFT JOIN $wpdb->usermeta umh ON umh.meta_key = ‘pmpromd_hide_directory’ AND u.ID = umh.user_id LEFT JOIN $wpdb->usermeta umf ON umf.meta_key = ‘first_name’ AND u.ID = umf.user_id LEFT JOIN $wpdb->usermeta uml ON uml.meta_key = ‘last_name’ AND u.ID = uml.user_id LEFT JOIN $wpdb->pmpro_memberships_users mu ON u.ID = mu.user_id LEFT JOIN $wpdb->pmpro_membership_levels m ON mu.membership_id = m.id”;
    $sqlQuery .= ” WHERE mu.status = ‘active’ AND (umh.meta_value IS NULL OR umh.meta_value <> ‘1’) AND mu.membership_id > 0 “;
    if($levels)
    $sqlQuery .= ” AND mu.membership_id IN(” . esc_sql($levels) . “) “;
    $sqlQuery .= “ORDER BY “. esc_sql($order_by) . ” ” . esc_sql($order);
    }

    $sqlQuery .= ” LIMIT $start, $limit”;

    $sqlQuery = apply_filters(“pmpro_member_directory_sql”, $sqlQuery, $levels, $s, $pn, $limit, $start, $end, $order_by, $order);

    Thank you,

    #22454
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    Hi,

    Well, while I cannot test, this might work if you have the latest release of the plugin installed (use in functions.php in theme directory):

    add_filter('asp_query_args', 'asp_query_args_user_fields', 10, 1);
    function asp_query_args_user_fields($args) {
      global $wpdb; 
      $args['user_query'] = array(
          'fields' => 'UNIX_TIMESTAMP(mu.startdate) as startdate, ',
          'join' => " LEFT JOIN $wpdb->pmpro_memberships_users mu ON $wpdb->users.ID = mu.user_id ",
          'where' => '',
          'orderby' => ' startdate DESC, ',
          'groupby' => ''
      );
      return $args;
    }
    Best,
    Ernest Marcinko

    If you like my products, don't forget to rate them on codecanyon :)


    #22460
    sabbella
    sabbella
    Participant

    Hello Ernsert, thank you, it’s almost done:

    ‘orderby’ => ‘ startdate DESC, ‘, doesn’t do nothing.

    If I remove DESC:

    ‘orderby’ => ‘ startdate, ‘, I get the results ordered in ASC

    I tried adding:

    ‘order’ => ‘DESC’, but doesn’t work

    Thank you,

    #22461
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    Hi,

    Try this variation maybe:

    add_filter('asp_query_args', 'asp_query_args_user_fields', 10, 1);
    function asp_query_args_user_fields($args) {
      global $wpdb; 
      $args['user_query'] = array(
          'fields' => 'UNIX_TIMESTAMP(mu.startdate) as startdate, ',
          'join' => " LEFT JOIN $wpdb->pmpro_memberships_users mu ON $wpdb->users.ID = $wpdb->pmpro_memberships_users.user_id ",
          'where' => '',
          'orderby' => ' mu.startdate DESC, ',
          'groupby' => ''
      );
      return $args;
    }
    Best,
    Ernest Marcinko

    If you like my products, don't forget to rate them on codecanyon :)


    #22462
    sabbella
    sabbella
    Participant

    No, it doesn’t work,

    ‘orderby’ => ‘ mu.startdate DESC, ‘,
    or
    ‘orderby’ => ‘ startdate DESC, ‘,

    It doesn’t do nothing. Only if I remove DESC the results order is ASC

    #22463
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    I honestly don’t know then, it should work that way, if the join operation is performed correctly.

    Best,
    Ernest Marcinko

    If you like my products, don't forget to rate them on codecanyon :)


    #22464
    sabbella
    sabbella
    Participant

    Ops! sorry, I checked it wrong:

    If I remove DESC I get the results order by ASC, but not for membership startdate.

    #22503
    sabbella
    sabbella
    Participant

    Hello Ernest, I can’t make it work; I’ve got this code under the last one; would you mind taking a look, please?

    $theusers = $wpdb->get_results($sqlQuery);
    $totalrows = $wpdb->get_var(“SELECT FOUND_ROWS() as found_rows”);

    //update end to match totalrows if total rows is small
    if($totalrows < $end)
    $end = $totalrows;

    $layout_cols = preg_replace(‘/[^0-9]/’, ”, $layout);
    if(!empty($layout_cols))
    $theusers_chunks = array_chunk($theusers, $layout_cols);
    else
    $theusers_chunks = array_chunk($theusers, 1);

    ob_start();

    Thank you,

    #22504
    sabbella
    sabbella
    Participant

    I don’t know if I explained very well; I’d like to order the search results based on startdate of memberships, so, for expample if an user has a membership startdate on 1th january 2019, and changes today it’s membership to other membership, his new membership startdate would be Today (5th may 2019).

    Thank you,

    #22507
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    Hi,

    I don’t know what that code might do there, it must be a part of a bigger section, or multiple files. It looks like it takes chunks out of the results, and then somehow separates them, or something like that.
    I understand you want to order the results that way, but without actually having access to the code, I cannot tell why it is not working as it should on the live results list.

    Best,
    Ernest Marcinko

    If you like my products, don't forget to rate them on codecanyon :)


    #22508
    sabbella
    sabbella
    Participant
    You cannot access this content.
    #22509
    sabbella
    sabbella
    Participant
    You cannot access this content.
    #22510
    sabbella
    sabbella
    Participant
    You cannot access this content.
    #22528
    Ernest Marcinko
    Ernest Marcinko
    Keymaster

    Hi,

    Thank you! I could not find anything new unfortunately there. The only way I could tell if I was able to debug the search query directly on the site. Otherwise it looks okay, so I must be missing something, but I don’t know what exactly.

    Best,
    Ernest Marcinko

    If you like my products, don't forget to rate them on codecanyon :)


    #22564
    sabbella
    sabbella
    Participant
    You cannot access this content.
Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic.