Order user search results by mu.startdate

[sabbella]

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 Marcinko]

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;
}

[sabbella]

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 Marcinko]

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;
}

[sabbella]

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 Marcinko]

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

[sabbella]

Ops! sorry, I checked it wrong:

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

[sabbella]

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,

[sabbella]

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 Marcinko]

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.

[sabbella]

You cannot access this content.

[sabbella]

You cannot access this content.

[sabbella]

You cannot access this content.

[Ernest Marcinko]

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.

[sabbella]

You cannot access this content.

[Author]

Posts