Query for searching with multiple tables

Asked

Viewed 319 times

0

I need to search the data of a table of processes, and of other 2 tables one of authors and defendants of the process, the doubt is how to search in grouped form that I can go through with foreach to fill a report, I am using the Laravel, list of tables and how it should look in the report.

$processos = Processo::join('autores', 'autores.processo_id', 'processos.id')
        ->join('reus', 'reus.processo_id', 'processos.id')
        ->join('pessoas', 'autores.pessoa_id', 'pessoas.id')
        ->join('pessoas', 'reus.pessoa_id', 'pessoas.id')
        ->select('pessoas.nome as nome_pessoa', 'processos.*', 'autores.*', 'reus.*')->get();

Of that mistake: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: pessoas (SQL: select pessoas.nome as nome_pessoa, processos.*, autores.*, reus.* from processos inner join autores on processo.processo_id = processos.id inner join reus_processo on reus.processo_id = processos.id inner join pessoas on autores.pessoa_id = pessoas.id inner join pessoas on reus.pessoa_id = pessoas.id)

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

laravel laravel-5 laravel-eloquent

  • Put the class code too.

  • What class? you say the controller?

  • No, your model classes: Processo, Pessoas, Reus and Autores

  • Your table has a date, but your bank model doesn’t! Maybe you need to load the information and then unite it according to a criterion, only bank result in these relationships seems to me quite complicated, at least I think...

  • Using the Laravel ORM concepts you can achieve the goal. See this example. https://github.com/GeekSilva97/laravel-orm

1 answer

0

Before implementing in Laravel you need to understand a little more about database relationships.

In the relation Defendant-Process and Plaintiff-Process you have relationship N:N, therefore it would be necessary to create an intermediate table, as processes_reus and processes_authors. As an example, the table processos_reus would have the id, processo_id (foreign) and reu_id (foreign).

Here is an example of Migration:

Schema::create('processos_reus', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('processo_id')->unsigned();
        $table->integer('reu_id')->unsigned();

        $table->foreign('processo_id')->references('id')->on('processo');
        $table->foreign('reu_id')->references('id')->on('reus');
    });

Created the tables, you will need to do the relationship on Laravel to pull the information you need into just one variable. You can see an example here.

Browser other questions tagged laravel laravel-5 laravel-eloquent

You are not signed in. Login or sign up in order to post.