Luka Otočan
Reputation: 45
Filter data with pivot table Laravel
I have three table:
- Igraci (as players in English)
- Turnir (as tournament in English)
- NastupTurnir (pivot table between this two)
I need to create table where will show all the players which will be the rows in the table, while columns will be tournaments. Value of this tournaments will be point which did player won on that tournament.
I manage to did this but whit one flaw. On my table it shows also players who doesn't have value for any of the tournaments.
How can I filter players to not show them in table? Problem
My codes:
Controller.php
<?php
namespace App\Http\Controllers\Frontend;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Sezona;
use App\Igrac;
use App\TurnirPiramida;
class RangListaPiramidaController extends Controller
{
public function index()
{
$sezone = Sezona::orderBy('godina', 'desc')->get();
//$kola = TurnirPiramida::where('sezona_id', )
//dd($igraci);
return view("frontend.ranglista_piramida", compact('sezone'))->render();
}
public function show($id)
{
$sezone = Sezona::orderBy('godina', 'desc')->get();
$sezona = Sezona::findOrFail($id);
$igraci = Igrac::get();
$kola = TurnirPiramida::where('sezona_id', $id)->get();
//$ups = Igrac::find(1);
//dd($ups->igrac_piramida);
return view("frontend.ranglista_piramida_kola", compact('sezone', 'sezona', 'igraci', 'kola'))->render();
}
}
Blade file
@extends('layouts.frontend')
@section('title', 'TK Pazin | Rang-liste Piramida')
@section('content')
<!-- Page Content -->
<div class="container">
<!-- Page Heading -->
<h1 class="my-4" style="text-align:center; color: #ba3631;">Rang-lista | Piramida {{ $sezona->godina }}</h1>
<nav aria-label="breadcrumb">
<ol class="breadcrumb">
@foreach($sezone as $sezona)
<li class="breadcrumb-item"><a href="{{ route('ranglista.piramida.kola', $sezona->id) }}">{{ $sezona->godina }}</a></li>
@endforeach
</ol>
</nav>
<div>
<div class="media mb-5">
<div class="mr-3"><a href="../datoteke/Pravila-Pojedinačni-turniri.pdf" target="_blank"><i
class="far fa-file-pdf fa-3x"></i></a></div>
<div class="media-body mt-3">
<a href="/datoteke/Pravila-Piramida.pdf" target="_blank">
<h5 class="mt-0">Pravila
natjecanja u piramidi
</h5>
</a>
</div>
</div>
</div>
<div class="table-responsive">
<table class="table table-hover">
<thead class="thead-dark">
<tr>
<th scope="col">#</th>
<th scope="col">Ime i prezime</th>
@foreach($kola as $kolo)
<th scope="col">{{ $kolo->naziv }}</th>
@endforeach
<th scope="col">Ukupno</th>
</tr>
</thead>
<tbody>
@foreach($igraci as $igrac)
<tr>
<th scope="row">1</th>
<td>
<button type="button" class="btn btn-outline-primary" data-toggle="modal" data-target="#view_{{ $igrac->id }}">{{ $igrac->ime . " " . $igrac->prezime }}</button>
<!-- Modal -->
<div class="modal fade" id="view_{{ $igrac->id }}" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Natjecatelj: {{ $igrac->ime . " " . $igrac->prezime }}</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<div class="row">
<div class="col-6">
<img src="/img/avatar.png" alt="..." class="img-thumbnail">
</div>
<div class="col-6">
<b>Prebivalište: </b>
<p> {{ $igrac->prebivaliste }}</p>
<b>Igra: </b>
<p> {{ $igrac->igra }}</p>
<b>Član kluba od:</b>
<p> {{ $igrac->clanstvo }}. godine</p>
</div>
</div>
<hr>
<div class="row">
<div class="col-12 col-md-5 card card-body m-2">
<div class="row">
<div class="col-8">
<b>Najbolji ranking piramida: </b>
</div>
<div class="col-4">
<span class="badge badge-success p-3">1.</span>
</div>
</div>
</div>
<div class="col-12 col-md-6 card card-body m-2">
<div class="row">
<div class="col-8">
<b>Najbolji ranking pojedinačni turniri: </b>
</div>
<div class="col-4">
<span class="badge badge-success p-3">5.</span>
</div>
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
</td>
@foreach($kola as $kolo)
@foreach($igrac->igrac_piramida as $ip)
@if($ip->pivot->turnir_piramida_id == $kolo->id)
<td>{{ $ip->pivot->bodovi }}</td>
@endif
@endforeach
@endforeach
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
<!-- /.container -->
@endsection
Upvotes: 0
Views: 209
Answers (2)
Luka Otočan
Reputation: 45
I manage to solve a problem. Here is a code:
Blade file
@extends('layouts.frontend')
@section('title', 'TK Pazin | Rang-liste Piramida')
@section('content')
<!-- Page Content -->
<div class="container">
<!-- Page Heading -->
<h1 class="my-4" style="text-align:center; color: #ba3631;">Rang-lista | Piramida {{ $sezona->godina }}</h1>
<nav aria-label="breadcrumb">
<ol class="breadcrumb">
@foreach($sezone as $sezona)
<li class="breadcrumb-item"><a href="{{ route('ranglista.piramida.kola', $sezona->id) }}">{{ $sezona->godina }}</a></li>
@endforeach
</ol>
</nav>
<div>
<div class="media mb-5">
<div class="mr-3"><a href="../datoteke/Pravila-Pojedinačni-turniri.pdf" target="_blank"><i
class="far fa-file-pdf fa-3x"></i></a></div>
<div class="media-body mt-3">
<a href="/datoteke/Pravila-Piramida.pdf" target="_blank">
<h5 class="mt-0">Pravila
natjecanja u piramidi
</h5>
</a>
</div>
</div>
</div>
<div class="table-responsive">
<table class="table table-hover">
<thead class="thead-dark">
<tr>
<th scope="col">#</th>
<th scope="col">Ime i prezime</th>
@foreach($kola as $kolo)
<th scope="col">{{ $kolo->naziv }}</th>
@endforeach
<th scope="col">Ukupno</th>
</tr>
</thead>
<tbody>
@foreach($igraci as $igrac)
<tr>
<th scope="row">1</th>
<td>
<button type="button" class="btn btn-outline-primary" data-toggle="modal" data-target="#view_{{ $igrac->id }}">{{ $igrac->ime . " " . $igrac->prezime }}</button>
<!-- Modal -->
<div class="modal fade" id="view_{{ $igrac->id }}" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Natjecatelj: {{ $igrac->ime . " " . $igrac->prezime }}</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<div class="row">
<div class="col-6">
<img src="/img/avatar.png" alt="..." class="img-thumbnail">
</div>
<div class="col-6">
<b>Prebivalište: </b>
<p> {{ $igrac->prebivaliste }}</p>
<b>Igra: </b>
<p> {{ $igrac->igra }}</p>
<b>Član kluba od:</b>
<p> {{ $igrac->clanstvo }}. godine</p>
</div>
</div>
<hr>
<div class="row">
<div class="col-12 col-md-5 card card-body m-2">
<div class="row">
<div class="col-8">
<b>Najbolji ranking piramida: </b>
</div>
<div class="col-4">
<span class="badge badge-success p-3">1.</span>
</div>
</div>
</div>
<div class="col-12 col-md-6 card card-body m-2">
<div class="row">
<div class="col-8">
<b>Najbolji ranking pojedinačni turniri: </b>
</div>
<div class="col-4">
<span class="badge badge-success p-3">5.</span>
</div>
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
</td>
@foreach($kola as $kolo)
@foreach($igrac->igrac_piramida as $ip)
@if($ip->pivot->turnir_piramida_id == $kolo->id)
<td>{{ $ip->pivot->bodovi }}</td>
@endif
@endforeach
@endforeach
<td>{{ $igrac->igrac_piramida()->whereIn('turnir_piramida_id', $kola_id)->sum('bodovi') }}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
<!-- /.container -->
@endsection
Controller file
<?php
namespace App\Http\Controllers\Frontend;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Sezona;
use App\Igrac;
use App\TurnirPiramida;
use DB;
use App\NastupPiramida;
class RangListaPiramidaController extends Controller
{
public function show($id)
{
$sezone = Sezona::orderBy('godina', 'desc')->get();
$sezona = Sezona::findOrFail($id);
//Sva kola koja su u toj sezoni, u array spremi id od tih kola
$kola_id = TurnirPiramida::where('sezona_id', $id)->pluck('id');
//Svi id-evi igraca koji su u tom kolu spremi u array
$odabrani = NastupPiramida::where('turnir_piramida_id', $kola_id)->pluck('igrac_id');
$igraci = Igrac::whereIn('id', $odabrani)
->leftjoin('nastup_piramidas', 'igracs.id','=','nastup_piramidas.igrac_id')
->whereIn('nastup_piramidas.turnir_piramida_id', $kola_id)
->selectRaw('igracs.*, SUM(nastup_piramidas.bodovi) AS ukupno')
->groupBy('igracs.id')
->orderBy('ukupno', 'desc')
->get();
$kola = TurnirPiramida::where('sezona_id', $id)->get();
return view("frontend.ranglista_piramida_kola", compact('sezone', 'sezona', 'igraci', 'kola', 'kola_id'))->render();
}
}
Upvotes: 0
LobsterBaz
Reputation: 2003
If I understand properly, you want to fetch players that have been in a tournament.
You are currently fetching all player:
$igraci = Igrac::get();
You should instead fetch only the players found in the tournament_player pivot table.
Get distinct player ids from the pivot table:
$playerIds = DB::table('nastup_turnir')->groupBy('igrac_id')->pluck('igrac_id');
Fetch players from their ids:
Igrac::whereIn('id', $playerIds)->get();
Note: Your pivot table doesn't follow Laravel naming convention. If you pivot table contains igrac_id and turnir_id columns, it should be named igrac_turnir.
Upvotes: 1
Related Questions
- Laravel Eloquent, filtering from pivot table column
- Filter pivot table in Laravel?
- How to filter a pivot table using Eloquent?
- How can i filter pivot table with One to Many relation inside - Laravel 7
- Filtering query with pivot table
- Eloquent filtering pivot table
- How to filter collection by data in pivot table
- laravel 5.1 filter using pivot table
- Filter on laravel pivot table
- Filtering pivot table data with Laravel models