---

Real-world applications often split related data across multiple tables. You might have products for physical inventory and digital products for downloadable items. Both share common fields like name and price, but they live in different tables with different structures.

The challenge is simple: list them together, paginated, with full Eloquent relationships working. This is where most developers get stuck.

The Problem

Let’s say you run an online store. Some items are physical products that ship to customers. Others are digital downloads delivered via email. Your database design split these into two tables because they have different attributes.

The physical products table has columns for stock quantity and shipping weight. The digital products table has download URLs and license keys instead. Both tables have id, sku, name, and price in common, but they’re stored separately.

You need a single list view that shows both types. Users should be able to search, filter, and sort across all products. More importantly, when you render each product in your list, you want relationships like category and user to work without additional queries.

Why This Is Tricky

Laravel’s Eloquent ORM is powerful, but it assumes each model maps to one table. When you need data from two tables, you have to use raw SQL with UNION. That breaks the ORM.

The paginator returns raw stdClass objects, not Eloquent models. You lose access to relationships. Calling $product->category->name fails because there’s no model to call the relationship on.

This is the gap this guide fills.

The Approach

The solution has four parts. First, build queries for each table that return the same column structure. Second, union those queries together. Third, paginate the union result. Fourth, hydrate the raw results back into Eloquent models.

Let me walk through each step.

Step 1: Align Your Column Structures

The key is making both tables look identical from the union’s perspective. Map columns explicitly so each query returns the same field names.

If your products table has id, sku, name, price, stock_qty, and created_at, make your digital products query return those same fields. Use NULL or defaults for columns that don’t apply.

$productQuery = Product::select(['id', 'sku', 'name', 'price', 'created_at'])
    ->where('archived', false);

$digitalQuery = DigitalProduct::selectRaw("
    id, sku, name, price, created_at,
    'digital' as product_type,
    NULL as stock_qty
")->where('archived', false);

Notice how we add a product_type column to distinguish between physical and digital items. This becomes important when hydrating.

Step 2: Build the Union Query

Now combine both queries using UNION ALL. This preserves all rows without removing duplicates.

$unionSql = "(" . $productQuery->toSql() . ") UNION ALL (" . $digitalQuery->toSql() . ")";
$bindings = array_merge($productQuery->getBindings(), $digitalQuery->getBindings());

Create a wrapper query that the paginator can work with:

$wrapper = DB::table(DB::raw("(" . $unionSql . ") as combined"))
    ->setBindings($bindings)
    ->orderBy('created_at', 'desc');

return $wrapper->paginate($perPage);

This gives you a paginated result from two tables.

Step 3: Hydrate Back to Eloquent Models

Here’s the critical step. Raw union results aren’t Eloquent models. You need to convert each raw object back to its proper model so relationships work.

$paginator = $wrapper->paginate($perPage);

$rawItems = $paginator->items();

$hydratedItems = collect($rawItems)->map(function ($item) {
if (($item->product_type ?? null) === 'digital') {
 return DigitalProduct::find($item->id);
}
return Product::find($item->id);
});

$paginator->setCollection($hydratedItems);
return $paginator;

The paginator now contains actual Eloquent models. This is the real magic.

Step 4: Load Relationships

Now relationships work just like they would on any regular query:

$results = $this->mergedList(15);

$results->load(['category', 'user']);

foreach ($results as $product) {
 echo $product->category->name;
 echo $product->user->email;
}

This is the payoff. After all the union and hydration work, your code looks exactly like a normal Eloquent query.

Why Hydration Matters

Without this step, you’d need to manually query relationships or skip them entirely. Each product would need its own queries to load category, user, and any other relationships. That’s N+1 query problems and slow code.

With hydration, you get full Eloquent power. Relationships are lazy-loaded, eager-loaded, or whatever pattern fits your use case. The code remains clean and maintainable.

You also get access to model methods. If your Product model has formattedPrice() or isInStock() methods, they work. Traits and scopes work too.

Common Mistakes

Several things can go wrong. First, make sure both queries return the same column names. Differences in case or naming break the union. Second, always include the primary key in your select statements. You need it for the hydration step to find each record. Third, paginate before hydrating. If you hydrate first, the paginator won’t know how many total records to show.

Another issue is large datasets. If you’re unioning thousands of records, every page load hits the database twice. Consider caching results or using cursor-based pagination.

The Real-World Pattern

This pattern isn’t limited to products. I’ve seen it used for orders split by type, users split by role, and content split by format. Any time you have related data in separate tables that needs a unified view, this approach applies.

The production version would include more filters, search functionality, and sorting options passed as parameters. But the core pattern stays the same: union queries, paginate, hydrate, load relationships.

---

Tags: #Laravel #Eloquent #Database #Pagination #PHP #WebDevelopment