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

N69S · Accepted Answer · 2020-08-16 04:14:44Z

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();

edited Aug 16, 2020 at 4:14

answered Aug 16, 2020 at 4:08

N69S

17.3k4 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alireza Behnamnik · Accepted Answer · 2020-08-16 05:27:06Z

0

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

answered Aug 16, 2020 at 5:27

Alireza Behnamnik

3361 gold badge4 silver badges21 bronze badges

2 Comments

N69S Over a year ago

that will not show all the games for each team, even if the first, for example, doesnt play one of the games. Also, this bad structure will hunt you further in your development. What happens if a game gets deleted ? its reference (id) will still be in the table. And many many more issues... too long for a comment

Alireza Behnamnik Over a year ago

this is working perfectly in my project. data won't delete from database just 'enabled' column change from 1 to 0 , user has 5 team at max so it won't be a problem for small tables like mine

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related