Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

t0berius's avatar

retrieve nested attributes using eloquent (eager loading needed?)

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.

The fault is caused because there's no "relation" between the ticket and the files model directly or am I wrong?

0 likes
6 replies
mmonkey's avatar

I am not sure if this will work, but you could try: Rename "ticket_replie" to "ticket_reply". Don't change the table name, keep that "ticket_replies".

mmonkey's avatar

Actually the more I look at it, I don't see any belongsTo() relationships. You need to have those set on your models. Also does it make sense to have ticket_reply_id on your files table, or to have a file_id on your ticket_replies table?

t0berius's avatar

Did I need to use belongsTo()?

Each ticket contains ticket_replies. Ticket_replies contains the "reply" text for example. Ticket only contains a title of the title. The relation is ticket->ticket_replies->file. There is already a relation, take a look into ticket_replies model. Still no idea how to get this solved? Is there a need to use eager loading for this? If yes I tried this here, same error as above:

$ticket = Auth::user()->tickets()->with(['ticket_replie.file'])->where('tickets.id', $id)->where('files.ticket_replie_id', $attachment_id)->firstOrFail();

I tried this too:

$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();

result:

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)
t0berius's avatar

Should I write raw SQL and don't use Eloquent to get this done? It's tricky :/.

Please or to participate in this conversation.