如何基于PHP和MySQL实现REST API资源扩展并优化查询性能？

Great question! Let's break this down step by step, especially since you're working with CQS/CQRS and native SQL for performance—those constraints are key here.

1. Where to trigger the expansion logic? #

You want to handle this in your read service layer, not the REST controller. Here's why:

• Controllers should stick to request handling: parsing incoming params (like expand=contract), validating them against the allowed expandable list, and delegating to the appropriate query handler.
• Read services are designed for efficient data retrieval and shaping—this aligns perfectly with CQS/CQRS principles, keeping command/query concerns separated. The controller doesn't need to know how the data is assembled, just that it gets the right response back.

2. How to avoid exponential query growth (the N+1 problem)? #

Your concern about looping through each customer to fetch contracts is totally valid—that's a classic N+1 anti-pattern and will kill performance at scale. But there are two solid, production-friendly alternatives that use minimal SQL queries:

Option A: Batch fetch related data + in-memory assembly

This is the most common approach for one-to-many relationships:

1. First, run a single query to fetch all your core customers:

   SELECT id FROM customers WHERE <your filters here>;
   

2. Collect all the customer IDs from that result, then run a single batch query to fetch all related contracts in one go:

   SELECT * FROM contracts WHERE customer_id IN (<list of customer IDs>);
   

3. In your read service, use a hash map (dictionary) to group contracts by customer_id. Then loop through your customer list and attach the matching contracts to each customer.

This only uses 2 SQL queries total, regardless of how many customers you have—way better than N+1.

Option B: JOIN + application-level grouping

If you prefer a single SQL query, use a LEFT JOIN to pull customers and their contracts together, then group the results in your application:

1. Run a JOIN query (note: this will return one row per contract, with duplicated customer data):

   SELECT c.id AS customer_id, c.*, ct.* 
   FROM customers c
   LEFT JOIN contracts ct ON c.id = ct.customer_id
   WHERE <your filters here>;
   

2. In your read service, iterate through the result set and build customer objects, grouping contracts under their respective customer ID. Use a hash map to track which customers you've already processed so you don't create duplicates.

This uses 1 SQL query and leverages the database's efficient JOIN logic. Just be mindful that if a customer has many contracts, the result set will have more rows—but modern databases handle this well with proper indexing.

3. Is the "loop and fetch per customer" approach feasible? #

Short answer: Only for tiny datasets, never in production. Even with 100 customers, you're looking at 101 queries. As your user base grows, this will crush your database with connection overhead and repeated query execution. It's a quick fix for testing, but not scalable.

Quick recap for your stack #

• Controller: Validate expand params (ensure it's in the expandable list), pass the request to the read service.
• Read service: If expand=contract is requested, use either batch fetch + in-memory assembly or JOIN + grouping to build the expanded response. If no expansion is needed, just return the core customer data.
• Response: Format the final data with the data array and expandable field as shown in your examples.

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