Laravel多表关联查询需求：筛选指定供应商未交付订单

Hey there! Let's break down how to build this query while keeping your model relationships intact. First, let's make sure our model associations are set up correctly (you might already have these, but let's confirm):

Step 1: Verify Model Relationships #

Add these methods to your respective models if you haven't already:

Order Model #

public function orderLines()
{
    return $this->hasMany(OrderLine::class);
}

OrderLine Model #

public function orderLineArticles()
{
    return $this->hasMany(OrderLineArticle::class);
}

OrderLineArticle Model #

public function article()
{
    return $this->belongsTo(Article::class);
}

// If you have a Choice model for the choice_id attribute
public function choice()
{
    return $this->belongsTo(Choice::class);
}

Article Model #

public function supplier()
{
    return $this->belongsTo(Supplier::class);
}

Step 2: Build the Query #

We'll use whereHas to filter orders that have the specific unfulfilled articles from suppliers with handling_id = 1, and with to eager-load all the related data we need to avoid N+1 queries.

$orders = Order::query()
    // Filter orders that have at least one matching order line article
    ->whereHas('orderLines.orderLineArticles', function ($query) {
        $query->where('settled', false) // Adjust this value if "unsettled" uses a different flag (e.g., 0)
            ->whereHas('article.supplier', function ($subQuery) {
                $subQuery->where('handling_id', 1);
            });
    })
    // Eager-load all necessary relationships to fetch related data
    ->with([
        'orderLines.orderLineArticles' => function ($query) {
            // Only load the relevant articles that meet our criteria
            $query->where('settled', false)
                ->whereHas('article.supplier', function ($subQuery) {
                    $subQuery->where('handling_id', 1);
                })
                ->with(['article.supplier', 'choice']); // Load article's supplier and choice info
        },
        // Add other order relationships you need (e.g., customer)
        // 'customer'
    ])
    // Optional: Select specific fields from orders if needed
    // ->select('id', 'code_id', 'customer_id')
    ->get();

Step 3: Explanation #

• whereHas('orderLines.orderLineArticles'): This ensures we only get orders that have at least one order line article matching our conditions.
• Nested whereHas('article.supplier'): Drills down to filter articles linked to suppliers with handling_id = 1.
• with(): Eager-loads the related data so we don't hit the database multiple times. We also add a closure to the orderLineArticles eager load to only fetch the relevant articles (instead of all articles for the order).
• The choice relationship is included in the eager load if you have a Choice model to get that dimension info.

Notes #

• Double-check the settled field value: If "未交付" is represented by 0 instead of false in your database, adjust where('settled', false) to where('settled', 0).
• If you need to retrieve all articles for the order (not just the matching ones), remove the closure inside the orderLines.orderLineArticles eager load.

内容的提问来源于stack exchange，提问作者Martijn