0

I'm trying to sort my data just like:specific item to be first and then to sort the rest of the items in laravel.

So in my case,

colors table:

id    A        B  
1   cyan    deep green
2   green   dark red
3   yellow  pink
4   red     light green
5   blue    black

ColorController.php

$keyword = $request->input('keyword');
$query = Color::query(); 

if(!empty($keyword)){
$query->where('A','like','%'.$keyword.'%')
->orWhere('B','like','%'.$keyword.'%');

$data = $query->orderBy('A', 'ASC')->paginate(4);

then in the search bar, if I type "green", my website shows:

cyan(deep green)
green(dark red)
red(light green)

But, I want to sort A column matched item first, like:

green(dark red)
cyan(deep green)
red(light green)

So I rewrote controller like:

$data = $query->orderByRaw("IF('A' = $keyword) DESC")->paginate(4);

But, it just gets error.

[additional info]

I also tried

$data = $query->orderByRaw("('A' = '%$keyword%') DESC , B asc")->paginate(4);

It doesn't produce error but $keyword is produced as strings, not variable. If I take the ' ' out of $keyword, it produces error "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'green' in 'order clause'".

So how can I achieve this? Thank you.

Tieria
  • 333
  • 3
  • 11

1 Answers1

1

Try this as your orderBy query:

$data = $query->orderByRaw("(A like '%$keyword%') DESC")->paginate(4);

try 'A' if the above produces a column error.

Uzair Riaz
  • 909
  • 1
  • 5
  • 12
  • I tried your way, but didn't sort as I desired. Then I tried `$data = $query->orderByRaw("('A' = '%$keyword%') DESC , B asc")->paginate(4);` then it just worked $keyword as string, not variable. So I changed `'%$keyword%'` to `%$keyword%` then it produces error. So the problem is that I can't call $keyword as variable. – Tieria May 07 '20 at 02:06
  • I changed again `$data = $query->orderByRaw("A = '$keyword' DESC, B ASC")->paginate(4);` then now properly it worked! Your suggestion helped me alot! – Tieria May 07 '20 at 04:12