How can i convert this sql query in a eloquent laravel command?

Question

in sql query this commando do exactly i wanted:

SELECT 
    v.id,
    ( 
        SELECT sv.status_id 
        FROM status_viagem sv 
        WHERE sv.viagem_id = v.id 
        ORDER BY sv.created_at DESC LIMIT 1 ) AS status_id
FROM viagens v

Here is the sql results:

But i have no idea how can i do this using Laravel eloquent

Basically, a viagem entry can has a lot of status, but i need to get each viagem and their last status entry from status_viagem table (the pivot table)

by the way viagem/viagens means travel.

My class mapping:

class Viagem extends Model
{
  ...
  public function status()
  {
        return $this->belongsToMany('App\Status')->withTimestamps();
  }
  ...
}


class Status extends Model
{

    public function viagens()
    {
        return $this->belongsToMany('App\Viagem')->withTimestamps();
    }
}

The belongsToMany at both classes gets me a many-to-many:

can someone help me? thanks

---------- Temporary Solution -------

Thanks for all help guys. In fact i can't find a nice solution using only eloquent.

Step 1/3 - To bypass this situation i first execute the above sql to grab only the viagens under the desired status_id (last status_viagem entry):

$viagens_ids = DB::select(
            "SELECT viagem_id FROM (
                SELECT 
                    v.id AS viagem_id,
                    ( 
                        SELECT sv.status_id 
                        FROM status_viagem sv 
                        WHERE sv.viagem_id = v.id 
                        ORDER BY sv.id DESC LIMIT 1 ) AS status_id
                FROM viagens v
            ) AS tt

            WHERE tt.status_id = {$status->id}"
        );

Step 2/3 - then i used the array_map to organize my viagens ids

$a = array_map(
                function($obj) { return $obj->viagem_id; }, 
                $viagens_ids
            );

Step 3/3 - And at last i used elequent whereIn to fetch my viagens:

 Viagem::with( 'status')->whereIn('id', $a)->get();

In fact i have solved the problem by a-old-sashion-way but i not happy with it because i wish i learn how to do it using eloquent. what bad to me.

Answer 1

There are many ways to query in laravel. I have created a test project for you to try. The gist are:

1. Eloquent ORM

Eloquent ORM is Laravel's magic which have some limitations in eager loading - which i just come across while contemplating your question for hours. It wont play nicely with first(), last(), and some more functions in the constrained eager loading closure.

In your case, our almost there can be fixed:

   App\Models\Viagem::with(['status' => function($query){
          return $query->orderBy('pivot_created_at', 'desc');
         }])
        ->get()

It will return entire field for Viagem and Status including its pivot table (the status_viagem).

However, if you wanted to retrieve only viagem.id and status_viagem.status_id, you can map() it as such:

   App\Models\Viagem::with(['status' => function($query){
           return $query->orderBy('pivot_created_at', 'desc');
         }])
        ->get()
        ->map(function($data){
           $o = new stdClass();
           $o->id = $data->id;
           $o->status_id = $data->status->first()->id;
           return $o;
        });

Please take note that the statement above require sql query to be ran twice. Eager loading basically works by querying all the Viagem first then queries the Status and map them in memory based on the foreign keys. You can observe that replacing get with toSql will only give you the first query. Please enable Query Logging to see the second query.

2. Query Builder

Embarking from Ryan Adhitama Putra answer, you could do something like:

   App\Models\Viagem::join('status_viagem', 'viagens.id', '=', 'status_viagem.viagem_id')
       ->orderBy('status_viagem.created_at', 'desc')
       ->groupBy(['status_viagem.status_id', 'viagens.id'])
       ->select(['viagens.id', 'status_viagem.status_id'])
       ->get();

This query builder approach guaranteed to be ran only once, you can replace the get() with toSql() to see the resulting query.

3. Raw Queries

Throwing DB::raw() can help sometime, but i really did not want to mention it.