Back

Technologies:

javascriptjavascript
node.jsnode.js
avatar
Tolerim
a month ago

How can I use AdonisJS .join for a Many to Many relationship?

I'm attempting to retrieve data from three tables: Actors, Matters, and a pivot table Actor_Matter that includes pivot columns "relation" and "primary". My objective is to join the actor where the conditions of 'client' and 'true' are both met, and then order them by the client name. However, I am struggling to understand how to accomplish this task. Although I reckon I should be able to utilize the existing relationship in the model, it seems that the .join function is unaware of the model; therefore, I need to write an SQL query to make it work. Currently, my controller code only retrieves matters with matching primary clients, and I need to combine different joins to obtain all matters (possibly with null value for the client name). I have included the Actor and Matter models below, including their many-to-many relationships and pivot columns, as well as the relevant code used in the controller.
Answers(2)
avatar
Vogi
a month ago
Verified Answer
Based on your message, it seems that you are trying to retrieve data from three tables: Actors, Matters, and a pivot table Actor_Matter that includes pivot columns "relation" and "primary". You want to join the actor where the conditions of 'client' and 'true' are both met, and then order them by the client name. You mentioned that you are struggling to understand how to accomplish this task. Although you reckon you should be able to utilize the existing relationship in the model, it seems that the .join function is unaware of the model; therefore, you need to write an SQL query to make it work. You can use Laravel's Eloquent ORM to join multiple tables. Here's an example of how you can join two tables using Eloquent: 
$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->select('users.*', 'contacts.phone')
            ->get();
In this example, we are joining the users table with the contacts table on the id column of users and user_id column of contacts. We are selecting all columns from the users table and the phone column from the contacts table. To join three tables using Eloquent, you can use something like this: 
$actors = Actor::join('actor_matter', 'actors.id', '=', 'actor_matter.actor_id')
                ->join('matters', 'actor_matter.matter_id', '=', 'matters.id')
                ->where('actor_matter.relation', '=', 'client')
                ->where('actor_matter.primary', '=', true)
                ->orderBy('actors.name')
                ->get();
In this example, we are joining the actors table with the actor_matter table on the id column of actors and actor_id column of actor_matter. We are then joining the resulting table with the matters table on the matter_id column of actor_matter and the id column of matters. We are selecting all columns from the resulting table. We are filtering by where conditions of 'client' and 'true', and ordering by client name. I hope this helps! Let me know if you have any further questions.

Sources

Laravel Eloquent Join 2,3,4 Tables Example - Tuts MakeLaravel 9, 8 Joins Example Tutorial - Tuts MakeHow to perform inner join of two tables in Laravel queryphp - Laravel join tables - Stack OverflowLaravel 10/9 Joins Example Tutorial – Lara TutorialsLaravel: Pivot Table With An Example | Scratch CodePivot tables and many-to-many relationships - Laravel Daily3-way pivot table in Laravel - MediumHow to join pivot table in laravel - Stack OverflowLaravel 5: How to do a join query on a pivot table using Eloquent?Laravel 7 Eloquent Join Example Tutorial - Tuts MakeHow to query from two database tables with a condition in LaravelJoin Multiple Table Query in Laravel - Experts PHPLaravel Eloquent inner join with multiple conditionslaravel - A JOIN With Additional Conditions Using Query Builder or ...Laravel Eloquent Join Multiple Conditions - Tuts Make
avatar
Tolerim
a month ago
To query and join the Actor table where the relation and primary conditions of 'client' and 'true' are met for all Matters, you can modify the controller code as follows: 
.leftJoin('actor_matter', 'matters.id', 'actor_matter.matter_id')
  .leftJoin('actors', (query) => {
    query.on('actors.id', 'actor_matter.actor_id')
      .andOn('actor_matter.relation', '=', 'client')
      .andOn('actor_matter.primary_actor', '=', true)
  })
  .select('matters.*', 'actors.name as client_name')
  .orderBy(sortParams.sortField, sortParams.sortOrder === 'asc' ? 'asc' : 'desc')
  .paginate(page, perPage)
In this modified code, we first leftJoin the actor_matter pivot table with the Matters table on the matter_id. We then leftJoin the Actors table with the actor_id from the pivot table and add 'client' and 'true' as conditions for the relationship in the pivot table using the andOn() method. Finally, we select all the fields from Matters and the name of the client from the Actors table and paginate the result.
;