Reputation: 1621

Laravel eloquent with multiple inner joins

I currently am doing a raw sql query however this is causing issues with relationships and model boot methods.

Is it possible to do the following SQL query but with laravel eloquent models by relationship? Note all db tables have FK's defined, and relationships either HasOne or HasMany relationships.

    $timeBreakDown = DB::select(
        "SELECT
                Entries.`task_id`,
                Entries.`opportunity_id`,
                SUM(Entries.`total_duration`) as 'duration',
                Class.`class` as 'class',
                Subclass.`sub_class` as 'subclass'
            from entries Entries
                INNER JOIN `tasks` Task
                    ON task_id = Task.id
                INNER JOIN `task_class` Class
                    ON Task.`class_id` = Class.`id`
                INNER JOIN `task_subclasses` Subclass
                    ON Task.`subclass_id` = Subclass.`id`
                WHERE Entries.`opportunity_id` = '".$opportunity->id."'
                GROUP BY Entries.`task_id`"
    );

Models are

Entries
Tasks
Class
Subclass

How would I have to structure my models relationships to handle the above sql query?

Upvotes: 10

Answers (8)

Calvin

Reputation: 635

Models\Entries.php

<?php

namespace App\Models;


use Illuminate\Database\Eloquent\Model;

class Entries extends Model
{
    public function Tasks(){
        return $this->hasOne(Tasks::class);
    }
    public function Class(){
        return $this->hasMany(Classes::class);
    }
    public function SubClasses(){
        return $this->hasOne(SubClasses::class);
    }
}

Models\Tasks.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Tasks extends Model
{
    public function Entries(){
        return $this->belongsTo(Entries::class, "id", "task_id");
    }
}

Models\Classes.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Classes extends Model
{
    public function Entries(){
        return $this->belongsTo(Entries::class, "class_id", "id");
    }
}

Models\Subclasses.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class SubClasses extends Model
{
    public function Entries(){
        
        return $this->belongsTo(Entries::class, "id", "subclass_id");
        
    }
}

Query:

Entries::with([
        "Tasks",
        "Classes",
        "SubClasses"
    ])
    ->where("opportunity_id", $opportunity->id)
    ->groupBy("task_id")
    ->get();

Upvotes: 2

Faizan Ali

Reputation: 330

You can write a query in this way:
Please check table names according to your database

DB:: table('table name')->join('tasks','task_id','=','tasks.id')
->join('task_class', 'tasks.subclass_id','=','task_class.id')
->join('task_subclasses','tasks.subclass_id','=', 'task_subclasses.id')
->selectRaw('entries.task_id,
            task_subclasses.opportunity_id,
            SUM(entries.total_duration) as duration,
            task_class.class as class,
            task_subclasses.sub_class as subclass') 
->where(['entries.opportunity_id'=>$opportunity->id])
->groupBy('enteries.task_id')->get();

Upvotes: 3

Danish

Reputation: 150

If you would have posted your models that would have been easier for us. But here is what I got from your raw query above.

$timeBreakDown = Entries::where('opportunity_id',$opportunity->id)->load('Tasks','Class.SubClass')->get();

You should read about Laravel Eloquent and relationships. Just for brief intro the difference between load and with used by Waleed is: Load is used for lazy loading of relationship data while with is used for eager loading.

Eager loading is all the data gets load as soon as the Eloquent queries the data while lazy loading loads the data when it is required.

Upvotes: 0

Mike Lucid

Reputation: 1354

you need to just setup the relationships like this:

I am assuming that that a Class will have a subClass and a Class will also have Tasks and those Tasks have Entries.

Also do you not have a User model?

Class Model

class Class extends Model
{ 

  protected $with = ['entries', 'subclass', 'task'];

  public function entries()
  {
      return $this->hasManyThrough(\App\Models\Entries::class, \App\Models\Task::class);
  }

  public function subClass()
  {
      return $this->hasOne(\App\Models\subClass::class);
  }

  public function tasks()
  {
    return $this->hasMany(\App\Models\Task::class);
  }
}

Entry Model

class Entry extends Model
{ 

  protected $with = ['task'];

  public function task()
  {
    return $this->belongsTo(Task::class);
  }
}

SubClass Model

class SubClass extends Model
{ 

  protected $with = ['class'];

  public function class()
  {
    return $this->belongsTo(\App\Models\Class::class);
  }
}

Task Model

class Task extends Model
{ 

  protected $with = ['entries', 'class'];

  public function entries()
  {
      return $this->hasMany(\App\Models\Class::class);
  }

  public function class()
  {
    return $this->hasMany(\App\Models\Task::class);
  }
}

With all of that set up you should be good to do something like this fro wherever your at in the stack:

$entry = Entry::findOrFail('id');
$entry->task->class->subClass->name;

$class = Class::findOrFail($class->id);
$subclass_name = $class->subclass->name;
$entries = $class->tasks->entries;

Upvotes: 0

BlackCat

Reputation: 19

From the official documentation. You can define relationships using the base database relationship type by adding the tasks method to the Entries model. The tasks method should call the hasOne method and return its result.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Entries extends Model
{
    /**
     * Get the phone associated with the user.
     */
    public function task()
    {
        return $this->hasOne(Tasks::class);
    }
}

In turn, the Tasks model will have an entry method with which we can determine the inverse of the hasOne relationship using the belongsTo method:

<?php
    
  namespace App\Models;
   
  use Illuminate\Database\Eloquent\Model;
    
  class Tasks extends Model
  {
      /**
       * Get the user that owns the phone.
       */
      public function entry()
      {
          return $this->belongsTo(Entries::class);
      }
  }

Upvotes: 0

FULL STACK DEV

Reputation: 15941

Try this query:

$timeBreakDown = Entries::join('tasks', 'tasks.id', '=', 'entries.task_id')
            ->join('class', 'class.id', '=', 'entries.class_id')
            ->join('subclass', 'subclass.id', '=', 'entries.subclass_id')
            ->select(
                'entries.task_id',
                'entries.opportunity_id',
                \DB::raw('SUM(entries.total_duration) as duration'),
                'class.class',
                'subclass.sub_class as subclass')
            ->where('entries.opportunity_id', $opportunity->id)
            ->groupBy('entries.task_id')
            ->get();

And try dd($timeBreakDown->toSql()); to match with your Raw SQL query.

Upvotes: 0

jssDev

Reputation: 993

Might be something like this:

$timeBreakDown = Entries::select('entries.task_id, entries.opportunity_id', DB:raw('SUM(Entries.total_duration) as duration), task_class.class, task_subclasses.sub_class as subclass)
join('tasks', [['tasks.id', 'entries.task_id']])
join('task_class', [['task_class.id', 'entries.class_id']])
join('task_subclasses', [['task_subclasses.id', 'entries.subclass_id']])
->where('entries.opportunity_id', $opportunity->id)
->groupBy('entries.task_id')
->get();

Upvotes: 0

Waleed Muaz

Reputation: 802

Yes, You can do it with Eloquent I'll share an example with you I can't read your Mess Query sorry for this but I will suggest you to do this

Entries::with(['Tasks','Class','Subclass'])->get();

from this, you will get all objects from this array

Let just say

The class have a relation with another Model but not Entries table then the Eloquent is something like this

Entries::with(['Tasks','Class.Subclass'])->get();

hope its helpful for you

Upvotes: 0

Laravel eloquent with multiple inner joins

Answers (8)

Related Questions