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');
}
}
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();
I'm not 100% sure because of the naming conventions:
$user = User::with(['subjects.modules.topics.notes' => function($query)
{
$query->orderBy('created_at')
}])->whereId($user_id)->first();
dd($user);
Give me feedback in the comments for what you get.