Order user search results by mu.startdate

[sabbellaParticipant]

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,

[Ernest MarcinkoKeymaster]

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 :)

[sabbellaParticipant]

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,

[Ernest MarcinkoKeymaster]

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 :)

[sabbellaParticipant]

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

[Ernest MarcinkoKeymaster]

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 :)

[sabbellaParticipant]

Ops! sorry, I checked it wrong:

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

[sabbellaParticipant]

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,

[sabbellaParticipant]

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,

[Ernest MarcinkoKeymaster]

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 :)

[sabbellaParticipant]

You cannot access this content.

[sabbellaParticipant]

You cannot access this content.

[sabbellaParticipant]

You cannot access this content.

[Ernest MarcinkoKeymaster]

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 :)

[sabbellaParticipant]

You cannot access this content.

[Author]

Posts