I have two tables books and publishers. publisher_id is primary key in publishers table and foreign key in books table. In index.blade.php of books, I want to create link for sorting of columns of book index. here is my that part of index.blade.php view where I am creating links.
<table class="table"> <tr> <th><a href="{{ route('books-index',['sort' => 'name','direction' => 'asc']) }}">Name</a></th> <th><a href="{{ route('books-index',['sort' => 'published_date','direction' => 'asc']) }}">Published Date</a></th> <th><a href="{{ route('books-index',['sort' => 'publisher_name','direction' => 'asc']) }}">Publisher Name</a></th> <th>Category</th> <th>Author</th>
And in BooksController.php, I have my index method in which sorting part is given below
public function index(Request $request) { $input = $request->all(); $sort = 'created_at'; $direction = 'desc'; if(isset($input['sort'])){ $sort = $input['sort']; } if(isset($input['direction'])){ $direction = $input['direction']; } $books = Book::join('publishers','publishers.id','books.publisher_id') ->orderby($sort,$direction)->select('books.*')->paginate(5);
Below is given my index view of books
But When I click on publisher_name for sorting, it gives me an error like this.
QueryException in Connection.php line 770: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'publisher_name' in 'order clause' (SQL: select `books`.* from `books` inner join `publishers` on `publishers`.`id` = `books`.`publisher_id` order by `publisher_name` asc limit 5 offset 0)
So what can I do to solve it?
Advertisement
Answer
You can alias publishers.name
to publisher_name
, so that mysql can find the alias column:
$books = Book::join('publishers','publishers.id','books.publisher_id') ->orderBy($sort,$direction)->selectRaw('books.*, publishers.name AS publisher_name')->paginate(5);