Encapsulate support methods for next_result() in Laravel

gitbox 1970-01-01

When using Laravel for database operations, we are mostly used to using Eloquent ORM or Query Builder to handle business logic. But when we face more complex database operations, such as Stored Procedures returning multiple result sets, the next_result() function becomes a key tool.

This article will explore how to encapsulate next_result() support methods in Laravel to improve the flexibility of database operations, especially in multi-result processing scenarios.

1. Problem background

Some database operations return multiple result sets (such as multiple SELECTs in MySQL stored procedures), which is particularly common in some complex reports or multi-stage query scenarios. Laravel's default database abstraction layer does not provide direct support for next_result() , so we need to manually encapsulate it.

2. Example of scenarios using next_result()

Suppose you have the following MySQL stored procedure:

 DELIMITER //
CREATE PROCEDURE GetUserStats(IN userId INT)
BEGIN
    SELECT * FROM users WHERE id = userId;
    SELECT COUNT(*) AS post_count FROM posts WHERE user_id = userId;
END //
DELIMITER ;

Calling this procedure will return two result sets: one is user information and the other is the number of articles for that user.

3. Encapsulation scheme in Laravel

1. Extend database connection class

We can create a helper class to handle multiple result sets.

 namespace App\Support;

use Illuminate\Support\Facades\DB;

class MultiResultSet
{
    protected $connection;

    public function __construct()
    {
        $this->connection = DB::connection()->getPdo();
    }

    public function callProcedure(string $procedure, array $bindings = [])
    {
        $stmt = $this->connection->prepare("CALL {$procedure}");
        $stmt->execute($bindings);

        $results = [];

        do {
            $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            if ($result) {
                $results[] = $result;
            }
        } while ($stmt->nextRowset());

        return $results;
    }
}

2. Use examples

Call this class to handle the return of multi-result sets:

 use App\Support\MultiResultSet;

$multi = new MultiResultSet();
$results = $multi->callProcedure('GetUserStats(?)', [1]);

$user = $results[0][0] ?? [];
$postCount = $results[1][0]['post_count'] ?? 0;

3. Configuration precautions

Make sure you are using a database driver that supports multiple result sets (such as mysql ).
If you encounter a MySQL server has gone away error in Laravel, you need to check the max_allowed_packet and connection timeout configuration.

4. Suggestions for improving code reusability

To make this feature more versatile, we recommend that you:

Service Provider or Facade encapsulated as Laravel;
Add logging and exception handling;
Format the returned results (such as converting to DTO or Collection).

V. Conclusion

Although Laravel does not directly support next_result() by default, we can still gracefully handle multi-result set scenarios by manually encapsulating the underlying PDO operation. This method is particularly suitable for database interaction requirements during migration of complex queries, reporting systems or legacy systems.

To further expand this feature, such as integrating Laravel's event listening, logging or caching support, you can also visit https://gitbox.net for more code instances and practical experience sharing.

next_result