Page List order by Custom Attribute display order

Permalink
I am creating a page displaying competition results. Each award winner is an individual page so I am using a page list to display the winners. I have set up a custom attribute for the awards e.g Gold, Silver, Bronze but can only seem to order the awards alphabetically rather than the display order of the attribute.

Loader::model('page_list');
$pl = new PageList();
$pl->filterByAttribute('exclude_page_list',1,'!=');
$pl->filterByPath('/awards');
$pl->sortBy('award', 'asc');
$winners = $pl->get();


This displays the results as:
• Bronze
• Gold
• Silver

When I am trying to replicate the display order of the attribute:
• Gold
• Silver
• Bronze

Does anyone have any suggestions on how to maintain the attribute order when listing pages or if it is possible at all?

Many thanks.
Paul

 
A3020 replied on at Permalink Reply
A3020
Hi Paul,

Are we talking about a 'select' custom attribute? If so, have you checked the settings for this attribute in the dashboard? You can change the option order. If you set it to 'Display order' and remove the 'sortBy', it should do fine, I think.
phowie74 replied on at Permalink Reply
Thanks for taking the time to review my query. I think I may have over simplified it!

I have set the attribute up using Display Order as you suggest and I do have a select list generated for a search facility which does keep the Display Order. However, when I try to embed a page list and sort the pages by the award custom attribute I only have the option to list alphabetically (asc or desc).

I have taken a look at the page object using the print_r() function and I can see that the display order is in the custom attribute object but I have now idea how to access it and use it in the page list setup.

Thanks
Paul
A3020 replied on at Permalink Reply
A3020
Ah, got ya. Good question! At first I thought of $pl->sortByDisplayOrder(), but don't want the page display order, but a specific attribute display order.

Have you tried $pl->debug(1) and modify the query in a DB tool?

Pages are sorted with this sortBy function:
parent::sortBy('p1.cDisplayOrder', 'asc');

Maybe you can change the table alias + column name?

- Adri
phowie74 replied on at Permalink Reply
Thanks Adri for your suggestion.

I have never used the $pl->debug(1) option so I will look into that and give it a go. If I get a solution then I will post it back here.

Thanks again for taking the time to look into my query.

Thanks
Paul
A3020 replied on at Permalink Reply
A3020
Yeah, the debug method is really handy. In fact it just does Database::setDebug(true) before it executes the query and sets it back to false after execution.

Ok, let me know if you find something.
JohntheFish replied on at Permalink Reply
JohntheFish
Are the awards declared as a text attribute or as a select?

If you use a select attribute and add the categories in the order you want, internally they will be stored as (example)

1 - Gold
2 - Silver
3 - Bronze

Thus may in turn facilitate sortBy.
phowie74 replied on at Permalink Reply
Thanks for looking into my query.

The Awards attribute is a Select attribute with the Option Order set as Display Order.

This appears to work when I am creating the select list for my search form but unfortunately not when I am sorting my page list.

Thanks
Paul
JohntheFish replied on at Permalink Reply
JohntheFish
How big is the overall set of pages you are listing from?

If its not too big, loading the entire list or a subset that includes the results you want from the database and then sorting/filtering in php (or using my Magic Data) may be an easier alternative.
phowie74 replied on at Permalink Reply
The page list is not huge at the moment but the problem is it will be growing steadily.

http://www.englishwineproducers.co.uk/news/competitions/...

I am currently using PHP to filter the list until I can find the correct solution. My php skills are not the best and it strikes me that it is not the most efficient way to be outputting the list and this will only be magnified as the list of competition winners grows.

I will definitely check out your Magic Data add-on and see how that would work.

Thanks
Paul
JohntheFish replied on at Permalink Reply
JohntheFish
Looking at the site, maybe I am missing something but I don't think there would be any problems with a 2-part solution.

1. List/search the results for a competition (irrespective of Gold/Silver/Bronze). Do that in SQL using a page list object from php.

2. Sort and final filter those results for display using php.

As long as the SQL and page list object gets the overall result set down to what you need or close to it, loading each of those page objects and checking the specific award should not be an unreasonable overhead.
phowie74 replied on at Permalink Reply
I think that is effectively what I am doing at the moment - with the exception that I am using an embedded page list to get the list of award winners rather than a mysql query.

Would there be a big performance boost in using a direct mysql query over the page list?
JohntheFish replied on at Permalink Reply
JohntheFish
The c5 page list object you are embedding using php is actually a SQL query builder, so by SQL that is what I meant.

Writing custom SQL would be slightly faster, but would only be relevant if there are 1000s of pages and would be more work to maintain.
phowie74 replied on at Permalink Reply
I see - the $pl->debug(1) did give me a MySQL select statement output so I was wondering what the difference would be performance wise.

Thanks very much for your help.
Paul
phowie74 replied on at Permalink Best Answer Reply
For anyone stumbling across this thread (and possibly as a reminder to my future self) I managed to figure out how to access the select object displayOrder to help correctly organise my page list.

I came across this forum discussion relating to file object attributes -http://www.concrete5.org/community/forums/customizing_c5/getting-th...
and wondered if the same technique would apply to the select object attribute in page properties.

Loader::model('page_list');
$pl = new PageList();
$pl->filterByPath('/awards');
$winners = $pl->get();


Taking the original page list I was able to loop through each award winner and get the display order value:

foreach($winners as $k=>$v) {
$awd = $v->getAttribute('award');
$sort['awd'][$k] = $awd->get('0')->getSelectAttributeOptionDisplayOrder();
}


It is then possible to sort the array by the attribute displayOrder using php - in my case I was using array_multisort to sort on multiple values.

array_multisort($sort['awd'], SORT_ASC,$sort['comp'], SORT_ASC, $winners);


I don't know how much more performant this would be but it is certainly a lot neater solution to the tangle of nested loops I had implemented to fudge the final result!

Thanks
Paul
JohntheFish replied on at Permalink Reply
JohntheFish
I just marked you as best answer (in case modesty prevented you from doing so) - because your answer provides the details of the solution and will be the most help to anyone coming across this in the future.