Rretzko's avatar
Level 15

Livewire Pagination Sorting on nested value

Hi All - I have an event. Each event has Participants and each Participant eager loads with a User object. I have columns for first,last, and middle names in the User table. I've set my Livewire component to paginate the Participants, and this is working as expected but ordering the Participants by their participant.id. I want the participants ordered by last/first and, so far, have NOT found the right way to make this work. I'm hoping you can give me some guidance! Here's the livewire ParticipantsComponent:

<?php

namespace App\Http\Livewire\Events\Versions;

use App\Models\Events\Participants\Participant;
use App\Models\Events\Participants\ParticipantStatus;
use Livewire\Component;
use Livewire\WithPagination;

class ParticipantsComponent extends Component
{
    use WithPagination;

    public $dto;
    public $formToggle=false;
    public $participantStatuses;
    public $participantStatusId=0;
    public $toggleParticipants=true;//false;
    public $userId;
    public $name='';

    public function render()
    {
        return view('livewire..events.versions.participants-component',
        [
            'participants' => Participant::paginate(),
        ]);
    }
}

and the Participant model:

<?php

namespace App\Models\Events\Participants;

use App\Models\User;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Participant extends Model
{
    use HasFactory;

    protected $fillable = ['participant_status_id', 'user_id', 'version_id'];
    protected $with = ['user'];

    public function user(): BelongsTo
    {
        return $this->belongsTo(User::class);
    }
}

and the User model. The fullNameAlpha is delivered through the nameParts($value) method:

<?php

namespace App\Models;

use App\Models\Teachers\School;
use App\Models\Teachers\Student;
use App\Models\Teachers\Teacher;
use App\Models\Utilities\Name;
use App\Notifications\MailResetPasswordToken;
use Illuminate\Contracts\Auth\MustVerifyEmail;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;
use Laravel\Sanctum\HasApiTokens;
use Spatie\Permission\Traits\HasRoles;

class User extends Authenticatable implements MustVerifyEmail
{
    use HasApiTokens, HasFactory, Notifiable, HasRoles;

    /**
     * The attributes that are mass assignable.
     *
     * @var array<int, string>
     */
    protected $fillable = [
        'email',
        'first',
        'last',
        'middle',
        'name',
        'password',
    ];
}

I've tried creating a custom LengthAwarePaginator:

private function participantsSorted(): LengthAwarePaginator
    {
        $participants = Participant::where('version_id', 72)
            ->take(10)
            ->get()
            ->sortBy(['user.last','user.first']);

        $lap = new LengthAwarePaginator(
            $participants->forPage(1,15),
            $participants->count(),
            15,
            1,
        );

        return $lap;
    }

and then changing the render() method to:

 public function render()
    {
        return view('livewire..events.versions.participants-component',
        [
            'participants' => $this->participantsSorted()->paginate(),
        ]);
    }

but this resulted in the error: Method Illuminate\Database\Eloquent\Collection::paginate does not exist. All help is appreciated!

0 likes
9 replies
LaryAI's avatar
Level 58

To sort the participants by last name and first name, you can modify the render method in the ParticipantsComponent Livewire component. Here's the updated code:

use Illuminate\Support\Facades\DB;
use Livewire\WithPagination;

class ParticipantsComponent extends Component
{
    use WithPagination;

    // ...

    public function render()
    {
        $participants = Participant::with('user')
            ->orderBy('user.last')
            ->orderBy('user.first')
            ->paginate();

        return view('livewire.events.versions.participants-component', [
            'participants' => $participants,
        ]);
    }
}

In this solution, we use the orderBy method to sort the participants by the last and first columns of the user relationship. The with('user') method eager loads the user relationship to avoid the N+1 problem.

Make sure to import the necessary classes at the top of the file:

use App\Models\Events\Participants\Participant;

Also, ensure that the Livewire component's view path is correct:

return view('livewire.events.versions.participants-component', [
    'participants' => $participants,
]);

This should order the participants by last name and first name as desired.

Rretzko's avatar
Level 15

Hi all - I ran the QuickDraw solution:

public function render()
    {
        $participants = Participant::with('user')
            ->orderBy('user.last')
            ->orderBy('user.first')
            ->paginate();

        return view('livewire..events.versions.participants-component',
        [
            'participants' => $participants,
        ]);
    }

but that returned:

 Column not found: 1054 Unknown column 'user.last' in 'order clause'
SELECT * FROM `participants` ORDER BY `USER`.`LAST` ASC, `USER`.`FIRST` ASC limit 15 OFFSET 0

error. It looks like the "with" clause is being ignored. The users table and the model both have 'last' and 'first' as fillable columns. I'd write a DB join SQL, but that wouldn't give me the LengthAwarePaginator object that (I think) Livewire is looking for. Thoughts?

Snapey's avatar

Unfortunately Lary, thats not how Eloquent works.

Because of pagination, you will need to use a join..... back in 5

Snapey's avatar
Snapey
Best Answer
Level 122

something like;

$participants = Participant::query()
    ->select('participants.*', 'users.last', 'users.first')
    ->join('users', 'users.id', '=', 'participants.user_id')
    ->orderBy('users.last')
    ->orderBy('users.first')
    ->paginate();
Rretzko's avatar
Level 15

Hi @snapey - Thanks. I've run with that:

 public function render()
    {
        $participants = Participant::query()
            ->select('participants.*', 'users.last', 'users.first')
            ->join('users', 'users.id', '=', 'participants.user_id')
            ->orderBy('users.last')
            ->orderBy('users.first')
            ->paginate();

        return view('livewire..events.versions.participants-component',
        [
            'participants' => $participants,
        ]);
    }

but Livewire doesn't seem to like it even though it does return a LengthAwarePaginator object:

Livewire component's [events.versions.participants-component] public property [participantsSorted] must be of type: [numeric, string, array, null, or boolean]. Only protected or private properties can be set as other types because JavaScript doesn't need to access them.

I must be missing something...

Snapey's avatar

The code you show does not contain $participantsSorted ?

Rretzko's avatar
Level 15

Hi @snapey - Perfect question. I returned to the error log, saw that it was referencing the $participantsSorted variable from the mount() method, commented that out, and that cleared the error. I was so focused on the original problem, I didn't see that I had created a new one. I've marked your answer as Best. Thanks, as always, for the help!

Please or to participate in this conversation.