Ordered results set in relationships using Laravel
I have a table structure that has this structure:
User (has many) Subjects (has many) Modules (has many) Topics (has many) Notes
I have foreign keys on each table:
Subject (user_id), Modules(subject_id), Topics(module_id), Notes(topic_id)
I want to be able to access all user generated notes, sorted by creation date. Is this possible with a query builder?
Note.php
class Note extends Model implements SluggableInterface
{
use SluggableTrait;
protected $sluggable = [
'build_from' => ['question', 'id'],
'save_to' => 'slug'
];
protected $fillable = ['question', 'answer', 'topic_id'];
public function topic()
{
return $this->belongsTo('App\Topic');
}
}
Topic.php
class Topic extends Model implements SluggableInterface
{
use SluggableTrait;
protected $sluggable = [
'build_from' => ['title', 'id'],
'save_to' => 'slug'
];
protected $fillable = ['title', 'module_id'];
public function notes()
{
return $this->hasMany('App\Note');
}
public function module()
{
return $this->belongsTo('App\Module');
}
}
source to share
There are several ways to do this. If you want to use your relationship, you will need to nest several loops to work your way through the entire relationship tree.
$user = User::with('subjects.modules.topics.notes')->find($user_id);
$notes = new Illuminate\Database\Eloquent\Collection;
foreach($user->subjects as $subject) {
foreach($subject->modules as $module) {
foreach($module->topics as $topic) {
foreach($topic->notes as $note) {
$notes->add($note);
}
}
}
}
$notes = $notes->sortBy('created_at');
As far as simplifying database creation is concerned, this is a tricky question without knowing what the rest of your application is doing. If this is the only place where you have notes, I would say that this is the perfect solution. If there are notes in other places too, this could be a problem.
Another approach would be to use a query builder, which would not require you to iterate over any result sets, but would require you to write sql. I wouldn't say it easier or simpler though.
$notes = \DB::table('users')
->select('notes.*')
->join('subjects', 'users.id', '=', 'subjects.user_id')
->join('modules', 'subjects.id', '=', 'modules.subject_id')
->join('topics', 'modules.id', '=', 'topics.module_id')
->join('notes', 'topics.id', '=', 'notes.topic_id')
->where('users.id', $user_id)
->get();
source to share