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 /

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


Reply


1182 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?