Consultation with Eloquent do Laravel

Question

I have this consultation:

select 
Q.id,Q.questao, 
D.disciplina, 
S.serie, 
S.ensino 
from questoes as Q 
left join capitulos_questoes as CQ 
on CQ.questoes_id = Q.id 
left join modulos_questoes as MQ 
on MQ.questoes_id = Q.id 
left join banco_disciplinas as D 
on (D.id = CQ.banco_disciplinas_id) or (D.id = MQ.banco_disciplinas_id) 
left join banco_series as S 
on (S.id = CQ.banco_series_id) or (S.id = MQ.banco_series_id)

I need to paginate the results of this consultation in Laravel 5.2. I am having difficulty in making the consultation with Eloquent because I am using the or in the LEFT JOIN.

I tried to do so:

$questoes=\DB::select('select Q.id,Q.questao, D.disciplina, S.serie, S.ensino
            from questoes as Q 
            left join capitulos_questoes as CQ
            on CQ.questoes_id = Q.id
            left join modulos_questoes as MQ 
            on MQ.questoes_id = Q.id
            LEFT join banco_disciplinas as D 
            on (D.id = CQ.banco_disciplinas_id) or (D.id = MQ.banco_disciplinas_id)
            left join banco_series as S 
            on (S.id = CQ.banco_series_id) or (S.id = MQ.banco_series_id)')
            ->paginate(15);

but returns an error saying that it is not possible to paginate an array. How can I resolve?

Answer 1

If I’m not mistaken, when using the DB You have to make the Pagination manually.

But if you want to use the Eloquent you can do so:

$questoes = Questao::leftJoin('capitulos_questoes', 'capitulos_questoes.questoes_id', '=', 'questoes.id')
->leftJoin('modulos_questoes', 'modulos_questoes.questoes_id', '=', 'questoes.id')
->leftJoin('banco_disciplinas', function($join){
      $join->on('banco_disciplinas.id', '=', 'capitulos_questoes.banco_disciplinas_id')
      ->orOn('banco_disciplinas.id', '=', 'modulos_questoes.banco_disciplinas_id');
})
->leftJoin('banco_series', function($join){
      $join->on('banco_series.id', '=', 'capitulos_questoes.banco_series_id')
      ->orOn('banco_series.id', '=', 'modulos_questoes.banco_series_id');
})
->paginate(15);

Where it is written Questao:: is a Model that needs to be created.

If you are using Composer just enter the Project Folder by Prompt and type:

php artisan make:model Questao

But since the table name is in English and plural you will have to rename it in the Model class created.

Then Model will be like this:

class Questao extends Model{

   protected $table = "questoes";

}

You must create this variable above in the class to identify the correct table.

Answer 2

Amanda you can make the query as follows:

$questoes = Questao::with(['capitulosQuestoes',
                           'bancoDisciplinas','
                           'bancoSeries'])
            ->paginate(15);

in the html you display {{$questoes->links()}}

To do this you need to create in the Questao model the methods that will be responsible for the relationships from 1 to n, n to n and etc.

Ex. if the relation of questions and chapters is 1 to n the method would be as follows:

public function capitulosQuestoes(){
    return $this->hasMany(CapituloQuestao::class,'questao_id');
}

CapituloQuestao::class is the modulus of the chapter table and the field 'questao_id' would be the foreign key making relationship with table issues.

if you want to know more about relationship between models you can check here.