angelorigo's avatar

How to create Inner Join using with ?

Hi , How can i join more tables in a expression using with, can i use join ?

I have this expression : $result = MyModel::with(['TableA' => function ($query) { $query->with('TableB'); }]) ->whereMyType($indexMyType);

And i need to join more tables so i try using join but did not find the syntax yet, or there is another way ?

$result = MyModel::with(['TableA' => function ($query) { $query->with('TableB') ->join('TableC', function( $join) use( $type) { $join->on('TableC.id', '=', 'TableB.table_c_id'); ->join('TableD', function( $join) use( $type) { $join->on('TableC.id', '=', 'table_d.table_d_id'); });
}])

0 likes
2 replies
LaryAI's avatar
LaryAI
AI
1 year ago
Level 58

To achieve an inner join using Eloquent's with method, you need to understand that with is used for eager loading relationships, not for joining tables directly. However, you can use the join method directly on the query builder to perform joins. Here's how you can modify your code to include joins:

$result = MyModel::with(['TableA' => function ($query) {
    $query->with('TableB');
}])
->whereMyType($indexMyType)
->join('TableC', 'TableC.id', '=', 'TableB.table_c_id')
->join('TableD', 'TableC.id', '=', 'TableD.table_d_id')
->get();

Explanation:

  1. Eager Loading with with:

    • The with method is used to eager load relationships. In your case, TableA is being eager loaded with its relationship to TableB.

  2. Joining Tables:

    • Use the join method to perform SQL joins. In this example, TableC is joined with TableB on the condition that TableC.id equals TableB.table_c_id.
    • Similarly, TableD is joined with TableC on the condition that TableC.id equals TableD.table_d_id.

  3. Chaining Methods:

    • You can chain the join methods after the with method to include the necessary joins in your query.

  4. Fetching Results:

    • Finally, use get() to execute the query and retrieve the results.

This approach allows you to combine eager loading with direct SQL joins in Eloquent. Make sure that the relationships and foreign keys are correctly defined in your models for the eager loading to work as expected.

tisuchi's avatar

@angelorigo how about in this approach?


MyModel::with([
    'tableA' => function ($query) {
        $query->with(['tableB.tableC', 'tableB.tableD']);
    }
])->where('my_type', $indexMyType)->get();
2 likes

Please or to participate in this conversation.