Symphony.

This may be a basic concept but as a noob to symphony i'm really struggling. I'm working on creating a year archive, but I can't figure out what is the most efficient way to just return a list of years that there is a post. I did some reading around here in the forums but didn't find anything that fit my situation so I figured I'd ask for some advice.

Basically I have a datasource for floor plans. They are categorized by number of bedrooms (3 bed, 4 bed, etc), and Floor plan of the month. For my archive page I'm filtering the result based on the category, but for the the "Floor plan of the month" category there needs to be a yearly filter as well. Filtering down a result on both cat and year is all working properly.

Enough back story now where I'm a bit lost..... I'm thinking to return a list of years for the archive list navigation, I need to create a separate datasource that filters down to only "Floor plans of the month". Right now while there is only 12 items per year for 2 years just using a group by year and paginating by say 100 entries works fine for returning a group by two years... But this already seems extremely inefficient to return all those entries for no reason. Plus eventually it will out grow my pagination and constantly bumping it up to get the full list of years will really slow down performance.

Is there anything I can do to just return a list of the dates so I would just get: 2012,2011 etc.

In my head I want to make the sql

"SELECT date FROM floor-plans WHERE category='floor-plan-of-the-month' GROUP BY YEAR(date) "

Any advice would be greatly appreciated. Thanks for taking the time to read all this.

When I had to create some archive stuff(article months in my case) I opted to use a custom SQL query in a datasource - then output that as XML from the grab function.

From my understanding the alternative using standard data-sources you would have to group the outputs by date & output that from the result but is seems highly inefficient.

If you group by the date in a datasource, and then output no elements, you will get a decent list grouped by year, then sub-grouped by month, with empty entry nodes, which you could use to count the entries per month if you wanted to.

You can also filter these groups by filtering the field you grouped by, so could only show one year, and therefore all of it's months that have entries.

So, using this idea, you can output that grouped, filtered datasource with minimal overhead, then out a datasource that has full content for entries, but filter that by either the same params, or something you've output from the grouped datasource.

@gunglien do you have any links that show how to do this. I couldn't find in the docs where it let you put a custom SQL query in for the data source.

@designermonkey, this is what I currently have setup, since I know that no matter what there will only be one post a month I don't need any extra details and really just want to return a list on years. I do have another area of the site that is going to need a more true archive, and i'll be using what you suggested.

Thank you both for the suggestions.

hmm don't have any links on hand - but I'll give you a copy of the code that you have to edit. - mainly the grab function of the datasource

public function grab(&$param_pool=NULL){
        $result = new XMLElement($this->dsParamROOTELEMENT);
        $handleTable = 'sym_entries_data_38';
        $dateTable = 'sym_entries_data_3';

        $months = Symphony::Database()->fetch("SELECT COUNT(t1.id) AS count ,YEAR(STR_TO_DATE(t1.value,'%Y-%m-%dT%H:%i:%s+%x')) AS year, MONTH(STR_TO_DATE(t1.value,'%Y-%m-%dT%H:%i:%s+%x')) AS month FROM {$dateTable} AS t1 JOIN {$handleTable} AS t2 USING (entry_id) WHERE t2.`value-{$lang}` IS NOT NULL GROUP BY year DESC, month DESC");

        foreach ($months as $month){
            $node = new XMLElement('entry',  $this->twoDigit($month['year']).'/'. $this->twoDigit($month['month']));
            $node->setAttribute('count', $month['count']);
            $node->setAttribute('month', $month['month']);
            $node->setAttribute('year', $month['year']);
            $result->appendChild($node);
        }

        return $result;
    }

Note that I have {$lang} as I had a multilingual environment and this was set to a variable. You can replace that where clause to match whatever you have. Note it is important to set allowEditorToParse to return False.

Thanks a lot, I really appreciate this.

Sure no problem - note I have something called $handleTable I was using this as a 'published' field sort-of so you wouldn't really need it. I believe there are other ways to obtain the table-name from symphony but I went for the easiest way for me. If you need any help let me know.

I got it all working, your code made perfect sense and I dropped out what I didn't need and modified the SQL to fit my needs.

I really need to spend some time actually reading through the API. I knew there had to be a way to call a custom query somewhere but with my noob experience in symphony I wasn't sure where I needed to go or if there was an extension that allowed you to do it. After searching the forums for forever I finally gave in and asked.

Again I have to thank you, this is the first site I'm building on symphony and just learning as I run into problems. This symphony community is def one of the best i've come across!

Was pretty much the same when I started off - difference was I took on an existing symphony install - which wasn't quite perfect so had to learn my way around fixing it and making it more efficient.

Glad you figured it out. The APIs are an important friend if you want to do some customisations.