Laravel livewire sorting by foreign key

Question

I'm trying to sort a post table by a foreign key, and can't figure out how to do it. Hopefully someone can lend me a hand. I can sort by the fields already present. The post_stats table has the following columns:

id, 
post_id, 
views, 

with views being an int. I need to sort it by 'views'.

This is what I currently have. My livewire controller:

<?php

namespace App\Http\Livewire\Admin;

use App\Models\Post;
use Livewire\Component;
use Livewire\WithPagination;

class PostsIndex extends Component
{
  use WithPagination;

  protected $paginationTheme = 'bootstrap';

  public $search;
  public $perPage = 12;
  public $orderBy = 'created_at';
  public $orderAsc = false;

  public function updatingSearch()
  {
    $this->resetPage();
  }

  public function render()
  {
    $posts = Post::with([
      'category',
      'user:id,username',
      'image',
      'stats',
      'tags:id,name,slug',
    ])
      ->orWhere('title', 'LIKE', '%' . $this->search . '%')
      ->orWhere('slug', 'LIKE', '%' . $this->search . '%')
      ->orWhere('id', 'LIKE', '%' . $this->search . '%')
      ->orderBy($this->orderBy, $this->orderAsc ? 'asc' : 'desc')
      ->paginate($this->perPage);

    return view('livewire.admin.posts-index', compact('posts'));
  }
}

// Post model

public function stats()
{
    return $this->hasOne(PostStat::class);
}

// PostStat model

public function post()
{
    return $this->belongsTo(Post::class);
}

// Blade view

<select wire:model='orderBy' class="form-select">
<option selected="id" value="id">Sort By ID</option>
<option value="title">Sort By Title</option>
<option value="?????">Sort By Views</option>
</select>

Answer 1

If you need to order by your search with a foreign key, you need to do a relation with the table post_stats, you can do this using join or left join.

Like this:

$posts = Post::with([
      'category',
      'user:id,username',
      'image',
      'stats',
      'tags:id,name,slug',
    ])
      ->leftJoin('post_stats', 'post_stats.post_id', '=', 'post.id') //or ->join('post_stats', 'post_stats.post_id', '=', 'post.id')
      ->orWhere('title', 'LIKE', '%' . $this->search . '%')
      ->orWhere('slug', 'LIKE', '%' . $this->search . '%')
      ->orWhere('id', 'LIKE', '%' . $this->search . '%')
      ->orderBy($this->orderBy, $this->orderAsc ? 'asc' : 'desc')
      ->paginate($this->perPage);

So, at order by clause, you need to pass post_stats.view. I think that this can help you.