I have one-to-may relation: Task -> hasMany(History)
History = [{ 'task_id'=>1, 'key'=>1, 'date'=>'2024.12.18', 'data'=>'some log'},
{ 'task_id'=>1, 'key'=>2, 'date'=>'2024.12.17', 'data'=>'other log'},
{ 'task_id'=>1, 'key'=>1, 'date'=>'2024.12.16', 'data'=>'some other log'},
{ 'task_id'=>2, 'key'=>3, 'date'=>'2024.12.15', 'data'=>'log'},
{ 'task_id'=>2, 'key'=>2, 'date'=>'2024.12.18', 'data'=>'more log'}]
I want to retrieve Task with this relation in multiple version: by two keys, first element:
$task = Task::where('id',1)
->with(['history' =>
fn($q) => $q->where('key', 1)->orderBy('date','asc')->first()])
->with(['history' =>
fn($q) => $q->where('key', 2)->orderBy('date','asc')->first()])
->get();
What I wanted to achieve:
$task = [{'id'=>1, 'taskdata'=>'data', 'history'=>[
{'key'=>1, 'date'=>'2024.12.16', 'data'=>'some other log'},
{'key'=>2, 'date'=>'2024.12.17', 'data'=>'other log'}]
}]
I cannot find out how. First made with two scopes but same result: only tha last relation visible.
Edit
History key is not unique in here as visible in first code, there are more items with key=1 and key=2 and I only want the first of them (ordered by date).
For one key it is perfectly working:
$task = Task::where('id',1)
->with(['history' =>
fn($q) => $q->where('key', 1)->orderBy('date','asc')->first()])
->get();
I just need an alternative for two keys.
Let me explain a bit further:
There are Tasks, and when they are changes, it saved to a history table, where a key selects the changed column.
So when is wanted back the original Task, I queried with the history to that Task with the first value of the two desired columns.
Maybe it’s a bad versioning design, but now i have this.
1
Rather than running the checks one after the other and making your query complex, you can restructure it and get the Task with the history relationship where the id is in the array of values [1, 2].
$task = Task::where('id',1)
->with('history' =>
fn($q) => $q->whereIn('key', [1, 2])->orderBy('date','asc')
)
->get();
1
You can do it but you will have to give each eager load a unique name
$task = Task::where('id',1)
->with(['history as history1' =>
fn($q) => $q->where('key', 1)->orderBy('date','asc')->limit(1)])
->with(['history as history2' =>
fn($q) => $q->where('key', 2)->orderBy('date','asc')->limit(1)])
->get();
which will result in this structure
$task = [{
'id'=>1,
'taskdata'=>'data',
'history1'=>[{'key'=>1, 'date'=>'2024.12.16', 'data'=>'some other log'}],
'history2'=>[{'key'=>2, 'date'=>'2024.12.17', 'data'=>'other log'}]
}]
You can always merge the two afterwards