Level 5
The automated response by @laryai returned the exact same JSON response. I am still in need of assistance on this.
Sep 8, 2023
3
Level 5
Query Returns Empty Relationship When Data Exists
Hi All,
I'm running into an issue where some of the next_instance relationships return empty despite the data being present in the database. When I write the raw SQL in DataGrip, I am able to find what I'm looking for, but when querying via Eloquent, I'm running into issues.
The first record returned (9407707) is behaving as desired, but the rest of the records are not. If next_instance does not have a value in the database, then the whole record should not be returned by the query.
The Eloquent Query and Result are below. I'd appreciate if anyone would point out what I'm doing wrong.
Note: $tag is passed in via the route.
Controller Method:
Get Events by Tag
class EventsController extends Controller
{
public function tagged($tag)
{
return EventModel::has('nextInstance', '>=', 1)
->whereRelation('tags', 'id', $tag)
->whereRelation('nextInstance', 'starts_at', '>=',
Carbon::now()->shiftTimezone(config('planningcenter.timezone'))
->startOfDay())
->with(['nextInstance', 'tags'])
->get();
}
}
JSON Response
[
{
"id": 9407707,
"external_id": "9407707",
"type": "calendar",
"name": "Young Adults Group",
"description": null,
"image_url": null,
"is_public": true,
"created_at": "2023-05-31T15:20:50.000000Z",
"updated_at": "2023-08-30T16:04:22.000000Z",
"next_instance": [
{
"id": 128448325,
"external_id": 128448325,
"event_id": 9407707,
"type": "calendar",
"starts_at": "2023-10-04T00:00:00.000000Z",
"ends_at": "2023-10-04T02:00:00.000000Z",
"created_at": "2023-05-31T15:20:50.000000Z",
"updated_at": "2023-08-30T16:03:47.000000Z"
}
],
"tags": [
{
"id": 115375,
"external_id": 115375,
"tag_group_id": 14707,
"type": "calendar",
"name": "Small Groups",
"is_public": true,
"created_at": "2020-09-08T19:01:28.000000Z",
"updated_at": "2023-09-05T14:06:40.000000Z",
"deleted_at": null,
"pivot": {
"event_id": 9407707,
"tag_id": 115375
}
}
]
},
{
"id": 9407742,
"external_id": "9407742",
"type": "calendar",
"name": "Young Adults Group",
"description": null,
"image_url": null,
"is_public": true,
"created_at": "2023-05-31T15:22:56.000000Z",
"updated_at": "2023-08-30T15:27:39.000000Z",
"next_instance": [],
"tags": [
{
"id": 115375,
"external_id": 115375,
"tag_group_id": 14707,
"type": "calendar",
"name": "Small Groups",
"is_public": true,
"created_at": "2020-09-08T19:01:28.000000Z",
"updated_at": "2023-09-05T14:06:40.000000Z",
"deleted_at": null,
"pivot": {
"event_id": 9407742,
"tag_id": 115375
}
}
]
},
{
"id": 10013000,
"external_id": "10013000",
"type": "calendar",
"name": "High School Youth Group",
"description": null,
"image_url": null,
"is_public": true,
"created_at": "2023-08-14T14:19:52.000000Z",
"updated_at": "2023-08-14T14:24:59.000000Z",
"next_instance": [],
"tags": [
{
"id": 115375,
"external_id": 115375,
"tag_group_id": 14707,
"type": "calendar",
"name": "Small Groups",
"is_public": true,
"created_at": "2020-09-08T19:01:28.000000Z",
"updated_at": "2023-09-05T14:06:40.000000Z",
"deleted_at": null,
"pivot": {
"event_id": 10013000,
"tag_id": 115375
}
}
]
},
{
"id": 10013002,
"external_id": "10013002",
"type": "calendar",
"name": "High School Youth Group",
"description": null,
"image_url": null,
"is_public": true,
"created_at": "2023-08-14T14:20:03.000000Z",
"updated_at": "2023-08-14T14:25:07.000000Z",
"next_instance": [],
"tags": [
{
"id": 115375,
"external_id": 115375,
"tag_group_id": 14707,
"type": "calendar",
"name": "Small Groups",
"is_public": true,
"created_at": "2020-09-08T19:01:28.000000Z",
"updated_at": "2023-09-05T14:06:40.000000Z",
"deleted_at": null,
"pivot": {
"event_id": 10013002,
"tag_id": 115375
}
}
]
}
]
Level 5
For those interested, this is the SQL I'm looking to replicate in Eloquent:
select
tags.id
,tags.name
,events.id
,events.name
,event_instances.starts_at
from tags
join event_tag on tags.id = event_tag.tag_id
join events on events.id = event_tag.event_id
join event_instances on events.id = event_instances.event_id
where
tags.id = 115375
and event_instances.starts_at >= now()
order by event_instances.starts_at asc
Level 75
@onairmarc if you have a good working query, I suggest use as is via the db facade, or getPdo().
See https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder
Please or to participate in this conversation.