Full outer join query in laravel 5.4

Question

I have two table application_requests and transactions in both the tables there may be matching record or may not be. For any condition i want record from both the tables.

In transactions table there is a foreign key column application_request_id (this has value of reference of primary key in application_requests table).. If this condition matched then it should display as one row (or record).

I dont know how to achieve this in laravel.

I have tried below codes but its not working:

    $a = \DB::table('application_requests')->select('id');

    $b  = \DB::table('transactions')->select('application_request_id');

    $results = $b->union($a)->get();

    echo "<pre>";
    print_r($results);die;

transactions table is

And my application_requests table is

You use join for this behavior, not union. See https://laravel.com/docs/5.4/queries#joins for an explanation. — marijnz0r, Sep 08 '17 at 12:02
It only give matched conditon records but i want all the records @marijnz0r — VinoCoder, Sep 08 '17 at 12:21
Possible duplicate of [How to use outer full join in laravel 5.0?](https://stackoverflow.com/questions/41662033/how-to-use-outer-full-join-in-laravel-5-0) — marijnz0r, Sep 08 '17 at 12:25

score 1 · Answer 1 · edited Oct 25 '19 at 16:36

1

just like that:

DB::table('transactions')->join('application_requests', 'transactions.application_request_id', '=', 'application_requests.id', 'full outer');

edited Oct 25 '19 at 16:36

Dharman

30,962
25
85
135

answered Oct 25 '19 at 16:35

Mild Academy

11
1

score 0 · Answer 2 · edited May 16 '19 at 08:37

0

Two outer joins to show all rows from both tables:

$second = DB::table('t2')
             ->rightJoin('t1', 't1.id', '=', 't2.id')

$first = DB::table('t1')
            ->leftJoin('t2', 't1.id', '=', 't2.id')
            ->unionAll($second)
            ->get();

Source: https://stackoverflow.com/a/41662283/4587214

edited May 16 '19 at 08:37

Faramarz Qoshchi

1,292
1
13
24

answered Sep 08 '17 at 12:25

marijnz0r

934
10
23

Maulik · Answer 3 · 2017-09-11T06:39:20.707

0

    $results = DB::table('transactions')
                ->leftJoin('application_requests','transactions.application_request_id','=','application_requests.id')
                ->select('transactions.partner_id as tr_pratnerid','transactions.application_request_id as tr_applicationrequestid','transactions.class_2_1 as tr_clas21','transactions.class_2_2 as tr_clas22','transactions.class_2_3 as tr_clas23','transactions.class_3_1 as tr_clas31',
                 DO THIS TO ALL FIELDS FROM BOTH TABLE)
                ->get();
    echo "<pre>";
    print_r($results);die;

edited Sep 11 '17 at 06:39

answered Sep 08 '17 at 12:27

Maulik

877
1
8
17

1

Please include more context/explanation for this. – EJoshuaS - Stand with Ukraine Sep 09 '17 at 22:42

score 0 · Answer 4 · answered Apr 04 '23 at 22:56

this is the right code to make a full join:

$second = DB::table('t1')
             ->rightJoin('t2', 't1.t2_id', '=', 't2.id')

$first = DB::table('t1')
            ->leftJoin('t2', 't1.t2_id', '=', 't2.id')
            ->union($first)
            ->get();

Full outer join query in laravel 5.4

4 Answers4