query eloquent pivot table

Question

query eloquent pivot table

Navigation

#1 by (0 votes)

0

I have the following tables

empresa     | estado_empresa      | estado
 -id           -id_empresa            -id
 -nombre       -id_estado             -estado
               -Fecha_creado
               -Fecha_actualizado

In my system, companies go through the different states, when a company changes its state, a record is added in the intermediate table, without deleting the history of states through which that company has passed, since I use it in another module where I show the history of states through which that company has passed.

Now, to obtain the current status of 1 single company, I made the following query.

public static function estado_actual($id){
    $empresa= Empresa::findOrFail($id);
    $estado_actual = $empresa->estados()->orderBy('fecha_creado', 'DESC')->take(1)->get(); //obtiene el estado actual (ultimo registro segun fecha
    return $estado_actual;
}

This works perfect for me, however I need to ask you the following:

Taking into account the previous query, where I get the current status of 1 company in specific ...

How can I get the current status of all companies at the same   time?

Ex: I need to get the list of all the companies where their status   current be PROSPECTO

my current query

-- CONSULTA ESTADO PROSPECTO --

$prospectos = Empresa::with('estados')->selectRaw('distinct empresas.*')->whereHas('estados', function ($query) {
        $query->where('estado_empresa.estado_id', '=', 1)->orderBy('fecha_creado', 'desc');
    })->get();

but it is not specific because if I have a company with two states PROSPECTOS duplicates me the information.

since I appreciate your time.

laravel eloquent

asked by Rafa 21.11.2018 в 16:01

source

1 answer

how to change a boolean by clicking on an image with django? How to extract data from the database, store it in an associative array and pass it to an implode in PHP and Mysql correctly?

score 0 · Answer 1

The DISTINCT clause is what you need ... To your query, in the end, before the get ... Your current query would remain:

-- CONSULTA ESTADO PROSPECTO --

$prospectos = Empresa::with('estados')->selectRaw('distinct empresas.*')->whereHas('estados', function ($query) {
        $query->where('estado_empresa.estado_id', '=', 1)->orderBy('fecha_creado', 'desc');
    })->distinct()->get();

Greetings