title: How to get all users with all their roles in a many to many relation based sql query?
body: <p><strong>Goal</strong><br />
  Get all users with all their roles.</p>
  <p><strong>Problem</strong><br />
  If one user has more than one role, i get an instance of this user for each role he has.</p>
  <p><strong>Description</strong><br />
  I have a setup with three tables, like it is described in <a href="https://stackoverflow.com/questions/56801506/getting-all-users-except-admins-in-many-to-many-relationship">this stackoverflow post</a> for example.
  A <code>user</code> table, a <code>role</code> table and a <code>role_user</code> table.
  Now i want to query all users with their roles with this query:</p>
  <pre><code>$allUsers = DB::table('users')
              -&gt;join('role_user', 'role_user.user_id', '=', 'users' . '.id')
              -&gt;join('roles', 'roles.id', '=', 'role_user.role_id')
              -&gt;select(['users.uuid', 'users.username', 'users.last_online_at', 'roles.role', 'roles.id as role_id'])
              -&gt;get();
  </code></pre>
  <p>To me it looks exactly like the plain MYSQL query proposal which made Lizesh Shakya in the second post of the stackoverflow post i already mentioned above.
  Except for the fact that i use Laravel. And i know, that i could solve it with the user model with the appropriate <code>belongsToMany</code>methods are set in it and the roles model. But i want to understand it a bit deeper and therefore i try it that way.
  If i have three users and if one user has the roles &quot;editor&quot; and &quot;author&quot; for example, the query returns four users. For each role one.</p>
  <pre><code>Illuminate\Support\Collection {#1267 ▼
    #items: array:4 [▼
      0 =&gt; {#1273 ▼
        +&quot;uuid&quot;: &quot;fc8bbd29-9005-4f05-9706-a437933d3bc2&quot;
        +&quot;username&quot;: &quot;admin&quot;
        +&quot;last_online_at&quot;: &quot;2021-01-16 19:01:37&quot;
        +&quot;role&quot;: &quot;admin&quot;
        +&quot;role_id&quot;: 1
      }
      1 =&gt; {#1272 ▼
        +&quot;uuid&quot;: &quot;d957e5af-f8f3-4a07-a2c3-eabecbdda618&quot;
        +&quot;username&quot;: &quot;author1&quot;
        +&quot;last_online_at&quot;: &quot;2021-01-16 19:01:37&quot;
        +&quot;role&quot;: &quot;author&quot;
        +&quot;role_id&quot;: 3
      }
      2 =&gt; {#1282 ▼
        +&quot;uuid&quot;: &quot;dc94d900-bbaf-4605-b8d8-6bab5fb43b70&quot;
        +&quot;username&quot;: &quot;editor&quot;
        +&quot;last_online_at&quot;: &quot;2021-01-16 19:01:37&quot;
        +&quot;role&quot;: &quot;editor&quot;
        +&quot;role_id&quot;: 2
      }
      3 =&gt; {#1281 ▼
        +&quot;uuid&quot;: &quot;d957e5af-f8f3-4a07-a2c3-eabecbdda618&quot;
        +&quot;username&quot;: &quot;author1&quot;
        +&quot;last_online_at&quot;: &quot;2021-01-16 19:01:37&quot;
        +&quot;role&quot;: &quot;editor&quot;
        +&quot;role_id&quot;: 2
      }
    ]
  }
  </code></pre>
  <p>So where is my fault? :)</p>