Laravel Multi-tenant database, query multiple databases at the same time

To achieve querying multiple databases simultaneously in a Laravel multi-tenant setup, you can use Laravel's database connection functionality to switch between connections dynamically. Here's a step-by-step solution to combine datasets from multiple databases and paginate the results:

1. Define Multiple Database Connections: Ensure your config/database.php is correctly set up with multiple connections, as you have already done.

2. Create a Service to Handle Multi-Database Queries: Create a service class that will handle querying multiple databases and combining the results.

3. Query Each Database and Combine Results: Use Laravel's DB facade to switch between connections, query each database, and combine the results.

4. Paginate the Combined Results: Use Laravel's LengthAwarePaginator to paginate the combined results.

Here's an example implementation:

Step 1: Define Multiple Database Connections

Your config/database.php is already set up correctly.

Step 2: Create a Service Class

Create a service class, e.g., MultiTenantService.php, in the app/Services directory.

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Pagination\Paginator;

class MultiTenantService
{
    protected $connections = ['4D_VIC_HUB', '4D_WA_HUB'];

    public function getCombinedData($table, $perPage = 15, $page = null)
    {
        $results = collect();

        foreach ($this->connections as $connection) {
            $data = DB::connection($connection)->table($table)->get();
            $results = $results->merge($data);
        }

        // Paginate the combined results
        $page = $page ?: (Paginator::resolveCurrentPage() ?: 1);
        $total = $results->count();
        $results = $results->forPage($page, $perPage);

        return new LengthAwarePaginator($results, $total, $perPage, $page, [
            'path' => Paginator::resolveCurrentPath(),
        ]);
    }
}

Step 3: Use the Service in Your Controller

In your controller, you can now use this service to get combined data and paginate it.

namespace App\Http\Controllers;

use App\Services\MultiTenantService;
use Illuminate\Http\Request;

class MultiTenantController extends Controller
{
    protected $multiTenantService;

    public function __construct(MultiTenantService $multiTenantService)
    {
        $this->multiTenantService = $multiTenantService;
    }

    public function index(Request $request)
    {
        $table = 'your_table_name'; // Replace with your table name
        $perPage = 15; // Number of items per page
        $page = $request->input('page', 1);

        $data = $this->multiTenantService->getCombinedData($table, $perPage, $page);

        return view('your_view', compact('data'));
    }
}

Step 4: Display Paginated Results in Your View

In your Blade view, you can display the paginated results and render the pagination links.

<!-- your_view.blade.php -->

@foreach ($data as $item)
    <div>{{ $item->column_name }}</div> <!-- Replace with your column name -->
@endforeach

{{ $data->links() }}

This approach allows you to query multiple databases, combine the results, and paginate them efficiently. Adjust the table name and column names as per your schema.