Combining Multiple Data Sources with Union Queries in Laravel

The Problem

Sometimes you need to merge data from different tables or queries into a single result set. Maybe you’re combining default configuration settings with user-specific overrides, or pulling together related records from separate tables. Writing separate queries and merging results in PHP is messy and inefficient.

The Pattern

Laravel’s union() method lets you combine multiple queries into one result set at the database level:

use Illuminate\Support\Facades\DB;

$defaultSettings = DB::table('system_settings')
    ->select('id', 'category', 'label')
    ->where('active', true);

$userSettings = DB::table('user_preferences')
    ->select('id', 'category', 'name as label')
    ->where('user_id', $userId);

$combined = $defaultSettings
    ->union($userSettings->toBase())
    ->get();

Both queries must return the same number of columns with compatible data types. Use toBase() to convert Eloquent queries to query builder instances before the union.

Real-World Example

Building a dropdown of available templates: show built-in system templates plus user-created custom templates.

$systemTemplates = DB::table('system_templates')
    ->selectRaw('id')
    ->selectRaw('name')
    ->selectRaw('? as source', ['System'])
    ->where('enabled', true);

$userTemplates = DB::table('custom_templates')
    ->select('id', 'name')
    ->selectRaw('? as source', ['Custom'])
    ->where('created_by', auth()->id());

$allTemplates = $systemTemplates
    ->union($userTemplates->toBase())
    ->orderBy('name')
    ->get();

When to Use It

• Merging defaults + overrides: System configs + user-specific settings
• Multi-table aggregation: Combining similar data from legacy + current tables
• Fallback chains: Primary source + backup source in one query

Watch Out For

• Column count must match: Use selectRaw('? as placeholder', [null]) for missing columns
• Type compatibility: MySQL will coerce types, but keep them consistent
• Performance: Union deduplicates by default (like SQL UNION). Use unionAll() if you want duplicates and better performance

Quick Tip

If you need to apply filters or pagination to the unified result, wrap it in a subquery:

$union = $defaultSettings->union($userSettings->toBase());

$results = DB::table(DB::raw("({$union->toSql()}) as combined"))
    ->mergeBindings($union)
    ->where('category', 'notifications')
    ->paginate(20);

Union queries keep your data merging at the database level where it belongs—fast, efficient, and clean.