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 /

building a DISTINCT DataList (without using SELECT DISTINCT)


Reply


4 Posts   1195 Views

Avatar
ScottT

Community Member, 9 Posts

23 January 2013 at 3:00am

Hi folks,
I'm looking for a new SS 3.x solution to an old problem. I have a list of news items stored in a DB which cover several years. I want to create a link for each year represented by the news items (e.g., 2006, 2007... 2013). If I get all the news items, I'll have multiple items per year, when all I really want is a DISTINCT year.

In SQL terms, I would like to SELECT DISTINCT YEAR(Date) FROM NewsItems ... and turn the result into a data list.

In my template, I'd would like to do

<% loop Years %>
<a href="$Date.Year">
<% end_loop %>

But if I just build a simple DataList, I'll get a link for each article, not each year in which there is an article.

Is there a way to get the whole table of NewsItems into a DataList and then efficiently perform an operation on the DL to just get the distinct years? I can imagine doing it with a loop, but it seems like a kludge.

SQLQuery is another option but it's throwing up all sort of deprecation notices...

Please help. Thanks.

Scott

P.S. I could also just build the HTML by hand and stick into a LiteralField or return it as a string but that's not using the template system.

Avatar
Izeroy

Community Member, 3 Posts

23 January 2013 at 4:58am

Hello,
ok the problem in your example is the MySQL Year function.

A distinct can you make with

...
return DataObject::get('NewsItem')->column('Date');
...

Maybe it will help you to find a solution for your problem.

Greetz
Izeroy

Avatar
frankmullenger

Forum Moderator, 53 Posts

23 January 2013 at 9:55am

You could perhaps use a [url=http://doc.silverstripe.org/framework/en/howto/grouping-dataobjectsets]GroupedList[/url], using a method on your model to return the year from the Date field for that object.

Avatar
ScottT

Community Member, 9 Posts

24 January 2013 at 2:00am

Those are both good ideas. Thanks for your help.

Scott