0

I am trying to perform a query on a database that has the following info (in Drupal 7.21):

nid | sid | cid | data
----------------------
32  |  2  |  1  | date
32  |  2  |  2  | name
32  |  3  |  1  | date
32  |  3  |  2  | name   
32  |  4  |  1  | date
32  |  4  |  2  | name 
         etc..        

I would like to perform a query on the database where I extract the following information in this manner:

nid | sid |    date    |    name
-----------------------------------
 32 |  2  | date_value | name_value 
 32 |  3  | date_value | name_value 
 32 |  4  | date_value | name_value 

Where date and name are the actual values. Of course the result of this query would be an object. I am using the table display here to illustrate what I want to achieve in terms of data extraction.

My query looks like this:

$query = db_select('webform_submitted_data', 'sub1');
$query->innerJoin('webform_submitted_data', 'sub2', 'sub1.nid=sub2.nid and sub1.sid=sub2.sid');
$query = $query
  ->fields('sub1', array('nid', 'sid'))
  ->addField('sub1', 'data', 'date')
  ->addField('sub2', 'data', 'name')
  ->condition('sub1.nid', 32)
  ->condition('sub2.cid', 2)
  ->condition('sub1.cid', 1);

I thought the addField command would accomplish this but Drupal doesn't like it. What am I doing wrong?

Thanks.

1

Okay, I fixed it by doing the following:

$query->addField('sub1', 'data', 'date');
$query->addField('sub2', 'data', 'name');
$query = $query
  ->fields('sub1', array('nid', 'sid', 'data'))
  ->fields('sub2', array('data'))
  ->condition('sub1.nid', 32)
  ->condition('sub2.cid', 2)
  ->condition('sub1.cid', 1);

The issue was addField does not return an object so you can't chain the commands together. Also note that even though you aliased the colums to date and name you still need to refer to them as tablename.data. The aliased names will appear in the result of the query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.