I've currently no idea how to get this done in a smart way. I would like to prevent writing tons of querys. First my table design:
users:
|id|username|
tickets:
|id|user_id|
ticket_replies:
id|ticket_id|user_id|
files:
|id|ticket_replie_id|name
my controllers:
user:
public function tickets()
{
return $this->hasMany('App\ticket');
}
ticket:
public function ticket_replie()
{
return $this->hasMany('App\ticket_replie', 'ticket_id', 'id');
}
ticket_replie:
public function file()
{
return $this->hasOne('App\File', 'ticket_replie_id', 'id');
}
Each ticket_replie can be related to only one attachment (only one attachmentper ticket_replie), that's why I use hasOne relation. Now I need to retrieve the name of a file for a given ticket & ticket_replie_id. In my controller I use this at the moment:
$ticket = Auth::user()->tickets()->where('tickets.id', $id)->where('files.ticket_replie_id', $attachment_id)->firstOrFail();
Laravel generates me this query & error:
select * from `tickets` where `tickets`.`user_id` = 1 and `tickets`.`user_id` is not null and `tickets`.`id` = 43 and `files`.`ticket_replie_id` = 39 limit 1
Column not found: 1054 Unknown column 'files.ticket_replie_id' in 'where clause
The query must be something like:
select * from `tickets`, `files` where `tickets`.`user_id` = 1 and `tickets`.`user_id` is not null and `tickets`.`id` = 43 and `files`.`ticket_replie_id` = 39 limit 1
When I run this query in my database, it returns the needed informations. Is my way to retrieve the information okay? Where's my fault, because at the moment the query generated by Eloquent isn't working as described above. In case there's a easier way, just tell me.
I know eagerload, I tried this:
$ticket = Auth::user()->tickets()->with(['file'=>function($f) use ($attachment_id) { $f->where('files.ticket_replie_id', $attachment_id); } ])->where('tickets.id', $id)->where('files.ticket_replie_id', $attachment_id)->firstOrFail();`.
It results in:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'files.ticket_replie_id' in 'where clause' (SQL: select * from `tickets` where `tickets`.`user_id` = 1 and `tickets`.`user_id` is not null and `tickets`.`id` = 43 and `files`.`ticket_replie_id` = 39 limit 1)
The fault is caused because there's no "relation" between the ticket and the files model directly or am I wrong?
you have to eagerload your relationships, then you can apply the where condition. look, here is the example.
$courses = Courses::with(['quizzes','chapters'=>function($q){
$q->where('status','1')->orderBy('orderby','ASC');
},'chapters.quizzes','chapters.lessons'=>function($q) use ($lessonid){
$q->where('status','1')->orderBy('orderby','ASC');
},'chapters.lessons.quizzes'])->where('status','1')->where('id',$courseid)->first();
with([]) are using to load model relationships, eg: quizzes is relation with quiz table, chapters=>function($q).... its also a relation but I am applying condition on chapters relation etc
I Hope that will make sense for you