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

Procession

can you show me your table structure and what data you want to get ?? – Greiner

@Greiner i have updated my question. I hope its clear now – Procession

Laravel 5.8.27 adds whereHasMorph(): laravel.com/docs/… – Armourer

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();

Inoculation

Not yet the solution but this fix is doing the job partially – Procession

Issue is whereHas method doesn't work on polymorphic relations – Ruffina

This also returns orders whose part ids have part_type different then sfd_part class. Including same ids in sec_partsand rmf_parts table. – Chenault

Answer 2

You can safely and cleanly use the below code. You are out of Eloquent, but still using laravel functions.

Order::with('part.pcategory')
    ->leftJoin('sfd_parts','orders.part_id','=','sfd_parts.id')
    ->where('orders.part_type','=',sfd_parts::class)
    ->where('sfd_parts.cat_id','=',4)
    ->get();

Chenault