Question:
I have this query:
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 query in Laravel 5.2. I'm having trouble making the query with Eloquent because I'm using or
in the LEFT JOIN
.
I tried to do like this:
$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 it is not possible to paginate an array. How can I solve?
Answer:
If I'm not mistaken, when using DB
you have to do the pagination manually.
But if you want to use Eloquent you can do it like this:
$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 will need to be created.
If you are using composer, just enter the project folder through the Prompt and type:
php artisan make:model Questao
But as the name of the table is in Portuguese and in the plural you will have to rename it in the created Model class.
So the Model will look like this:
class Questao extends Model{
protected $table = "questoes";
}
You will necessarily have to create this variable above in the class to identify the correct table.