技术问询：如何查询特定提供者下用户多问题及关联客户信息的服务提供者数据

Hey there! Let’s tackle your two query needs step by step — I’ll cover both raw SQL and ORM approaches (using Laravel Eloquent since you referenced hasMany/belongsTo relationships, which is super common in that ecosystem):

问题1：从特定提供者处检索某一用户的多个问题 #

First up, let’s assume your basic table structure looks like this:

• providers: Stores service provider details (with id as primary key)
• questions: Tracks all questions, with provider_id (link to provider) and customer_id (link to the customer who asked)
• customers: Customer profile data

Raw SQL Solution #

If you want to pull all questions from a specific customer (e.g., customer_id = 456) that were sent to a specific provider (e.g., provider_id = 123), use this query:

SELECT q.*
FROM questions q
-- Join to ensure we're targeting the right provider (optional but adds validation)
JOIN providers p ON q.provider_id = p.id
WHERE p.id = 123
  AND q.customer_id = 456;

Need to include customer details too? Just add a join to the customers table:

SELECT q.*, c.name, c.email
FROM questions q
JOIN providers p ON q.provider_id = p.id
JOIN customers c ON q.customer_id = c.id
WHERE p.id = 123
  AND q.customer_id = 456;

Eloquent ORM Solution #

Assuming your models have the right relationships set up:

• Provider model: public function questions() { return $this->hasMany(Question::class); }
• Question model: public function customer() { return $this->belongsTo(Customer::class); }

You can query directly from the Provider model:

// Get all questions from customer 456 for provider 123
$questions = Provider::find(123)
    ->questions()
    ->where('customer_id', 456)
    ->get();

// Include customer details with each question to avoid N+1 queries
$questions = Provider::find(123)
    ->questions()
    ->where('customer_id', 456)
    ->with('customer')
    ->get();

Or skip the Provider lookup and query directly from the Question model if you already know the IDs:

$questions = Question::where('provider_id', 123)
    ->where('customer_id', 456)
    ->with('customer')
    ->get();

问题2：获取高评论量服务提供者、所有客户问题及客户信息 #

Got it, your goal is to fetch providers with lots of reviews, plus all questions those providers received from customers, along with each customer’s extra details. First, let’s define "lots of reviews" — I’ll use 50 as a threshold, but you can adjust that to your needs.

Raw SQL Solution #

Assuming you have a reviews table linked to providers via provider_id, here’s how to pull everything in one query:

SELECT
    p.id AS provider_id, p.name AS provider_name,
    q.id AS question_id, q.content AS question_content,
    c.id AS customer_id, c.name AS customer_name, c.phone AS customer_phone -- Add any customer fields you need
FROM providers p
-- First, filter providers with >=50 reviews
JOIN (
    SELECT provider_id, COUNT(*) AS review_count
    FROM reviews
    GROUP BY provider_id
    HAVING review_count >= 50
) r ON p.id = r.provider_id
-- Join all questions linked to the provider (use LEFT JOIN if you want providers even with no questions)
LEFT JOIN questions q ON p.id = q.provider_id
-- Join customer details for each question
LEFT JOIN customers c ON q.customer_id = c.id
-- Order by provider, then most recent questions first
ORDER BY p.id, q.created_at DESC;

Swap LEFT JOIN for INNER JOIN if you only want providers that have at least one question.

Eloquent ORM Solution #

With your model relationships set (add public function reviews() { return $this->hasMany(Review::class); } to the Provider model), you can use eager loading to avoid performance hits from N+1 queries:

// Fetch providers with >=50 reviews, plus their questions and each question's customer
$providers = Provider::whereHas('reviews', function ($query) {
    $query->groupBy('provider_id')
          ->havingRaw('COUNT(*) >= 50'); // Adjust the threshold here
})
->with(['questions' => function ($query) {
    // Optional: Sort questions by most recent first
    $query->orderBy('created_at', 'desc');
}, 'questions.customer']) // Eager load customer data for each question
->get();

Then you can loop through the results easily:

foreach ($providers as $provider) {
    echo "Provider: {$provider->name}\n";
    foreach ($provider->questions as $question) {
        echo "  Question: {$question->content}\n";
        echo "  Asked by: {$question->customer->name} (Phone: {$question->customer->phone})\n";
    }
}

If "lots of reviews" refers to reviews on the questions (not the provider itself), just adjust the whereHas to target questions.reviews instead — feel free to tweak this logic to fit your exact setup!

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