TableSort issue with expressions

I updated to HEAD (October 8, 2010 @ 13:20) and now receive this error when I try to resort by a column other than the default:

PDOException: SQLSTATE[42703]: Undefined column: 7 ERROR: column "itemlink" does not exist LINE 1: SELECT itemlink AS itemlink, battery_model_list_view.company... ^: SELECT itemlink AS itemlink, battery_model_list_view.company_name AS company_name, battery_model_list_view.abbreviation AS abbreviation, '<a href="/batt/details/' || batt_model_id ||'">' || model || '</a>' AS itemlink FROM {battery_model_list_view} battery_model_list_view ORDER BY itemlink ASC; Array ( ) in battery_info_batt_list() (line 93 of /var/www/drupal/sites/default/modules/battery_info/battery_info.module).

It looks like the generated query is incorrect, because it thinks that itemlink (an expression) is a database column. This may be independent of the original issue, which still occurs even with the revision of HEAD I am using.

The query is built with the D7's db_select system:

// Set up the query
  $query = db_select('battery_model_list_view');
  $query = $query->extend('TableSort');
  $query = $query->orderByHeader($headers);
  $query->addField('battery_model_list_view', 'company_name');  // FIRST db column
  $query->addExpression("'<a href=\"/batt/details/' || batt_model_id ||'\">' || model || '</a>'", 'itemlink');  // SECOND db column
  $query->addField('battery_model_list_view', 'abbreviation');  // THIRD db column

  // Execute the query
  $result = $query->execute();
  $rows = $result->fetchAll(PDO::FETCH_ASSOC);

The issue occurs when using TableSort with any PDO queries with an addExpression call, not just my above example. Just checked, and this is still an issue with 7.x-dev (as of Oct 21, 2010, 12:15PM EST)

I don't see how that fixes the bug with TableSort's handling of expressions. If I want to use my database's built-in functions or stored procedures on a set of data, Drupal should accommodate this.

I understand that the example code I gave is insecure, but it is just an example. The database is able to transform data, and I don't have to run an additional PHP loop. There is a bug, as Crell pointed out in his post. If I should start a new bug report to make this a regular bug, let me know.

If there are limitations to PDO, I can write it out as SQL I suppose.

Crell, thank you for clarifying the differences between the Drupal's database system and the PDO-provided features.

Here's an example, that should work for most databases (I'm using PostgreSQL). First, create a table

CREATE TABLE drupal_inventory_example
(
  manufacturer character(32),
  model character(32),
  number_in_stock integer
)

The first column is the manufacturer of the equipment (like Ford or Jaguar). The second column is the model (like Mustang or XJ). The third column is the number of that item in stock (like 5).

Sample data:

INSERT INTO drupal_inventory_example (manufacturer, model, number_in_stock) VALUES ('Ford', 'Taurus, 50);
INSERT INTO drupal_inventory_example (manufacturer, model, number_in_stock) VALUES ('Ford', 'Mustang', 30);
INSERT INTO drupal_inventory_example (manufacturer, model, number_in_stock) VALUES ('GMC', 'Yukon, 20);
INSERT INTO drupal_inventory_example (manufacturer, model, number_in_stock) VALUES ('Hyundai', 'Sonata', 100);
INSERT INTO drupal_inventory_example (manufacturer, model, number_in_stock) VALUES ('Hyundai', 'Accent', 83);
INSERT INTO drupal_inventory_example (manufacturer, model, number_in_stock) VALUES ('Hyundai', 'Elantra', 3);

Now I have a page callback that looks like this:

/**
 * NOTE: This is for the inventory example.
 *
 * @return
 *  Formatted HTML for the listing
 */
function sample_sales_list {
  
  $out = "";  // output (html) to be returned
  
  // Create headers for the table, and define default sorting.
  // Each header has a 'data' item (used for the title) and a 
  // 'field' item (to map it to a database column).
  $headers = array(
    // "Manufacturer" heading
    array(
      'data' => 'Manufacturer',
      'field' => 'manufacturer',
      'sort' => 'asc',
    ),

    // "Model" heading
    array(
      'data' => 'Model',
      'field' => 'model',
    ),

    // Sales goal for next week (will be 20% of stock)  - This is the THIRD column
    array(
      'data' => 'Sales Goal for next week',
      'field' => 'sales_goal',
    ),
        
    // "Inventory" heading - Fourth column
    array(
      'data' => 'Number in Stock',
      'field' => 'number_in_stock'
    ),
  );

  $query = db_select('inventory_example', 'ie');
  $query = $query->extend('TableSort');

  $query->addField('ie', 'manufacturer');
  
  $query->addField('ie', 'model');
  $query->addExpression('ie.number_in_stock * .2', 'sales_goal');
  $query->addField('ie', 'number_in_stock');
  
  $query = $query->orderByHeader($headers);
  
  
  $result = $query->execute();
  
  $rows = $result->fetchAll(PDO::FETCH_ASSOC);
  
  $table = array(
    'header' => $headers,
    'rows' => $rows,
    'caption' => null,
    'attributes' => array(),
    'colgroups' => array(),
    'sticky' => null,
    'empty' => null,
  );
  
  $out .= theme_table($table);
  
  return $out;
  
}

Notice that the third $headers element, "Sales Goal for next week," is before the "Number in Stock" column. However, when the page is created the "Sales Goal" numbers get moved to the fourth column ("Number of Stock") (see attached image). This is, of course, a made-up example, but I just tested it with HEAD from earlier today, and it still happens.

But that's not the point. I think that my example maybe make too much of the column order. The important thing is the header order. What I'm saying is that the $headers used by theme_table don't work right. Even when the headers are in the order I want them to be in $headers array, when I call theme_table then it moves the 'sales_goal' *data* from the third to the fourth column, even though 'sales_goal' is the third header. It does this with any $header column that gets its results from addExpression, not addField. This is illustrated in the screenshot attached to #9.

Isn't that a bug with theme_table? I updated #9 to make it clearer.

If I remove the $query = $query->extend('TableSort'); line and the $query = $query->orderByHeader($headers); line, then the table column data is still out of order. Does this mean that the bug is in theme_table?

So will this bug remain open then? I will use Dave Reid's work around (#6) for now, but like you said in #14, it's a bit of a drag on performance.

Should it be reclassified as a database system bug?