1 Topic by robin.jones

  • robin.jones
  • Member
  • Offline
  • From: UK
  • Registered: 24-06-2011
  • Posts: 18

Topic: Ordering of termlist cache lookups

I've been struggling to get my select lists for terms to reflect the sort_order field when using the "cache" view. The list actually appear in descending order of the "sort_order" field. I'm specifically using a select list in "complex_attr_grid" which uses the cache view, but I think that this problem will occur for any cache termlist lookups.

I think that I've narrowed the issue down to seemingly strange behaviour in postgresql (9.3). For some reason a descending index is being applied when a single termlist_id is being selected (as an integer).

If I run:
  select * from cache_termlists_terms
or
  select * from cache_termlists_terms where termlist_id = 54.0

then the output is correctly in ascending order, but if I use:

  select * from cache_termlists_terms where termlist_id = 54
  select * from cache_termlists_terms where termlist_id between 54 and 55

and some other variants, then it is shown in descending order.

This seems like unexpected behaviour in postgresql, so I wondered whether anyone else has encountered this or similar issues and maybe found a way of making the indexing consistent.

I have temporarily edited our copy of  data_entry_helper.php to make complex_attr_grid use the "list"view, but there must be a proper solution to be found.

Robin Jones
IT Manager: Species Conservation
Wildfowl & Wetlands Trust

robin.jones's Website

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Ordering of termlist cache lookups

Hi Robin
I'm pretty sure that PostgreSQL (and many other RDBMS) does not guarantee any sort order unless you include an ORDER BY in the query. The output will probably vary between versions and queries depending on how the optimiser decides to extract the data. I've just looked at the code in data_entry_helper.php and see the following is used to load the terms:

         $termlistData = self::get_population_data(array(
            'table'=>'termlists_term',
            'extraParams'=>$options['extraParams'] + array('termlist_id'=>$def['termlist_id'], 'view'=>'cache')
          ));

I would imagine that adding 'orderby'=>'sort_order' to the extraParams would force the sort order. I've not got a test case set up right now so are you happy to see if that works?
Cheers
John

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by robin.jones

  • robin.jones
  • Member
  • Offline
  • From: UK
  • Registered: 24-06-2011
  • Posts: 18

Re: Ordering of termlist cache lookups

Thanks John,
orderby does force the correct ordering ok. It seems likely that many termlists will not have any "sort_order" values assigned and therefore it seems sensible to allow the form to override the sort field as a parameter for the control and to default to using "term" as the search field.

I've now tried :

$extraParams=array_merge( array('termlist_id'=>$def['termlist_id'],'view'=>'cache','orderby'=>'term'),$options['extraParams']);
$termlistData = self::get_population_data(array(
            'table'=>'termlists_term',
            'extraParams'=>$extraParams)
            );

which shows the list in ascending "term" order.

Passing in:
@extraParams={"orderby":"sort_order"}

successfully changes the order to "sort_order".


Is 'orderby'=>'term' the most sensible default?

Robin Jones
IT Manager: Species Conservation
Wildfowl & Wetlands Trust

robin.jones's Website