By grasmash on

Hello,

I'm creating a module in which I'd like to use TableSort, but I'm having some trouble creating the DBTNG query object.

This works:

  $header = array(
    //array('data' => t('Profile Type'), 'field' => 'pt.label'),
    array('data' => t('ID'), 'field' => 'pr.profile_id', 'sort' => 'ASC'),
    array('data' => t('Path'), 'field' => 'pr.path', 'sort' => 'ASC'),
  );

  $query = db_select('profile2_regpath', 'pr')
    //->join('profile_type', 'pt', 'pr.profile_id = pt.id')
    ->extend('TableSort')
    ->fields('pr', array('profile_id', 'path'))
    //->fields('pt', array('id', 'label'))
    ->orderByHeader($header); // tell the query object how to find the header information.

  $result = $query->execute();

However, I need to query two tables and display the results of the join when generating the table. Adding in the join logic leaves me with a WSOD:

  $header = array(
    array('data' => t('Profile Type'), 'field' => 'pt.label'),
    array('data' => t('ID'), 'field' => 'pr.profile_id', 'sort' => 'ASC'),
    array('data' => t('Path'), 'field' => 'pr.path', 'sort' => 'ASC'),
  );

  $query = db_select('profile2_regpath', 'pr')
    ->join('profile_type', 'pt', 'pr.profile_id = pt.id')
    ->extend('TableSort')
    ->fields('pr', array('profile_id', 'path'))
    ->fields('pt', array('id', 'label'))
    ->orderByHeader($header); // tell the query object how to find the header information.

  $result = $query->execute();

I really can't figure out what I'm doing wrong with the join. I'd adding join, a new field, and a new header row. This causes the page to display a blank white screen.

What am I doing wrong?

Any insight would be appreciated! Thanks!

⋅ Categories: Drupal 7.x

Comments

shabana.navas’s picture

shabana.navas commented

Your code looks fine to me. What I would suggest is: before you execute the code, try a dbq($query). This will give you the entire sql statement, through that you can see what is missing or wrong. So, just comment out $result = $query->execute(); and, instead, do: dbq($query);. This will only work if you have the devel module enabled. Hope it helps.

Shabana Navas
snavas@acromedia.com
Software Developer
Acro Media
https://www.acromedia.com
1-800-818-4564
Skype: super.shaba

grasmash’s picture

grasmash commented

Thanks for the suggestion. It turns out that my new MAMP stack had PHP error reporting turned off, so I missed the following error:

Fatal error: Call to a member function extend() on a non-object in /Applications/MAMP/htdocs/drupal7dev/sites/all/modules/profile2_regpath/profile2_regpath.module on line 55

Seems that once ->join has been set, ->extend is no longer recognized as an object that can have properties? Maybe I'm interpretting that incorrectly. I wish that I understood the working of the DBTNG a little bit better, and how it actually uses objects to make the queries.

Drupal's API suggests that 'SelectQueryExtender::join' should help out in this case:
http://api.drupal.org/api/drupal/includes--tablesort.inc/class/TableSort/7

What am I missing here?

kingandy’s picture

kingandy commented

I'm doing something very much like this and it took a while of hunting around old patches and forum posts to dig up a solution. It seems that some of these methods do unexpected things, and are maybe a little inconsistent about whether they're returning a new object or modifying the object that's calling them - 'join', in particular, modifies its subject but doesn't return anything, so you need to perform this method on an existing db_select object. Best practice seems to be to do all of your extending, then add your joins, then run a chain of methods and finally assign the result of the "execute" to something. Your code should work with only a small tweak:

  // First add all required extensions
  $query = db_select('profile2_regpath', 'pr')->extend('TableSort');
  // Next join your tables
  $query->join('profile_type', 'pt', 'pr.profile_id = pt.id');
  // Finally run all non-join methods - fields, orderByHeader, range, etc etc
  $query->fields('pr', array('profile_id', 'path'))
    ->fields('pt', array('id', 'label'))
    ->orderByHeader($header);

  $result = $query->execute();

++Andy
Developing Drupal websites for Livelink New Media since 2008

grasmash’s picture

grasmash commented

Alright, so I've made some progress, but I'm not out of the woods yet.

It seems like, for whatever reason, $query->orderByHeader($header) doesn't play well when you're joining tables.

On to the real goal! I'd love to make a form element that is a draggable table. Still can't seem to get this right. One last issue-- I need to apply a class to a table column (or group, as Drupal's API calls it).

If anyone could help me fight through this last hurdle, it would be much appreciated! Just need to be able to reorder a draggable table by weight and save the new weight!

function profile2_regpath_system_settings() {
  $form = array();
  
  $form['profile-types'] = profile2_regpath_table();
  drupal_add_tabledrag('profile-types-table', 'order', 'sibling', 'profile-weight');
  
  return system_settings_form($form);
}

/**
 *   Build a table render array of Profile2 profile types.
 *   A render array set for theming by theme_table().
 */
function profile2_regpath_table() {
  // Define table headers.
  $header = array(
    array('data' => t('Weight'), 'field' => 'pr.weight', 'sort' => 'ASC'),
    array('data' => t('ID'), 'field' => 'pt.id', 'sort' => 'ASC'),
    array('data' => t('Profile Type'), 'field' => 'pt.label'),
    array('data' => t('Path'), 'field' => 'pr.path', 'sort' => 'ASC'),
  );

  $query = db_select('profile2_regpath', 'pr');
  $query->join('profile_type', 'pt', 'pr.profile_id = pt.id');
  $query->extend('TableSort');
  
  // Must define fields in the same order that headers are defined.
  $query->fields('pr', array('weight'));
  $query->fields('pt', array('id', 'label'));
  $query->fields('pr', array('path'));
  
  //$query->orderByHeader($header);
  $result = $query->execute();

  $rows = array();
  foreach ($result as $row) {
   // This doesn't seem to work, but I know I need the group of fields that will be in column 'weight' to have the same class! 
   /*
    $weight = $row->weight;
    $row->$weight = array(
        '#value' => $weight,
        '#attributes' => array('class' => array('profile-weight')),
    );
    */
    
    $rows[] = array(
      'data' => (array) $row, 
      'class' => array('draggable'), // All draggable rows must have class 'draggable'.
    );
  }

  // build the table for the nice output.

  $table = array(
    '#theme' => 'table',
    '#header' => $header,
    '#rows' => $rows,
    '#attributes' => array('id' => 'profile-types-table'), // All draggable tables must have a set css id.
  );

  return $table;

}