Need help with a custom sort function

Hi there. I want to implement a custom sort function for a treeview model. Quick background - I want the parent IDs ( integers ) to be sortable in either ascending or descending order, but I always want the child IDs ( integers ) to be sorted in ascending order.

To do this, I need to know inside my custom sort function, what the currently requested sort order is … and I guess I’d invert my return value for children if the user is requesting descending order?

How do I fetch the requested sort order? I’ve tried ( in perl ):

my ( $sort_column_id , $sort_order ) = $liststore->get_sort_column_id();

… but $sort_order is always coming back as ‘1’ ( integer ), whether I’ve requested ascending or descending.

Hi Dan,

One way to go about it is to us sqlite. It can take care of complex sorts easily. You can open an in memory database with sqlite, load the data in and sort. Then copy the data into a liststore and show it with a treeview.

Eric


/*
   gcc -Wall sqlite_sort1.c -o sqlite_sort1 `pkg-config --cflags --libs gtk+-3.0` -lsqlite3

   Ubuntu18.04 with GTK3.22
*/

#include<gtk/gtk.h>
#include<sqlite3.h>

static sqlite3 *db=NULL;

static void initialize_database();
static void run_sql(GtkWidget *button, gpointer *data);
static void get_sqlite_data(const gchar *sql_string, gpointer *data);
static void close_program(GtkWidget *widget, gpointer data);

int main(int argc, char *argv[])
  {
    gtk_init(&argc, &argv);

    GtkWidget *window=gtk_window_new(GTK_WINDOW_TOPLEVEL);
    gtk_window_set_title(GTK_WINDOW(window), "SQLite Sort");
    gtk_window_set_position(GTK_WINDOW(window), GTK_WIN_POS_CENTER);
    gtk_container_set_border_width(GTK_CONTAINER(window), 10);
    gtk_window_set_default_size(GTK_WINDOW(window), 950, 400);
    g_signal_connect(window, "destroy", G_CALLBACK(close_program), NULL);

    initialize_database();

    GtkWidget *tree=gtk_tree_view_new();
    gtk_tree_view_set_grid_lines(GTK_TREE_VIEW(tree), GTK_TREE_VIEW_GRID_LINES_BOTH);
    gtk_widget_set_hexpand(tree, TRUE);
    gtk_widget_set_vexpand(tree, TRUE);

    GtkWidget *scroll=gtk_scrolled_window_new(NULL, NULL);
    gtk_container_add(GTK_CONTAINER(scroll), tree);

    GtkWidget *entry=gtk_entry_new();
    gtk_entry_set_text(GTK_ENTRY(entry), "SELECT T1.id, T1.TreeType, T2.Fk, T2.NameID, T2.TreeName FROM Tree T1 INNER JOIN Trees T2 ON T1.id=T2.Fk ORDER BY T1.id DESC;");
    gtk_widget_set_hexpand(entry, TRUE);

    GtkWidget *button=gtk_button_new_with_label("Run SQL");
    gpointer widgets[2]={entry, tree};
    g_signal_connect(button, "clicked", G_CALLBACK(run_sql), widgets);

    GtkWidget *grid=gtk_grid_new();
    gtk_grid_attach(GTK_GRID(grid), scroll, 0, 0, 1, 1);
    gtk_grid_attach(GTK_GRID(grid), entry, 0, 1, 1, 1);
    gtk_grid_attach(GTK_GRID(grid), button, 0, 2, 1, 1);

    gtk_container_add(GTK_CONTAINER(window), grid);
   
    gtk_widget_show_all(window);

    //Get initial data sorted for the treeview.
    run_sql(button, widgets);

    gtk_main();
    return 0;   
  }
static void initialize_database()
  {
    gchar *err_msg=NULL;
    gint rc=sqlite3_open(":memory:", &db);

    if(rc!=SQLITE_OK) 
      {       
        g_print("Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
      }

    gchar *sql="CREATE TABLE Tree(Id INTEGER PRIMARY KEY, TreeType TEXT);" 
               "INSERT INTO Tree VALUES(1, 'Cedar');" 
               "INSERT INTO Tree VALUES(2, 'Pine');" 
               "INSERT INTO Tree VALUES(3, 'Fir');"
               "CREATE TABLE Trees(Fk INT, NameID INT, TreeName TEXT);" 
               "INSERT INTO Trees VALUES(1, 1, 'Western Red Cedar');" 
               "INSERT INTO Trees VALUES(1, 2, 'Deodar Cedar');" 
               "INSERT INTO Trees VALUES(1, 3, 'Insense Cedar');" 
               "INSERT INTO Trees VALUES(2, 1, 'Western White Pine');" 
               "INSERT INTO Trees VALUES(2, 2, 'Long Leaf Pine');"
               "INSERT INTO Trees VALUES(3, 1, 'Grand Fir');"
               "INSERT INTO Trees VALUES(3, 2, 'Noble Fir');"
               "INSERT INTO Trees VALUES(3, 3, 'Balsam Fir');"
               "INSERT INTO Trees VALUES(3, 4, 'Fraser Fir');";

    rc=sqlite3_exec(db, sql, 0, 0, &err_msg);

    if (rc!=SQLITE_OK) 
      {        
        g_print("Failed to create table\n");
        g_print("SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
      }
  }
static void run_sql(GtkWidget *button, gpointer *data)
  {
    gchar *sql_string=g_strdup(gtk_entry_get_text(GTK_ENTRY(data[0])));
    get_sqlite_data(sql_string, data);
    g_free(sql_string);         
  }
static void get_sqlite_data(const gchar *sql_string, gpointer *data)
  {
    gint i=0;
    gint columns=0;
    GtkTreeIter iter;
    gint ret_val=0;
    sqlite3_stmt *stmt1=NULL;
    GPtrArray *column_names=g_ptr_array_new_with_free_func(g_free);
    GtkListStore *new_store=NULL;

    //Prepare the incoming SQL statement.
    sqlite3_prepare_v2(db, sql_string, -1, &stmt1, 0);
    columns=sqlite3_column_count(stmt1);
    new_store=gtk_list_store_new(1, G_TYPE_STRING);

    //Check the statement.
    if(stmt1!=NULL)
      {
        ret_val=sqlite3_step(stmt1);
        //Check generalized column types. SQLite dynamically typed so this may be trouble for mixed data.
        gint column_types[columns];
        GType type_array[columns];
        for(i=0;i<columns;i++)
          {
            column_types[i]=sqlite3_column_type(stmt1, i);
            switch(column_types[i])
              {
                case 1:
                  type_array[i]=G_TYPE_INT;
                  break;
                case 2:
                  type_array[i]=G_TYPE_DOUBLE;
                  break;
                case 3:
                  type_array[i]=G_TYPE_STRING;
                  break;
                default:
                  g_print("Column Type Error\n");
               }                  
            g_ptr_array_add(column_names, g_strdup(sqlite3_column_name(stmt1, i)));
          }
        //Reset the list store based on column types.
        if(sql_string!=NULL)
          {
            gtk_list_store_set_column_types(GTK_LIST_STORE(new_store), columns, type_array);
          }
        //Get the rows.
        while(ret_val==SQLITE_ROW)
          {            
            //Load the list store with data.
            gtk_list_store_append(new_store, &iter);
            for(i=0;i<columns;i++)
              {
                switch(column_types[i])
                  {
                    case SQLITE_INTEGER:
                      gtk_list_store_set(new_store, &iter, i, sqlite3_column_int(stmt1, i), -1);
                      break;
                    case SQLITE_FLOAT:
                      gtk_list_store_set(new_store, &iter, i, sqlite3_column_double(stmt1, i), -1);
                      break;
                    case SQLITE_TEXT:
                      gtk_list_store_set(new_store, &iter, i, sqlite3_column_text(stmt1, i), -1);
                      break;
                    default:
                      g_print("Column Type Error\n");
                  }
              }
            
            ret_val=sqlite3_step(stmt1);
          }
      }
    else            
      {
        const gchar *message=sqlite3_errmsg(db);
        g_print("%s\n", message);
      }
    if(stmt1!=NULL) sqlite3_finalize(stmt1);

    //Setup new treeview.
    if(stmt1!=NULL)
      {
        //Set new model.
        gtk_tree_view_set_model(GTK_TREE_VIEW(data[1]), GTK_TREE_MODEL(new_store));
        g_object_unref(new_store);
        //Drop old columns. 
        gint n_columns=gtk_tree_view_get_n_columns(GTK_TREE_VIEW(data[1]));
        GtkTreeViewColumn *t_column=NULL;
        for(i=0;i<n_columns;i++)
          {
            t_column=gtk_tree_view_get_column(GTK_TREE_VIEW(data[1]), 0);
            gtk_tree_view_remove_column(GTK_TREE_VIEW(data[1]), t_column);
          }
        //Setup new columns.
        GtkCellRenderer *renderer=gtk_cell_renderer_text_new();
        g_object_set(renderer, "xalign", 0.5, "editable", FALSE, NULL);
        for(i=0;i<columns;i++)
          {
            t_column=gtk_tree_view_column_new_with_attributes(g_ptr_array_index(column_names, i), GTK_CELL_RENDERER(renderer) , "text", i, NULL);
            gtk_tree_view_column_set_alignment(t_column, 0.5);
            gtk_tree_view_column_set_resizable(t_column, TRUE);
            gtk_tree_view_append_column(GTK_TREE_VIEW(data[1]), t_column);
          }
      }
    
    g_ptr_array_free(column_names, TRUE);
  }
static void close_program(GtkWidget *widget, gpointer data)
  {
    sqlite3_close(db);
    gtk_main_quit();
  }    

Hi Eric. Thanks for the reply. I make heavy use of SQLite ( and Postgres ) in this application already. In fact I was previously doing the sorting in the DB, however this had some usability issues. I’ll provide some more background.

I’m displaying a hierarchy of processes in a realtime dashboard. At the highest level, I have batches. Batches spawn multiple jobs ( and jobs can themselves spawn child jobs ). Commonly users would want to flip the sorting on batch ID ( to show oldest / newest batches at the top of the treeview ), but we always want the jobs themselves to be sorted in ascending order ( as work is split up into jobs that run in parallel, and the order of these jobs is one of the important things that users make use of to quickly locate the data they’re after. As this is a realtime dashboard, new data is always coming in about job status changes and new jobs.

If I went with the approach of doing all the sorting in the DB, I’d have to do a full model drop and rebuild when the user wanted to sort things differently, and when I did a refresh ( currently I poll, but I’m going to move to using Postgres’ event-pushing capability ). Doing a full drop and rebuild would cause the treeview to loose its current select and scroll position. It would also put significantly more load on the database ( and the GUI app itself ) to use the DB for this.

It seems like what I really need is to know the sort order the user has triggered by clicking on the column headers. I’m very close. Surely there is a way to do this?

You can save the sort order of an id column in the liststore with an array. It might be useful to keep a copy of the current id order so that it could be restored if need be.

I don’t have how to update a liststore with joined tables figured out without a copy. Maybe update a sqlite in memory database and go through and compare and update all row values in the liststore that have changed.

Eric

/*
    gcc -Wall sort_list1.c -o sort_list1 `pkg-config --cflags --libs gtk+-3.0`

    With Ubuntu18.04 and GTK3.22.

*/
#include<gtk/gtk.h>

enum
{
   ID,
   PROGRAM,
   IMAGE,
   COLUMNS
};

static void get_list_order(GtkTreeModel *tree_model, GtkTreePath *path, GtkTreeIter *iter, gpointer new_order, GArray *list_order)
  {
    gint i=0;
    GtkTreeIter tree_iter;
    guint value=0;
    gint len=list_order->len;
    for(i=len;i>0;i--) g_array_remove_index_fast(list_order, i-1);
    if(gtk_tree_model_get_iter_first(tree_model, &tree_iter))
      {
        do{
            gtk_tree_model_get(tree_model, &tree_iter, 0, &value, -1);
            g_print("%i ", value);
            g_array_append_val(list_order, value);
          }while(gtk_tree_model_iter_next(tree_model, &tree_iter));
        g_print("\n    ");
      }

    for(i=0;i<list_order->len;i++) g_print("%i ", g_array_index(list_order, gint, i));
    g_print("\n");
  }
int main(int argc, char *argv[])
  {
    gtk_init(&argc, &argv);

    GtkWidget *window=gtk_window_new(GTK_WINDOW_TOPLEVEL);
    gtk_window_set_title(GTK_WINDOW(window), "Tree Sort");
    gtk_window_set_position(GTK_WINDOW(window), GTK_WIN_POS_CENTER);
    gtk_window_set_default_size(GTK_WINDOW(window), 300, 150);
    g_signal_connect(window, "destroy", G_CALLBACK(gtk_main_quit), NULL);

    gint i=0;
    GArray *list_order=g_array_new(FALSE, FALSE, sizeof(gint));
    GtkTreeIter iter;
    GtkListStore *store = gtk_list_store_new(COLUMNS, G_TYPE_UINT, G_TYPE_STRING, G_TYPE_INT);
    gtk_list_store_append(store, &iter);
    gtk_list_store_set(store, &iter, ID, 0, PROGRAM, "R", IMAGE, 1,  -1);
    g_array_append_val(list_order, i);
    i=2;
    gtk_list_store_append(store, &iter);
    gtk_list_store_set(store, &iter, ID, 1, PROGRAM, "R", IMAGE,  2, -1);
    g_array_append_val(list_order, i);
    i=1;
    gtk_list_store_append(store, &iter);
    gtk_list_store_set(store, &iter, ID, 2, PROGRAM, "R", IMAGE, 3, -1);
    g_array_append_val(list_order, i);
    i=3;
    gtk_list_store_append(store, &iter);
    gtk_list_store_set(store, &iter, ID, 3, PROGRAM, "A", IMAGE, 1, -1);
    g_array_append_val(list_order, i);
    i=4;
    gtk_list_store_append(store, &iter);
    gtk_list_store_set(store, &iter, ID, 4, PROGRAM, "A", IMAGE, 2, -1);
    g_array_append_val(list_order, i);
    i=5;
    gtk_list_store_append(store, &iter);
    gtk_list_store_set(store, &iter, ID, 5, PROGRAM, "A", IMAGE, 3, -1);
    g_array_append_val(list_order, i);
    i=6;
    gtk_list_store_append(store, &iter);
    gtk_list_store_set(store, &iter, ID, 6, PROGRAM, "S", IMAGE, 1, -1);
    g_array_append_val(list_order, i);


    //Order the list with the array.
    //gtk_list_store_reorder(store, (gint*)(list_order->data));

    GtkTreeModel *sort_model=gtk_tree_model_sort_new_with_model(GTK_TREE_MODEL(store));
    g_signal_connect(sort_model, "rows-reordered", G_CALLBACK(get_list_order), list_order);
    g_object_unref(G_OBJECT(store));

    GtkWidget *tree=gtk_tree_view_new_with_model(sort_model);
    gtk_widget_set_hexpand(tree, TRUE);
    gtk_widget_set_vexpand(tree, TRUE);

    GtkCellRenderer *renderer1 = gtk_cell_renderer_text_new();
    g_object_set(renderer1, "editable", FALSE, NULL);
   
    GtkTreeViewColumn *column1 = gtk_tree_view_column_new_with_attributes("ID", renderer1, "text", ID, NULL);
    gtk_tree_view_column_set_sort_column_id(column1, 0);
    gtk_tree_view_append_column(GTK_TREE_VIEW(tree), column1);    
    GtkTreeViewColumn *column2=gtk_tree_view_column_new_with_attributes("Program", renderer1, "text", PROGRAM, NULL);
    gtk_tree_view_column_set_sort_column_id(column2, 1);
    gtk_tree_view_append_column(GTK_TREE_VIEW(tree), column2);
    GtkTreeViewColumn *column3=gtk_tree_view_column_new_with_attributes("Image", renderer1, "text", IMAGE, NULL);
    //gtk_tree_view_column_set_sort_column_id(column3, 2);
    gtk_tree_view_append_column(GTK_TREE_VIEW(tree), column3);

    GtkWidget *scroll=gtk_scrolled_window_new(NULL, NULL);
    gtk_container_add(GTK_CONTAINER(scroll), tree);

    gtk_container_add(GTK_CONTAINER(window), scroll);
   
    gtk_widget_show_all(window);
    gtk_main();

    g_array_free(list_order, TRUE);

    return 0;   
  }

my ( $sort_column_id , $sort_order ) = $liststore->get_sort_column_id();

According to https://developer.gnome.org/gtk3/stable/GtkTreeSortable.html#gtk-tree-sortable-get-sort-column-id get_sort_column_id returns three values:

  • the actual return value (gboolean) of the corresponding C function
  • the two out parameters (sort_column_id, order)

so this should be

my ( $is_not_special_column_id, $sort_column_id , $sort_order ) = $liststore->get_sort_column_id();

1 Like

I gave this another try. This one feeds in a couple of sql statements to test the updating of the treeview with the selected row and the scroll position. Thinking that with Postgres’ event-pushing it could send the application individual INSERTs, DELETEs and UPDATEs. These would update the sqlite database and then the new results would be copied to the liststore and treeview. For sorting with a column button, the sort state can be saved and then applied to the updated listview. OK, a bit rough around the edges but maybe a little closer than the last one.

Eric

Thankyou Roderich :slight_smile: The docs for that function still confound me … but you are absolutely correct.

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.