CODEWITHSUNDEEP
phplaravellaravel-5
Michał Lipa
Michał Lipa

Reputation: 978

Laravel - How to sort, order appended fields

I'm creating a function to search books in specific genre. Everything works fine except sorting, ordering by 'stars' which is appended in model.

Function:

 $genre = Genre::find($genre_id);


    $phrase = $request->search;
    $sort = $request->sort;
    $order = $request['order'];


    if (!isset($sort) || !in_array($sort, ['title', 'stars', 'created_at', 'author_id']))
        $sort = 'title';

    if (!isset($order) || !in_array($order, ['DESC', 'ASC']))
        $order = 'DESC';


    $result['name'] = $genre->name;
    $result['results'] = Book::with('authors')->whereGenre_id($genre_id)->where('club_id', '=', $club_id)
        ->where(function ($query) use ($phrase, $club_id, $sort, $order, $user) {

            if (isset($phrase))
                $query->where('title', 'LIKE', '%' . $phrase . '%');
        })
        ->orderBy($sort, $order)
        ->get();

When I try to sort by 'stars' I get an error:

Column not found: 1054 Unknown column 'stars' in 'order clause' (SQL: select * from books where genre_id = 1 and club_id = 24 and books.deleted_at is null order by stars desc)

I understand this, stars doesn't exist in database, but how I can make this working?

Upvotes: 0

Views: 3141

Answers (1)

Michał Lipa
Michał Lipa

Reputation: 978

This is final working code:

$res = Book::with('authors')
        ->where('genre_id', $genre_id)
        ->where('club_id', $club_id)
        ->where(function ($query) use ($phrase) {
            if (isset($phrase))
                $query->where('title', 'LIKE', '%' . $phrase . '%');
        })->get();

    if($order == 'ASC'){
        $res = $res->sortBy($sort);
    }else{
        $res = $res->sortByDesc($sort);
    }

    $result['results'] = $res->values()->all();

@mith I changed ->all() to ->get() and getting final result looks like this:

$result['results'] = $res->values()->all();

Laravel documentation - sortBy

Upvotes: 1

Related Questions