Problems checking input and query optimization

Question

im creating a management system in which teachers can manage the final projects of students, and developers can see what other students have created.

im a newbie laravel and im having problems with query optimization and url checking

here are my table schemas:

Cursos

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| curso | varchar(255)     | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

Trienios

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| data_trienio | varchar(255)     | NO   |     | NULL    |                |
| curso_id     | int(11)          | NO   |     | NULL    |                |
| oe_id        | int(11)          | NO   |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

Alunos

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| id_cartao  | int(10) unsigned | NO   | UNI | NULL    |                |
| nome       | varchar(255)     | NO   |     | NULL    |                |
| email      | varchar(255)     | NO   | UNI | NULL    |                |
| trienio_id | int(11)          | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

RAP

+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| nome      | varchar(255)     | NO   |     | NULL    |                |
| descricao | text             | NO   |     | NULL    |                |
| nota      | int(11)          | NO   |     | NULL    |                |
| aluno_id  | int(11)          | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

so far I have been able to configure dynamic URLs based on entries defined in the cursos and trienios table, for example: http://localhost:8000/TGEI/2014-2017

(TGEI is an entry in the cursos table that retrieves the associated trienio and 2014-2017 entries, which is an entry in the trienios table that is associated with the one-to-many curso entry and selects the related pap entries)

, , ,

:

Curso.php

public function trienio()
{
    return $this->hasMany('App\Trienio');
}

Trienio.php

public function curso()
{
    return $this->belongsTo('App\Curso');
}

public function oe()
{
    return $this->belongsTo('App\OE');
}

public function aluno()
{
    return $this->hasMany('App\Aluno');
}

Aluno.php

public function trienio()
{
    return $this->belongsTo('App\Trienio');
}

public function pap()
{
    return $this->hasOne('App\PAP');
}

PAP.php

protected $table = 'pap';

public function aluno()
{
    return $this->belongsTo('App\Aluno');
}

, , :

CursoController.php

public function index(Curso $curso)
{
    $cursos = $curso->all();

    return view('curso')->withCursos($cursos);
}

TrienioController.php

public function index(Trienio $trienio, $curso)
{   
    $trienios = $trienio->whereHas('curso', function ($query) use ($curso) {
        $query->where('curso', '=', $curso);
    })->get();

    return view('trienio')->withTrienios($trienios);
}

PapController.php

public function index(Pap $pap, $curso, $trienio)
{
    $pap = $pap->whereHas('aluno.trienio', function ($query) use ($curso, $trienio) {
        $query->where('data_trienio', '=', $trienio)->whereHas('curso', function ($query) use ($curso) {
            $query->where('curso', '=', $curso);
        });
    })->toSql();

    dd($pap);

    return view('pap')->withPap($pap);

}

public function show(Pap $pap, $curso, $trienio, $id)
{   
    $pap = $pap->find($id);

    dd($pap);

    return view('show')->withPap($pap);
}

, PAP , , , n + 1:

"select * from `pap` where exists (select * from `alunos` where `pap`.`aluno_id` = `alunos`.`id` and exists (select * from `trienios` where `alunos`.`trienio_id` = `trienios`.`id` and `data_trienio` = ? and exists (select * from `cursos` where `trienios`.`curso_id` = `cursos`.`id` and `curso` = ?)))"

, , - PAP, trienio, , , curso, , URL ( ), , , , ,

URL-, :

http://localhost:8000/qwfkjnfwq/qjqtikjn/1

pap , 2 , , , ""

:

http://localhost:8000/TGEI/2014-2017/1

, aluno.trienio, trienio, aluno, 2014-2017, curso, trienio, TGEI

,

http://localhost:8000/qwfkjnfwq/qjqtikjn/1

, .

, , . , ( , ), , .

, web.php

Route::get('/', 'CursoController@index');
Route::get('/{curso}', 'TrienioController@index');
Route::get('/{curso}/{trienio}', 'PapController@index');
Route::get('/{curso}/{trienio}/{id}', 'PapController@show');

Answer 1

, .

Laravel 5.2 , (: public function show(Pap $pap)) Laravel Pap URL- ( Pap::find($id) $pap). , , .

. - (., )

// CursoController.php
public function index()
{
    $cursos = Curso::all();

    return view('curso')->withCursos($cursos);
}

// TrienioController.php
public function index($curso)
{   
    $trienios = Trienio::whereHas('curso', function ($query) use ($curso) {
        $query->where('curso', '=', $curso);
    })->get();

    return view('trienio')->withTrienios($trienios);
}

// Pap controller
public function index($curso, $trienio)
{
    $pap = Pap::whereHas('aluno.trienio', function ($query) use ($curso, $trienio) {
        $query->where('data_trienio', '=', $trienio)->whereHas('curso', function ($query) use ($curso) {
            $query->where('curso', '=', $curso);
        });
    })->get();

    return view('pap')->withPap($pap);

}

public function show($curso, $trienio, $id)
{   
    $pap = Pap::whereHas('aluno.trienio', function ($query) use ($curso, $trienio) {
        $query->where('data_trienio', '=', $trienio)->whereHas('curso', function ($query) use ($curso) {
            $query->where('curso', '=', $curso);
        });
    })->findOrFail($id);

    return view('show')->withPap($pap);
}

, show() index(), .

- , , . n + 1, .

n + 1, foreach . , - Pap:

@foreach($pap as $p)
<div>{{ $p->aluno->id }}</div>
@endforeach

$p $pap aluno.

n + 1, . ->with(relationship). - :

// Pap controller
public function index($curso, $trienio)
{
    $pap = Pap::whereHas('aluno.trienio', function ($query) use ($curso, $trienio) {
        $query->where('data_trienio', '=', $trienio)->whereHas('curso', function ($query) use ($curso) {
            $query->where('curso', '=', $curso);
        });
    })
    ->with('aluno.trienio') // You might need some additional checks here, depending on you needs
    ->get();

    return view('pap')->withPap($pap);
}

, ->whereHas(relationship) . , :

// Pap controller
public function index($curso, $trienio)
{
    $pap = Pap::whereHas('aluno.trienio', function ($query) use ($curso, $trienio) {
        $query->where('data_trienio', '=', $trienio)->whereHas('curso', function ($query) use ($curso) {
            $query->where('curso', '=', $curso);
        });
    })
    ->with(['aluno.trienio' => function ($q) use ($curso, $trienio) {
        $query->where('data_trienio', '=', $trienio)->whereHas('curso', function ($query) use ($curso) {
            $query->where('curso', '=', $curso);
        }]); // These are the additional checks
    ->get();

    return view('pap')->withPap($pap);
}