Level 24
Please post the three relationships.
Sep 5, 2018
4
Level 5
Eager loading: Filtering relationships by eager-loaded values
Hi all,
I'm trying to get some results from my database that I can't seem to wrap my head around...
I have the following method:
public function getMetricGroups(Metric $metric) {
return $metric->load([
'sections',
'sections.groups',
'sections.groups.metric_data' => function($query) {
$query->where('submission_date', request('submission_date'));
}
]);
}
The problem is that "metric_data" needs to be filtered by 4 criteria:
- Metric ID
- Section ID
- Group ID
- Submission Date
The Submission Date is coming in via $_POST, but the rest of the criteria is based on relationships of the models/database.
The above code snippet seems to give me the correct metric_data records by 'metric', 'group', and obviously 'submission_date', but it's not filtering by the section/section ID.
What I want to do is add another where clause to say where('metric_data.id', 'sections.id') but that obviously doesn't work. What I don't know is whether there's a way to actually do this, or if I need to load the metric_data table in some other way or with some other relation in the model(s)?
Any pointers?
Thanks
Level 67
Not having to do with your issue, but you don't need this
'sections',
'sections.groups',
because they are already loaded by
'sections.groups.metric_data'
Yes, we'd need to see the relationship definitions.
Level 5
My Metric model has many sections (via a MetricSection model):
/**
* Sections
*/
public function sections() {
return $this->hasMany(MetricSection::class);
}
The MetricSection model belongs to many groups, via a group_metric pivot table:
/**
* Groups
*/
public function groups() {
return $this->belongsToMany(Group::class, 'face_group_metric')->withPivot('id');
}
The group_metric table contains:
- Metric ID
- Section ID
- Group ID
A Metric can have many Sections and a Section can have many Groups.
However, a Metric might also have many Groups without a Group being associated with a Section.
In other words:
Metric
Section #1
Group #1
Group #2
...
Section #2
Group #1
Group #2
...
Group #1
Group #2
Group #3
Finally, my Group model has one MetricData:
/**
* FACe Metric Data
*/
public function metric_data()
{
return $this->hasOne(\App\Models\Face\MetricData::class, 'group_id');
}
Here's a sample output:
{
"id": 3,
"functional_area_id": "6",
"name": "Test Metric",
"data_type": "decimal",
"frequency": "daily",
"created_at": "2018-06-20 15:38:04.537",
"updated_at": "2018-09-04 20:16:35.843",
"deleted_at": null,
"allow_custom_groups": null,
"sections": [
{
"id": 138,
"metric_id": "3",
"name": "Section #1",
"group_id": null,
"created_at": "2018-09-04 19:58:32.837",
"updated_at": "2018-09-04 19:58:32.837",
"deleted_at": null,
"section_group": null,
"groups": [
{
"id": 161,
"cell_name": "Haworth NA",
"cell_number": null,
"app_division_id": null,
"app_location_id": "5",
"cell_iq_connector": null,
"cell_status": "active",
"cell_order": null,
"parent_group_id": "109",
"level_id": "5",
"created_at": "2018-08-09 14:12:57.453",
"updated_at": "2018-08-30 15:05:06.677",
"deleted_at": null,
"division_id": null,
"pivot": {
"metric_section_id": "138",
"group_id": "161",
"id": 985
},
"metric_data": {
"id": 3499,
"metric_id": "3",
"group_id": "161",
"value": "1.00",
"submission_date": "2018-09-03",
"created_at": "2018-09-04 20:16:47.367",
"updated_at": "2018-09-04 20:16:47.367",
"deleted_at": null,
"metric_section_id": "138"
}
}
]
},
{
"id": 139,
"metric_id": "3",
"name": "Section #2",
"group_id": null,
"created_at": "2018-09-04 20:15:47.913",
"updated_at": "2018-09-04 20:15:47.913",
"deleted_at": null,
"section_group": null,
"groups": [
{
"id": 161,
"cell_name": "Haworth NA",
"cell_number": null,
"app_division_id": null,
"app_location_id": "5",
"cell_iq_connector": null,
"cell_status": "active",
"cell_order": null,
"parent_group_id": "109",
"level_id": "5",
"created_at": "2018-08-09 14:12:57.453",
"updated_at": "2018-08-30 15:05:06.677",
"deleted_at": null,
"division_id": null,
"pivot": {
"metric_section_id": "139",
"group_id": "161",
"id": 988
},
"metric_data": {
"id": 3499,
"metric_id": "3",
"group_id": "161",
"value": "1.00",
"submission_date": "2018-09-03",
"created_at": "2018-09-04 20:16:47.367",
"updated_at": "2018-09-04 20:16:47.367",
"deleted_at": null,
"metric_section_id": "138"
}
}
]
}
]
}
(pay no attention to the "cell_" prefixes... This is from a legacy database table structure)
As you can see "metric_data" under "Section #1" has the correct metric_section_id "138". But so does metric_data under "Section #2".
I assume this is because there needs to be an additional relationship to the Section. I'm just not seeing how to do it...
Thanks
Level 24
I don't see a native way to do this.
You can create a direct relationship between Metric and MetricData with the HasManyDeep package I created:
class Metric extends Model {
use \Staudenmeir\EloquentHasManyDeep\HasRelationships;
public function metric_data() {
return $this->hasManyDeep(MetricData::class,
[MetricSection::class, 'face_group_metric', Group::class])
->where('submission_date', request('submission_date'))
->whereColumn('metric_data.metric_id', 'metric_sections.metric_id')
->whereColumn('metric_data.metric_section_id', 'metric_sections.id');
}
}
Please or to participate in this conversation.