Laravel Eloquent三张链式关联表查询问题求助

First off, let's spot the immediate problem in your query: the join condition between orders and order_carpets is wrong.

Looking at the columns you're selecting, you include orders.order_carpet_id—that tells me the relationship between these two tables should link orders.order_carpet_id to order_carpets.id, not orders.id to order_carpets.id as you currently have. That mismatch is definitely causing incorrect results (or no results at all) because you're joining on unrelated columns.

Corrected Raw Query #

Here's the fixed version of your DB facade query:

DB::table('factories')
    ->select(
        'factories.order_id',
        'factories.entry_date',
        'factories.supplier_status',
        'factories.remarks',
        'orders.order_carpet_id',
        'order_carpets.carpet_no'
    )
    ->join('orders', 'orders.id', '=', 'factories.order_id')
    ->join('order_carpets', 'order_carpets.id', '=', 'orders.order_carpet_id') // This line was fixed
    ->get();

A More Laravel-Eloquent Approach #

Since you're using Laravel, defining model relationships is usually a cleaner, more maintainable approach than writing raw joins. Here's how to set that up:

1. Define relationships in your models:

   • In app/Models/Factory.php:

     class Factory extends Model
     {
         public function order()
         {
             return $this->belongsTo(Order::class);
         }
     }
     

   • In app/Models/Order.php:

     class Order extends Model
     {
         public function orderCarpet()
         {
             return $this->belongsTo(OrderCarpet::class);
         }
     }
     

2. Query with Eloquent (with specific columns):
   If you still want to select only the columns you need, you can combine joins with Eloquent:

   Factory::select(
       'factories.order_id',
       'factories.entry_date',
       'factories.supplier_status',
       'factories.remarks',
       'orders.order_carpet_id',
       'order_carpets.carpet_no'
   )
   ->join('orders', 'orders.id', '=', 'factories.order_id')
   ->join('order_carpets', 'order_carpets.id', '=', 'orders.order_carpet_id')
   ->get();
   

   Or use eager loading for a more "Eloquent" feel (note: you need to include foreign keys in your selects for eager loading to work):

   $factories = Factory::with(['order:id,order_carpet_id', 'order.orderCarpet:id,carpet_no'])
       ->select('id', 'order_id', 'entry_date', 'supplier_status', 'remarks')
       ->get();
   
   // Transform the results to match your desired output format
   $formattedResults = $factories->map(function ($factory) {
       return [
           'order_id' => $factory->order_id,
           'entry_date' => $factory->entry_date,
           'supplier_status' => $factory->supplier_status,
           'remarks' => $factory->remarks,
           'order_carpet_id' => $factory->order->order_carpet_id,
           'carpet_no' => $factory->order->orderCarpet->carpet_no
       ];
   });
   

The main fix here is correcting that second join condition to align with your actual table structure. Always double-check your foreign key mappings when setting up joins—small typos or mismatched columns are the most common culprit here!

内容的提问来源于stack exchange，提问作者BalG Lal Shrestha