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

building a DISTINCT DataList (without using SELECT DISTINCT)


Go to End


4 Posts   5531 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 GroupedList, 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