changing default sort order on a related field

[Markus]

Hi Steve, hi all,

In my central table list_vehicles I have a field called L_HID reading out the hersteller (name of the manufacturer) from the related table list_hersteller by using the foreign key L_HID. The syntax in relationships.ini is

[list_hersteller]
__sql__="SELECT l_hid, hersteller FROM list_hersteller WHERE l_hid='$L_HID' ORDER BY hersteller"

In list view of list_vehicles I would like to have the records ordered by hersteller (name of the manufacturer) instead of having it ordered by the ID L_HID

When I put

if ( !isset($_REQUEST['-sort']) and @$_REQUEST['-table'] == 'list_vehicles' ){
$_REQUEST['-sort'] = $_GET['-sort'] = 'hersteller ASC';
}

on top of my index.php I get an error like:

Fehler: Konnte den folgenden Datensatz nicht laden: Unknown column 'list_vehicles.hersteller' in 'order clause' SELECT length(`list_vehicles`.`l_vid`) as `__l_vid_length`,`list_vehicles`.`l_vid`,length(`list_vehicles`.`L_HID`) as `__L_HID_length`,`list_vehicles`.`L_HID`, ..... FROM `list_vehicles` ORDER BY `list_vehicles`.`hersteller` ASC LIMIT 0,1

This, I suppose because there is no field called hersteller (only the ID L_HID) in my table list_vehicles but only in the related table list_hersteller.

So how else could I do this?

Thank you

Markus

[shannah]

Hi Markus,

Best not to put ORDER BY clauses in your __sql__ for relationships.Ê Better to do:

[list_hersteller]

__sql__="SELECT l_hid, hersteller FROM list_hersteller WHERE l_hid='$L_HID'"

metafieldsrder=hersteller

This designates the hersteller field as the order field for the relationship.Ê

-Steve

[Markus]

Hi Steve,

thank you for answering my question. Nevertheless it does not work properly.

I now have

[list_hersteller]

__sql__="SELECT l_hid, hersteller FROM list_hersteller WHERE l_hid='$L_HID'"

metafieldsrder=hersteller

in my relationships.ini of table list_vehicles

When I open my table list_vehicles in list view I got the records ordered by the tables primary key l_vid (the vehicle ID)

When I point on the label for the hersteller values I get something like

http://path_to/my_app/index.php?-table=list_vehicles&-action=list&-cursor=7&-skip=0&-limit=30&-mode=list&-sort=L_HID+asc

When I click it, the records get sorted by L_HID (the manufacturers ID) not hersteller (the manufacturers name)

The manufacturers IDs and names are stored in the related table list_hersteller but not in alphabetical order.

Any idea?

Markus

[shannah]

That is strange.Ê Perhaps we are talking about 2 different things.

Are you trying to sort a related list (i.e. in the relationship tab "list_hersteller"), or a list of records.Ê The URL that you posted makes it look like you are just sorting a standard list of records (not a related list).Ê Your relationship settings won't have any effect on that.Ê My guess is that you have given your L_HID column a label of "hersteller" and used a valuelist to have this column display the name of the make rather than the ID.Ê This, however won't change the fact that when you sort on that column, it sorts on the column value and not on the make.

If you want to be able to sort standard result lists based on the vehicle make, you could add a calculated field to the table containing the vehicle make.Ê Then you would be able to sort on that.

e.g.

in the fields.ini file:

__sql__ "select v.*, h.hersteller from vehicles v left join list_hersteller h on v.l_hid=h.l_hid"

Then the hersteller column would show up in the regular result list for vehicles.Ê You could sort on that column in the standard way.

Hope this makes sense.
-Steve

[Markus]

Hi steve,

I had to puzzle a little bit because when I tried your solution first I had the make sorted in the right way but in two columns, the first one the L_HID (labelled hersteller) and as a calculated field hersteller in the last column.

Now I changed it to:

__sql__ = "select lv.* from list_vehicles lv left join list_hersteller lh on lv.L_HID=lh.l_hid ORDER BY lh.hersteller"

and this does the job.

Thanks again

Markus

I had another posting but will try to write it again because something get lost by the online editor.

[shannah]

Hi Markus,

This will be problematic.Ê You really shouldn't have an order by clause in the __sql__ parameter, because it may cause inconsistent results in other parts of the application.Ê The reason for a left join is so that we can just add columns onto the end of each row of the result set without changing the result set itself.Ê Adding an order by clause will change the result set and possibly cause it to not line up with other things...

I can't think of what it might break right now... but there is a good chance that something will get broken with this approach.

Better to leave off the order by clause, then do the sort in the normal way.

Oh also, I noticed that you removed the hersteller column from the result set .. so your __sql__ parameter won't actually do anything except slow the application down.

Change it to:
__sql__ = "select lv.*, lh.hersteller from list_vehicles lv left join list_hersteller lh on lv.L_HID=lh.l_hid"

Then do the sort the normal way:
if ( !isset($_REQUEST['-sort']) and @$_REQUEST['-table'] == 'list_vehicles' ){

$_REQUEST['-sort'] = $_GET['-sort'] = 'hersteller ASC';

}

-Steve

[Markus]

Hi steve,

thanks for that but when I do it this way I have two columns with the hersteller in my list view.

And if I make the L_HID column a visibility:list=hidden field in fields.ini I have again the problem that the column with the calculated field hersteller comes last in the list view. I need it in the first column.

How to manage this?

Markus

[shannah]

Sorry.. I guess the current release doesn't integrate the calculated fields perfectly.Ê My dev version has this problem fixed, but I'm not sure exactly when I'll be ready to release that version.

-Steve

[Markus]

Hi Steve,

is it possible to use your dev version before it is released or could I maybe change only some files of 0.71 to make ordering of calculated fields possible?

Thank you

Markus

[shannah]

Hi Markus,

I still have some things to work out with the dev version before letting it loose... but I think the following will help you achieve ordering properly.Ê In the Dataface/ResultList.php file, replace the constructor function so that it is:
function Dataface_ResultList( $tablename, $db='', $columns=array(), $query=array()){
ÊÊÊÊ ÊÊÊ $app =& Dataface_Application::getInstance();
ÊÊÊÊ ÊÊÊ $this->_tablename = $tablename;
ÊÊÊÊ ÊÊÊ if (empty($db) ) $db = $app->db();
ÊÊÊÊ ÊÊÊ $this->_db = $db;
ÊÊÊÊ ÊÊÊ $this->_columns = $columns;
ÊÊÊÊ ÊÊÊ if ( !is_array($columns) ) $this->_columns = array();
ÊÊÊÊ ÊÊÊ $this->_query = $query;
ÊÊÊÊ ÊÊÊ if( !is_array($query) ) $this->_query = array();
ÊÊÊÊ ÊÊÊ
ÊÊÊÊ ÊÊÊ $this->_table =& Dataface_Table::loadTable($tablename);
ÊÊÊÊ ÊÊÊ $fieldnames = array_keys($this->_table->fields(false,true));
ÊÊÊÊ ÊÊÊ $fields =& $this->_table->fields(false,true);
ÊÊÊÊ ÊÊÊ
ÊÊÊÊ ÊÊÊ if ( count($this->_columns)==0 ){
ÊÊÊÊ ÊÊÊ ÊÊÊ
ÊÊÊÊ ÊÊÊ ÊÊÊ foreach ($fieldnames as $field){
ÊÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ if ( @$fields[$field]['filter'] ) $this->_filterCols[] = $field;
ÊÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ if ( $fields[$field]['visibility']['list'] != 'visible') continue;
ÊÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ if ( $this->_table->isPassword($field) ) continue;
ÊÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ if ( isset( $fields[$field] ) and !eregi('blob', $fields[$field]['Type']) ){
ÊÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ $this->_columns[] = $field;
ÊÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ }
ÊÊÊÊ ÊÊÊ ÊÊÊ }
ÊÊÊÊ ÊÊÊ ÊÊÊ /*
ÊÊÊÊ ÊÊÊ ÊÊÊ $grafted_fields = $this->_table->graftedFields();
ÊÊÊÊ ÊÊÊ ÊÊÊ $grafted_fieldnames = array_keys($grafted_fields);
ÊÊÊÊ ÊÊÊ ÊÊÊ foreach ($grafted_fieldnames as $field){
ÊÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ if ( $grafted_fields[$field]['visibility']['list'] != 'visible') continue;
ÊÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ $this->_columns[] = $field;
ÊÊÊÊ ÊÊÊ ÊÊÊ }*/
ÊÊÊÊ ÊÊÊ } else {
ÊÊÊÊ ÊÊÊ ÊÊÊ
ÊÊÊÊ ÊÊÊ
ÊÊÊÊ ÊÊÊ ÊÊÊ foreach ($fieldnames as $field){
ÊÊÊÊ ÊÊÊ ÊÊÊ ÊÊÊ if ( @$fields[$field]['filter'] ) $this->_filterCols[] = $field;
ÊÊÊÊ ÊÊÊ ÊÊÊ }
ÊÊÊÊ ÊÊÊ }
ÊÊÊÊ ÊÊÊ
ÊÊÊÊ ÊÊÊ $this->_resultSet =& Dataface_QueryTool::loadResult($tablename, $db, $query);
ÊÊÊÊ ÊÊÊ
ÊÊÊÊ }
This should make it so that the order of the columns obeys the order set in the fields.ini file (this is just a cut and paste from the dev version, but I think it should work in the current release also.
-Steve