Laravel: get data from related tables (json array column)

Question

I have 2 related tables

teams:

| id | name     | game_id       | user_id |
|----|----------|---------------|---------|
| 1  | EA Games | ["1","2"]     | 1       |
| 2  | Nintendo | ["1"]         | 2       |
| 3  | HoG      | ["3","4","5"] | 1       |

games:

| id | name     |
|----|----------|
| 1  | Cod MW   |
| 2  | FIFA     |

Controller:

public function showManage()
{
    $teams = Teams::where('user_id', Auth::user()->id)->where('enabled', '!=', 0)->get();
    return view('teams.manage.index', ['teams' => $teams]);
}

View:

@foreach ($teams as $item)
    <div class="col-md-2 text-center">
      <div class="team-item">
          <div class="team-image">
            <img src="/images/teams/logo/{{$item->logo}}" alt="{{$item->name}}">
          </div>
          <div class="team-text">
            {{$item->name}}
          </div>
        <ul>
          <li> Game Name 1 </li>
          <li> Game Name 2 </li>
          <li> Game Name 3 </li>
        </ul>
      </div>
    </div>
@endforeach

Team Model:

<?php

namespace App;
use App\User;
use App\Games;

use Illuminate\Database\Eloquent\Model;

class Teams extends Model
{

  protected $table = 'teams';


  public function captain()
  {
      return $this->hasOne(User::class, 'id', 'user_id');
  }

  public function game()
  {
      return $this->hasMany(Games::class, 'id', 'game_id');
  }

}

Each user can have many teams with different game_ids and i wan't to show game name for each team for my user

How can i join to my game table?

Sorry for my bad English

Answer 1

I can fix it with foreach and for loops

Controller:

public function showManage()
  {
    $teams = Teams::where('user_id', Auth::user()->id)->where('enabled', '!=', 0)->get();
    $array = array();
    foreach ($teams as $v) {
      $data = $v->game_id;
      $array[$v->id] = array(
        'games' => array(
        )
      );
      $games = Games::whereIn('id', $data)->get();
      foreach ($games as $k) {
        array_push($array[$v->id]['games'], $k->name);
      }
    }
    return view('teams.manage.index', ['teams' => $teams, 'data' => $array]);
  }

View:

@foreach ($teams as $item)
    <div class="col-md-2 text-center">
      <div class="team-item">
          <div class="team-image">
            <img src="/images/teams/logo/{{$item->logo}}" alt="{{$item->name}}">
          </div>
          <div class="team-text">
            {{$item->name}}
          </div>
        <ul>
          @foreach ($data[$item->id] as $v)
            @for ($i = 0; $i < count($v); $i++)
             <li> {{$v[$i]}} </li>
            @endfor
          @endforeach
        </ul>
      </div>
    </div>
    @endforeach

Answer 2

Having a json field make it impossible to do it with a database statement.

Note that JSON columns cannot have a default value, be used as a primary key, be referenced as a foreign key or have an index. You can create secondary indexes on generated virtual columns, but it’s possibly easier to retain an indexed value in a separate field.

With the current structure, your only solution is to loop the results and do another query for the games.

Changing the structure will be a better solution where you have another table game_team with ids of teams and games as composite key. Then it will be as easy as running this (with the right belongsToMany relation)

$teams = Auth::user()->teams()->with('games')->where('enabled', '!=', 0)->get();