[QDataTable] Sorting a column not by its content but by what it displays

[Charlie37]

Hi all

My class myTable inherits QDataTable and allows me to retrieve data from a MySql database.

I am using a QSqlCursor on a table called "staff" : it is really effective, you can edit/delete/insert records easily.
One column of myTable contains identifiers called "id_staff" that refer to another table called "personnel".
In the method paintField(), I tell not to draw these "id_staff", but the corresponding names retrieved using "personnel".

For instance, if I have tables "staff" and "personnel" like that :

//staff//
-----------------------------------
|| id_staff (int) ||
-----------------------------------
|| 12 ||
|| 23 ||
|| 37 ||
------------------------------------

//personnel//
-----------------------------------------------------------------
|| id(int) || name (varchar) ||
-----------------------------------------------------------------
|| 12 || Ringo ||
|| 23 || John ||
|| 37 || Paul ||
------------------------------------------------------------------

myTable will display as following.
(The default sorting of the items is defined by another column not mentioned here.)

(myTable)
Name
--------------------------------------
|| Ringo ||
|| Paul ||
|| John ||


If I click on the header of this column, it gets sorted using the id_staff.

(myTable after header click)
Name
--------------------------------------
|| Ringo ||
|| John ||
|| Paul ||


But I want to sort the column alphebetically, using the names displayed. It is more user-friendly.

( myTable after header click)
Name
--------------------------------------
|| John ||
|| Paul ||
|| Ringo ||


How can I do that ?
I have several ideas, but no one is efficient now :
- QSqlCursor is ok when you use only one table of your database, but I did not succeed to subclass it and make it reference both tables "staff" and "personnel".
- I intended to redefine the method that sort the columns or the one that compares the items of the QDataTable, but I did not find them.

//***********************************************************************//
myTable::myTable(...)
{
     ...
     QSqlCursor* cursor = new QSqlCursor ("staff");
     setSqlCursor(cursor);
 
     //I retrieve five columns, but this is the one I am interested in here
     addColumn("id_staff", "Name");
     .....
 
     setSorting( true );
 
     ....
}
 
myTable::paintField(QPainter *p, const QSqlField *field, 
                                const QRect& cr, bool selected) 
{
      if( ! field )
      return;
 
     ....
     if ( field->name() == "id_staff" ) 
     {
	int id = field->value().toInt();
 
         //using the id_staff, I retrieve the name of the staff member in table "personnel"
         QString name = retrieveName(id);
 
	 p->drawText( 2, 2, cr.width() - 4, cr.height() - 4, Qt::AlignLeft, name);	
     }
}
//***********************************************************************//

//***********************************************************************//
myTable::myTable(...)
{
     ...
     QSqlCursor* cursor = new QSqlCursor ("staff");
     setSqlCursor(cursor);

     //I retrieve five columns, but this is the one I am interested in here
     addColumn("id_staff", "Name");
     .....

     setSorting( true );

     ....
}

myTable::paintField(QPainter *p, const QSqlField *field, 
                                const QRect& cr, bool selected) 
{
      if( ! field )
      return;

     ....
     if ( field->name() == "id_staff" ) 
     {
	int id = field->value().toInt();

         //using the id_staff, I retrieve the name of the staff member in table "personnel"
         QString name = retrieveName(id);

	 p->drawText( 2, 2, cr.width() - 4, cr.height() - 4, Qt::AlignLeft, name);	
     }
}
//***********************************************************************//

To copy to clipboard, switch view to plain text mode 

I hope this is clear and someone has ever been through this before.
Thanks in advance