Laravel Invalid parameter number in orderByRaw-CodePudding

I have a problem with laravel query.

So i would like to create a query to get similar product with 4 order type.

This is the laravel generated sql query: SQL:

select `product`.`id`, 
  `product`.`name`, 
  `product`.`slug`, `product`.`lead`, 
  `product`.`price`, 
  `product`.`status_id`, 
  `product`.`condition_id`, 
  `product`.`created_at`,
  `product_image`.`product_id`, 
  `product_image`.`img_url`, 
  `categories`.`id` as `categoryId`, 
  `categories`.`name` as `categoryName`, 
  `categories`.`category_id` as `categoryParent`, 
  `users`.`last_name` as `userFirstName`, 
  `users`.`first_name` as `userLastName` 
from `product` left join `product_image` on `product_image`.`product_id` = `product`.`id` 
left join `categories` on `categories`.`id` = `product`.`category_id` 
left join `users` on `users`.`id` = `product`.`uploader_id` 
where `product`.`id` != 16 
  and `product_image`.`cover` = 1 
  and `status_id` = 1 
  and `product`.`deleted_at` is null 
order by CASE 
  WHEN product.category_id = '17' AND product.name LIKE '%Hamburger%' THEN 1 
  WHEN product.name LIKE '%Hamburger%' THEN 2 
  WHEN product.category_id = '17' THEN 3 
  WHEN categories.category_id = '1' THEN 4 
  ELSE 5 END 
limit 10

if i try in mysql this successfully but in larvel get this eror: SQLSTATE[HY093]: Invalid parameter number

$this->products = Product::select([
        'product.id',
        'product.name',
        'product.slug',
        'product.lead',
        'product.price',
        'product.status_id',
        'product.condition_id',
        'product.created_at',
        'product_image.product_id',
        'product_image.img_url',
        'categories.id AS categoryId',
        'categories.name AS categoryName',
        'categories.category_id AS categoryParent',
        'users.last_name AS userFirstName',
        'users.first_name AS userLastName'
    ])->where('product.id', '!=', $this->product->id)
    ->orderByRaw(
        "CASE WHEN product.category_id = '" . $this->product->category_id . "' AND product.name LIKE '%" . $this->product->name . "%' THEN 1
        WHEN product.name LIKE '%" . $this->product->name . "%' THEN 2
        WHEN product.category_id = '" . $this->product->category_id . "' THEN 3
        WHEN categories.category_id = '" . $categoryParentId . "' THEN 4
        ELSE 5 END", "DESC"
    )
    ->leftJoin('product_image', 'product_image.product_id', 'product.id')
    ->leftJoin('categories', 'categories.id', 'product.category_id')
    ->leftJoin('users', 'users.id', 'product.uploader_id')->where('product_image.cover', '1')
    ->where('status_id', 1)
    ->limit($this->limit)
    ->get();

CodePudding user response：

You can't pass sorting direction to orderByRaw

and it expects and array of query parameters not more,

Use DESC in orderByRaw string instead,

->orderByRaw(
        "CASE WHEN product.category_id = '" . $this->product->category_id . "' AND product.name LIKE '%" . $this->product->name . "%' THEN 1
        WHEN product.name LIKE '%" . $this->product->name . "%' THEN 2
        WHEN product.category_id = '" . $this->product->category_id . "' THEN 3
        WHEN categories.category_id = '" . $categoryParentId . "' THEN 4
        ELSE 5 END DESC"
    )