Wordpress orderby meta_value Eloquent equivalent

I've created a user_meta table which is similar to Wordpress. Wordpress supports "orderby" meta value and I'm looking for something equivalent in Eloquent. https://wordpress.stackexchange.com/questions/30241/wp-query-order-results-by-meta-value

The table is a list of user licenses, and the "sortable" column I'm trying is through this relationship.

The value of this column is found using this relationship. (user_meta() itself a HasMany relationship, but this is a quick lookup to return only one value);

$license->user->last_login();

public function last_login(): HasOne
{
      return $this->hasOne(UserMeta::class)
          ->where('meta_key', 'last_login');
}

I have been trying something using withAggregate() as a way to pull in this value, but I keep getting a "page is not responding" error. Any eloquent geniuses who can help?

LaryAI

1 year ago

Level 58

To achieve the equivalent of WordPress's orderby meta value in Eloquent, you can use the with method to eager load the relationship and then use the orderBy method to sort the results based on the meta value. Here's how you can do it:

Define the relationship in your User model to get the last_login meta value.
Use Eloquent's join method to join the user_meta table and then order by the meta_value.

Here's a step-by-step solution:

Define the Relationship in the User Model:

// User.php
public function lastLogin()
{
    return $this->hasOne(UserMeta::class)
                ->where('meta_key', 'last_login');
}

Query with Join and Order By:

use App\Models\User;
use App\Models\UserMeta;
use Illuminate\Support\Facades\DB;

// Assuming you have a User model and a UserMeta model
$users = User::select('users.*')
    ->join('user_meta', function($join) {
        $join->on('users.id', '=', 'user_meta.user_id')
             ->where('user_meta.meta_key', 'last_login');
    })
    ->orderBy('user_meta.meta_value', 'desc') // or 'asc' for ascending order
    ->get();

This query will join the users table with the user_meta table on the user_id and filter the user_meta table to only include rows where meta_key is last_login. Then, it orders the results by the meta_value.

Alternative Using withAggregate:

If you prefer using withAggregate, you can do it like this:

use App\Models\User;

$users = User::withAggregate(['lastLogin as last_login_value' => function ($query) {
    $query->select('meta_value');
}])
->orderBy('last_login_value', 'desc') // or 'asc' for ascending order
->get();

In this example, withAggregate is used to pull in the meta_value of the last_login and alias it as last_login_value. Then, you can order by this aggregated value.

These methods should help you achieve the desired ordering by meta value in Eloquent.

musciplay

1 year ago

Level 8

After changing to leftJoin, this is working for me.


    $users = User::select('users.*')
        ->with('last_login')

        // leftJoin includes null results from usermeta table
        ->leftJoin('user_metas', function($join) {
            $join->on('users.id', '=', 'user_metas.user_id')
                ->where('user_metas.meta_key', 'last_login');
        })
        ->orderBy('user_metas.meta_value', 'asc') // or 'asc' for ascending order
        ->get();

        dd($users);

But how can I then pass this sorting into a relationship one level higher? ex. something like?

I think I need an additional join, but not sure

UserLicense::orderBy(
	User::select('users.*')
        ->with('last_login')

        // leftJoin includes null results from usermeta table
        ->leftJoin('user_metas', function($join) {
            $join->on('users.id', '=', 'user_metas.user_id')
                ->where('user_metas.meta_key', 'last_login');
        })

        ->orderBy('user_metas.meta_value', 'asc');
)

musciplay

1 year ago

Level 8

Got it working! orderByRaw() saved the day

// Subquery
$users = User::select('users.*')
    ->with('last_login')
    ->whereHas('district_license', function($q) use ($direction, $livewire) {
        $q->where('district_id', $livewire->ownerRecord->id);
    })

    // leftJoin includes null results from usermeta table
    ->leftJoin('user_metas', function($join) {
        $join->on('users.id', '=', 'user_metas.user_id')
            ->where('user_metas.meta_key', 'last_login');
    })

    ->orderBy('user_metas.meta_value', $direction)
    ->pluck('id');

// OrderByRaw using user ids, maintaining the order of the original collection.
$query
    ->orderByRaw("FIELD(user_id, {$users->implode(',')})");

Please or to participate in this conversation.