0
219
ViewsLaravel 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?
3 answers
Answer question0
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();
0
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();
0
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.