How can I achieve the following data structure with Query Builder (And not Eloquent's Relationships)?

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

mdupor

3 years ago

Level 9

I am not sure if I understood what you're trying to achieve, but from the looks of it, you want 100+100 records separated. You should approach it the other way around. So something like:

$table2Data = Table2::where('table_1_id', $id)->get();
$table3Data = Table3::where('table_1_id', $id)->get();

However, this table structure seems wrong. Can't deduce just from arbitrary table names you provided, but this should probably have to be a morph relation where Table2 and Table3 have plain IDs, whereas Table1 has FK as some_id and some_type.

1 like

Tray2

3 years ago

Level 73

It depends,

Does table two and three have more than one record with the foreign key or is it a single record?

If it's only one record of each you can use a basic join between the tables.

If it's more than one, then you need to do a query that fetches the records from table two and three with each id from table one. That is the way eager loading works, and that is what you need to write yourself if you don't want to use Eloquent relationships.

Just be careful so you don't create unecessary n+1 issues.

1 like

Ligonsker

3 years ago

Level 8

@mdupor you are very close to what I mean. @tray2 yep, I ended up doing the following in order to achieve similar output to eager loading (and avoiding N+1):

$table1 = DB::table('table1')->get();
$table1_ids = $table1->pluck('id');

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

foreach($table1 as $row){
    $row->table2_data = $table2->where('table1_id', $row->id);
    $row->table3_data = $table3->where('table1_id', $row->id);
}

Using the collection's where() method (so no more queries beyond the first 3)

jlrdw

3 years ago

Level 75

@ligonsker I suggest you get a visual query builder to help workout these things. I use MS Access query builder myself via ODBC to work out queries. But if you don't have MS Access, you can use one called SQLeo.

Note however I don't use these online, rather it's duplicated on local. For SQLeo you do need JRE installed. Maybe it's JDK, been a while.

But these are only suggestions.

Also you could study and learn from: https://www.mysqltutorial.org/

There are many examples.

Edit:

Sounds like a simple one to many but using two child tables. I have done this before with a trucks picks and drops for LTL. Just handle one at a time. I suggest working out and understanding regular SQL on these tables first.

Please or to participate in this conversation.