CODEWITHSUNDEEP
mysqllaravel-5
whizzzzz
whizzzzz

Reputation: 27

how to return all join table including null foreign key values

I want to join two tables yet it will only return the not nulled foreign key column

it will all only show if i use 1 as default value to all and put nothing inside the foreign id 1 row to look like its empty but the problem there is if i update one row that has default 1 value it will update all foreign id with 1 value and i don't want that to happen.

$unit = DB::table('franchises')
       ->join('units', 'franchises.id', '=', 'units.franchise_id')
       ->orderBy('units.created_at', 'desc')->paginate(20);

return view('register-unit', compact('unit'));

i have this migration query

$table->integer('franchise_id')->unsigned()->default(null); 
$table->foreign('franchise_id')->references('id')->on('franchises')->onDelete('cascade')->onUpdate('cascade');

i want it to make the all foreign key null as a default and still can return all using join. How can i attain it?

Upvotes: 1

Views: 1414

Answers (1)

GMB
GMB

Reputation: 222482

You have a relation where each unit may be related to a franchise. You may use LEFT JOIN to use a table as starting point and optionally match on the other one. Where the relation does not match, the columns of the second query will be empty.

As it is, your query returns all franchises that do relate to a unit. You could make that relation optional.

DB::table('franchises')
    ->leftJoin('units', 'franchises.id', '=', 'units.franchise_id')
    ->orderBy('units.created_at', 'desc')->paginate(20);

But since you named your variable unit and also are sorting the query results by units.created_at, I think that you want pull out all units, with optionaly the corresponding franchise. If so, you should LEFT JOIN the other way around : 

DB::table('units')
    ->leftJoin('franchises', 'franchises.id', '=', 'units.franchise_id')
    ->orderBy('units.created_at', 'desc')
    ->paginate(20);

Upvotes: 1

Related Questions