title: Laravel relationship through an array of JSON objects
body: <p><strong>Models:</strong></p>
  
  <blockquote>
    <p><strong>User</strong> which <em>hasMany</em> <strong>groups</strong>, and <em>hasMany</em> <strong>roles</strong> <em>through</em> <strong>group</strong></p>
  </blockquote>
  
  <p>and</p>
  
  <blockquote>
    <p><strong>Group</strong> which <em>hasMany</em> <strong>users</strong>.</p>
  </blockquote>
  
  <p>These relationships are being stored on the <strong>group</strong> table, in a column called 'members', which is formatted as follows:</p>
  
  <pre><code>[
      {
          user_id: *user_id_one*
          role_id: *role_id_one*
      },
      {
          user_id: *user_id_two*
          role_id: *role_id_two*
      },
  ]
  </code></pre>
  
  <hr/>
  
  <p><strong>Requirements:</strong></p>
  
  <ul>
  <li>The models must be linked through a relationship, so that I can eagerly load them.</li>
  <li>I cannot change the data structure because a very large amount of programming has already been done regarding these models and structures. </li>
  <li>It must be done using sql version from 5.7</li>
  <li>It must be done using php 7.1, 7.2, or 7.3</li>
  </ul>
  
  <hr/>
  
  <p><strong>That I've tried:</strong></p>
  
  <p>I first tried this in the <strong>User</strong> model:</p>
  
  <pre><code>method:
  $this-&gt;hasMany('\App\Group', 'members-&gt;[*].user_id')
  
  query:
  select * from `groups` where json_unquote(json_extract(`groups`.`members`, '$."[*].user_id"')) = ? and json_unquote(json_extract(`groups`.`members`, '$."[*].user_id"')) is not null
  </code></pre>
  
  <p>Then:</p>
  
  <pre><code>method:
  $this-&gt;hasMany('\App\Group', 'members-&gt;[*].user_id')
  or
  $this-&gt;hasMany('\App\Group', 'members-&gt;$[*].user_id')
  
  query:
  Column Not Found
  </code></pre>
  
  <p>The mighty <em>staudenmeir/eloquent-json-relations</em> got me closer with</p>
  
  <pre><code>method:
  $this-&gt;hasManyJson('\App\Group', 'members-&gt;[*].user_id')
  
  query:
  select * from `groups` where json_contains(`groups`.`members`, ?, '$."[*].user_id"')
  </code></pre>
  
  <p><hr/>
  These were all tried with the normal primary key and custom attributes which returned:</p>
  
  <ul>
  <li>"JSON_ARRAY(<em>primary_key</em>)"</li>
  <li>"JSON_OBJECT('id',<em>primary_key</em>)"</li>
  <li>"['<em>primary_key</em>']"</li>
  <li>"{'id': '<em>primary_key</em>'}"</li>
  </ul>
  
  <p>However these all were escaped with quotes.</p>
  
  <hr/>
  
  <p><strong>Already Considered:</strong></p>
  
  <p>Here is an example of a working attribute</p>
  
  <pre><code>public function getGroupsAttribute()
  {
      return Group::whereRaw("JSON_CONTAINS(members-&gt;'$[*].user_id', JSON_ARRAY('{$this-&gt;id}'))")-&gt;get();
  }
  </code></pre>
  
  <hr/>
  
  <p>I figured before I went off and made a custom relationship I would ask here. I could probably do something with a scope to apply something like the following (with a relationship that just returned all groups) but it would be the same amount of work and less clean.</p>
  
  <pre><code>-&gt;with(['groups' =&gt; function ($query) use ($user) {
     $query-&gt;whereRaw("JSON_CONTAINS(members-&gt;'$[*].user_id', JSON_ARRAY('{$user-&gt;id}'))")
  }])
  </code></pre>