Consultation with Eloquent do Laravel

5

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 page the results of this query in Laravel 5.2. I'm having trouble consulting with Eloquent because I'm using or in LEFT JOIN .

I tried to do 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 that it is not possible to paginate an array. How can I resolve?

    
asked by anonymous 10.10.2016 / 19:38

2 answers

3

If I am not mistaken, when using DB you have to do the Paging manually.

But if you want to use Eloquent you can do 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 is written Questao:: is a Model that needs to be created.

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

php artisan make:model Questao

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

Then the Model will look like this:

class Questao extends Model{

   protected $table = "questoes";

}

You will have to create this variable in the class to identify the correct table.

    
10.10.2016 / 19:51
0

Amanda you can do the query as follows:

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

In 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 of 1 for n, n for n and etc.

Ex. if the relation of questions and chapters is 1 for n the method would look like this:

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

CapituloQuestao::class is the modulo referring to the table capitulos_questoes and the field 'questao_id' would be the foreign key that makes relationship with the table questions.

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

    
10.10.2016 / 22:01