I cant get a simple view to sort properly.
I am sorting on a CCk field that was populated in the node using a select values list (populated using php).

I copied the default sql code shown from the view and pasted it into phpadmin and ran the code.
The value returned for the field on which to sort was returned as a text version of the key number of the look up array (not the actual displayed item from the look up)
eg a list
key: Value
0 Pears
1 Oranges
2 Apples
10 Bananas
11 Lemons

sorted should be
2 Apples
10 Bananas
11 Lemons
1 Oranges
0 Pears

but is being sorted thus:
0 Pears
1 Oranges
10 Bananas
11 Lemons
22 Apples
etc

I dont understand how the view returns the real text value to the view, but it seems to be sorting before converting it to its text value
...... or am I doing something wrong.

Comments

markus_petrux’s picture

markus_petrux commented
Status: Active » Fixed

Views sorts using the ORDER BY clause in the query. The numeric keys of the allowed values list are the values stored in the database for this field, so that's what your SQL engine uses to sort, as instructed when a sort item is added to the view for that field. You cannot tell Views to sort by the label of the allowed values list, just because this information is not stored in the database.

You may want to use a text field instead, then key your allowed values list by something that can be used to simulate sort by labels of the allowed values list.

Pears|Pears
Oranges|Oranges
Apples|Apples
Bananas|Bananas
Lemons|Lemons

The value on the left is the data stored in the database, so that should match all your requirements with this field.

The above example could even be simplified as the following, because if the left values is omitted, then the label is used as the value key as well.

Pears
Oranges
Apples
Bananas
Lemons

Note that you need a Text field, not a Number field.

jimboh’s picture

jimboh commented
Status: Fixed » Active

Thanks for the prompt response.
I do use a text field.
I populate the list from a php fetch. But the data comes from a multi value field in several nodes so I couldnt ude nid or delta (would need to use nid & delta).
So I used a variable like so:

$sql ="select `field_mapareas_value` from {content_field_mapareas} order by `field_mapareas_value`" ;
$x = 0;
$res = db_query($sql);
while($row = db_fetch_array($res)){
$rows[$x] = $row['field_mapareas_value'];
$x=$x+1;
}
return $rows;

I tried replacing [$x] with ['field_mapareas_value'] (to give me Apples|Apples)
eg
$sql ="select `field_mapareas_value` from {content_field_mapareas} order by `field_mapareas_value`" ;
$res = db_query($sql);
while($row = db_fetch_array($res)){
$rows['field_mapareas_value'] = $row['field_mapareas_value'];
}
That only returned the last element of the list not the full list.
so I tried
$sql ="select `field_mapareas_value` from {content_field_mapareas} order by `field_mapareas_value`" ;
$res = db_query($sql);
while($row = db_fetch_array($res)){
$rows[] = $row['field_mapareas_value'];
}
That returned a full list but the text field in the db still contains a (textual) 22 ie still using a key no??

jimboh’s picture

jimboh commented

Another thing I would be grateful if someone can explain...

If only the key is stored in the db, where is the view getting the label (as markus called it) at display time?? , The view returns the same text value as displayed in the original node (not the key value). The view query when run from phpadmin returns the key value not the display value or label... so how is the done?

markus_petrux’s picture

markus_petrux commented
Status: Active » Fixed

Try with this loop:

while($row = db_fetch_array($res)){
  $value = $row['field_mapareas_value'];
  $rows[$value] = $value;
}

Re: "If only the key is stored in the db, where is the view getting the label (as markus called it) at display time??"

The formatter of the field loads the allowed values list, and uses that array to render the field output.

jimboh’s picture

jimboh commented

Thanks Markus, That works!

I didnt understand your comment re "Re"
If the key value (in my previous method with numbered keys) is stored in the db, where are the textual labels stored?? The renderer must be getting them from somewhere.
I have searched the tables and cant find them, is it stored in the content type somehow/where. I know it now works so am grateful for that but I like to understand how these things work so I dont have to keep asking questions.

markus_petrux’s picture

markus_petrux commented

The allowed values list is stored with the field settings, serialized. See how content_fields() function works, in content.module.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.