Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

Groupwise max queries done nicely?


Go to End


501 Views

Avatar
grgcnnr

Community Member, 5 Posts

21 February 2017 at 11:32am

Edited: 21/02/2017 11:33am

HI ORM gurus, I have a BlogPostEvent that has many EventObjects, each representing an actual event session date and time.

I'm looking for a way to select all BlogPostEvents that have at least one EventObject in the future.

At the moment I'm doing it like so:

		$query = DataObject::get('BlogPostEvent')
			->leftJoin('EventObject', '"EventObject"."BlogPostEventID" = "BlogPost"."ID"') 
			->where('EventStartDate = (
				SELECT MAX(EventStartDate)
				FROM "EventObject"
				WHERE "EventObject"."BlogPostEventID" = "BlogPostEvent"."ID" AND "EventObject"."EventStartDate" > NOW()
				)'
			);

Which is working fine but doesn't feel very ORMish. Is there a nicer way of doing this?

Cheers.
G