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.

Data Model Questions /

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

Complex query for getting latest news articles by unique category and sorted by date


Go to End
Reply


1364 Views

Avatar
LesC

Community Member, 70 Posts

2 May 2010 at 6:00am

Hi folks,

I'm trying to display 3 latest news articles on the homepage of my site, but need to filter it as follows:

It must be a unique category (so I don't have more than one from the same category showing),
It must sort by date, so that the latest ones show first.

I've tried the following:

public function getCombinedLatestNews() {
		$sqlQuery = new SQLQuery();
		$sqlQuery->select = array(
			"`NewsArticle`.*"
		);
		$sqlQuery->from = array(
		  "NewsArticle"
		);
		$sqlQuery->where = array(
		  "NewsCategoryPageID IN (178,179,180,181,182)"
		);
		$sqlQuery->groupby = array ("NewsCategoryPageID");
		$sqlQuery->orderby = "Date DESC"; 
		$sqlQuery->limit = 3;

		$result = $sqlQuery->execute();

		// let Silverstripe work the magic
		$records = singleton('NewsArticle')->buildDataObjectSet($result);

		return $records ? $records : false;
	}

But it doesn't quite work, and seems to return slightly random results.

Has anyone got any suggestions on how I might get this working?