select a row for each column`s value

Question

I have got a condition and i want to use mysql temporary tables.

i have Tasks table and Jobs table.

tasks has one to many relation with jobs (each task has one or more jobs)

i want to select The Last Job of each task in a query (based on created_at column).

this query works fine:

SELECT * 
  FROM tasks t
  join jobs j
    on t.id = j.task_id 
   and j.id = ( select id 
                  from jobs 
                 where jobs.task_id = t.id 
                   and jobs.deleted_at is null 
                 order 
                    by id desc 
                 limit 1
              ) 
 order 
    by j.created_at desc;

but i want to use something more flexible like mysql temporary table:

CREATE TEMPORARY TABLE last_jobs SELECT * from jobs where ...

and make it with laravel elequent and query builder if there is any built in function or so like:

Task::select('tasks.*,jobs.*')->join('jobs')->where()

if anyone can give me a hand or a clue it would be helpful.

Thanks.

Answer 1

You can create a view in a migration, note that you should explicitly declare each column name (with an alias if it is an ambiguous name).

class CreateLastJobsView extends Migration {
   public function up() {
    DB::statement("CREATE VIEW last_jobs AS
       SELECT t.id as task_id, j.id as job_id, ...  
       FROM tasks t
       join jobs j
         on t.id = j.task_id 
       and j.id = ( select id 
                  from jobs 
                 where jobs.task_id = t.id 
                   and jobs.deleted_at is null 
                 order 
                    by id desc 
                 limit 1
              ) 
        order by j.created_at desc");
  }

  public function down()  {
    DB::statement('DROP VIEW last_jobs');
  }  

}

Then you can use the view as a normal table:

DB::table('last_jobs')->select('task_id')->first();