phpmysqllaravelunion-alllaravel-pagination

Laravel fetch data from two tables without join with pagination


I want to fetch results from two tables properties and properties_x where properties.address or properties_x.address_x like test with laravel pagination.

There is no foreign key relationship between these two tables.

properties

id  name    address
1   test1   
2   test2   
3   test3   
4   test3   test
5   test4   test

properties_x

id  name    address
1   test1_x test
2   test2_x 
3   test3_x 
4   test3_x test
5   test4_x 

    Expected results:
    name     address
    test3    test
    test4    test
    test1_x  test
    test3_x  test

Solution

  • Use union all to union two table's datas,

    And get the columns from these datas in DB, so you can use pagination.

    try it like this:

    $p1 = DB::table('properties')
            ->where('address', 'test')
            ->select('name', 'address');
    
    $p2 = DB::table('properties_x')
             ->where('address', 'test')
             ->select('name', 'address');
    
    $p = $p1->unionAll($p2);
    
    DB::table(DB::raw("({$p->toSql()}) AS p"))
    ->mergeBindings($p)
    ->select('name', 'address')
    ->paginate(10);