Laravel 5.6 Polymorphic relation with whereHas

Question

I am facing an issues in Polymorphic relation where I can not make whereHas to work. Basically I have a "where" condition which i want to apply. The relation code is working fine to return the related models but it returns errors once applying the whereHas.

Below is the code

Orders Class:

class Order extends Model
 {

  // function to return orders 
  public static function getAllOrders()
  { 


    return $orders = Order::with('part.pcategory')->whereHas('part', function ($query) 
         {
                  $query->where('cat_id',4);
         })->get();
  }

  // the relation 
  public function part()
  { 

  return $this->morphTo(null,'department_short_code','part_stock_number','stock_number', 'dep_short_code');
  }

 }

SFD Parts Class:

class sfd_part extends Model
{

  public function orders()
    {   

    return  $this->morphMany('App\Order','part','department_short_code','part_stock_number');
   }

   public function pcategory()
    {

    return $this->belongsTo('App\Pcategories','cat_id', 'category_id');
    }

}

When i call getAllOrders() it gives the below error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'cat_id' in 'where clause' (SQL: select count(*) as aggregate from orders where exists (select * from orders as laravel_reserved_0 where laravel_reserved_0.id = laravel_reserved_0.part_stock_number and cat_id = 2 and laravel_reserved_0.id = laravel_reserved_0.part_stock_number and cat_id = 2))

Database tables structure

The data am trying to get is a list of orders with each part related. Then i get the category name which is related to this part. Also i want to filter this list by using whereHas where i get for example all orders which came for a specific category which.

Orders table has the orders which linked with part. this part can be in any of the 3 tables, thats why i used Polymorphic relation relaying on two keys department_short_code and part_stock_number

Answer 1

There are some issues using whereHas i don't why. this situation can be handled in other way though

you might not find where method in the editors but it can be used and it works accordingly. use tosql() to further check the query.

Order::with('part.pcategory')->where( function ($query) {
        $query->whereIn('part_id', function ($query) {
            $query->select('id')
                ->from('sfd_part')
                ->where('cat_id',4);
        });
    })->get();