I have 3 different tables. Table1 is the "main" table with primary id key.

Table2 and Table3 use Table1 id as foreign key table_1_id

Let's say I have some query from Table 1:

Table1::where(...)->where(...)->get();

which gives me 100 results.

Then I want to get all the data from Table2 and Table3 with the IDs of Table1, but still have 100 results, so a structure similar to Eloquents eager loading when it attaches the relationship results to the original Model result:

Array
(
    [1] => Array
        (
            [table2_data] => Array ( <table2_rows_array that matches id1> )
            [table3_data] => Array ( <table3_rows_array that matches id1> )
        )
    [2] => Array
        (
            [table2_data] => Array ( <table2_rows_array that matches id2> )
            [table3_data] => Array ( <table3_rows_array that matches id2> )
        )
    ...
    ...
    [100] => Array
        (
            [table2_data] => Array ( <table2_rows_array that matches id100> )
            [table3_data] => Array ( <table3_rows_array that matches id100> )
        )
)

Right now I am doing separate queries with whereIn but still it is not the same structure and I need to manipulate it with PHP

CodePudding user response：

to eager load relationship , you have to :

1. get the base table data in this case table1 and create a collection for it
2. create an array with the table1 data ids
3. get related tables where the relationship column table1_id is in the ids array
4. loop over each element of the first table1 collection and add an attribute for each related table with the related data as a value where table1_id equal to the current element id

$table1 = DB::table('table1')->select('*')->get();

$table1_ids = $table1->pluck('id')->all();

$table2 = DB::table('table1')->select('*')->whereIn('table1_id', $table1_ids)->get();
$table3 = DB::table('table1')->select('*')->whereIn('table1_id', $table1_ids)->get();
$table4 = DB::table('table1')->select('*')->whereIn('table1_id', $table1_ids)->get();

foreach($table1 as $el){
    $el->table2data = $table2->where('table1_id',$el->id);
    $el->table3data = $table3->where('table1_id',$el->id);
    $el->table4data = $table4->where('table1_id',$el->id);
}

Note That :

• for the foreach we looped over a collection and we used the where() of the collection so no other query was made ! :D
• instead of N 1 queries we used a query to get the main model and then a query to get each related model, in this case, a total of 4 queries, which is independent of the data count the N

Result

one single collection with all table1 data and in each element fo this collection you ll have 3 attribute for each relation (table2_data, table3_data, table4_data..) with the element related data.