1

Given the table structures below:

table users
  - id
  - name

table group_user
  - id
  - group_id
  - user_id

table groups
  - id
  - name

table events
  - id
  - name
  - group_id

We can see that a User has relations to Event objects, however it has to pass through the many-to-many relationship of a User to a Group.

I'm not sure what kind of relationship this is called... Is it a has-many-through-many-to-many since it has many Event objects through a many-to-many relationship of users-group_user-groups?

In any case, how do I compose a Laravel query for this?

dsapalo
  • 1,819
  • 2
  • 19
  • 37

1 Answers1

0

Given $id is the ID of the user performing the call, the SQL query that we are looking for looks something like this:

SELECT * FROM events WHERE group_id IN (
    SELECT group_id FROM group_user WHERE user_id = $id
);

So if in your group_user table, we find that the user with user_id = 1 has the following group relations group_id = [1, 2, 3, 4, 5], the resulting query looks like this: SELECT * FROM events WHERE group_id IN [1, 2, 3, 4, 5]; which are what we are looking for (the events related to those groups).

  1. Open up vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php

  2. Insert the following code after the hasMany method.

        /**
     * Define a many-through-many relationship.
     *
     * @param  string  $related
     * @param  string  $through
     * @param  string  $pivotTable
     * @param  string  $pivotKey
     * @param  string  $pivotThroughKey
     * @param  string|null  $firstKey
     * @param  string|null  $secondKey
     * @return \Illuminate\Database\Eloquent\Relations\HasManyThrough
     */
     public function manyThroughMany($related, $through, $pivotTable, $pivotKey, $pivotThroughKey, $firstKey = null, $secondKey = null)
     {
         $relatedModel = new $related; // App\Event
         $relatedTable = $relatedModel->getTable(); // events

         $firstKey = $firstKey ?: $this->getForeignKey(); // event_id

         $throughModel = new $through; // App\Group
         $throughTable = $throughModel->getTable(); // groups

         $secondKey = $secondKey ?: $throughModel->getForeignKey(); // group_id

         return $relatedModel
             ->whereIn($secondKey, function ($query) use ($pivotTable, $pivotKey, $pivotThroughKey)
             {
                 return $query
                         ->from($pivotTable)
                         ->where($pivotKey, '=', $this->id)
                         ->select($pivotThroughKey);
             })
             ->select($relatedTable . '.*');
      }

  3. Now for your model. To use the many-through-many relation, go to your User model and add the following.

        public function events()
    {
        return $this->manyThroughMany('App\Event', 'App\Group', 'group_user', 'user_id', 'group_id');
    }

The parameters are as follows:

  1. The name of the target model you are interested in (i.e. App\Event).
  2. The name of the secondary model you need to pass through (i.e. App\Group).
  3. The name of the pivot table (i.e. group_user).
  4. The pivot table key referencing the current model (i.e. user_id).
  5. The pivot table key referencing the secondary model (i.e. group_id).

Disclaimers

I don't really know if there's such a thing as a many-through-many relationship, or what the name for it is. This is just the code that worked for me, so I wanted to share it with people who might have the same dilemma as I did.

If there is a simpler way of doing it, please answer! If there are things I can improve, please comment. Thanks.

dsapalo
  • 1,819
  • 2
  • 19
  • 37
  • Did you ever find a more supported way of doing this? I am in a similar situation where I have Cars that have Features but those Features are grouped by Feature Types (i.e. Interior, Exterior, Safety, etc...) – Spechal Dec 13 '16 at 23:26
  • No I haven't been doing much PHP or involvement with Laravel lately. I hope my explanation was clear enough for your use though! – dsapalo Dec 14 '16 at 02:25
  • Unfortunately not, when I load it up, there are unused variables. – Spechal Dec 14 '16 at 05:36
  • You can put up a question so that others can understand your case. – dsapalo Dec 14 '16 at 15:45
  • I have, at http://stackoverflow.com/questions/41133533/laravel-hasmany-many-to-many-to-one-eloquent?noredirect=1#comment69475100_41133533 – Spechal Dec 15 '16 at 02:57
  • 2
    Don't do this! Never edit files inside of the vendor/ folder as they will be overriden by performing `composer update`.. also the vendor folder is in Laravel's default .gitignore file so your changes won't be commited to git.. – Rico Ocepek Apr 20 '17 at 18:29
  • 1
    Hello- 6 years after reading the code that I recommended, I second @RicoOcepek message not to edit things on the vendor folder, as he has explained above. – dsapalo Jul 21 '22 at 05:15