Jump to:

3373 Posts in 998 Topics by 712 members

Data Model Questions

SilverStripe Forums » Data Model Questions » building a DISTINCT DataList (without using SELECT DISTINCT)

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

Page: 1
Go to End
Author Topic: 754 Views
  • ScottT
    Avatar
    Community Member
    9 Posts

    building a DISTINCT DataList (without using SELECT DISTINCT) Link to this post

    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.

  • Izeroy
    Avatar
    Community Member
    3 Posts

    Re: building a DISTINCT DataList (without using SELECT DISTINCT) Link to this post

    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

  • frankmullenger
    Avatar
    Forum Moderator
    51 Posts

    Re: building a DISTINCT DataList (without using SELECT DISTINCT) Link to this post

    You could perhaps use a GroupedList, using a method on your model to return the year from the Date field for that object.

  • ScottT
    Avatar
    Community Member
    9 Posts

    Re: building a DISTINCT DataList (without using SELECT DISTINCT) Link to this post

    Those are both good ideas. Thanks for your help.

    Scott

    754 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.