0

hello everybody i have to make tableselect form with some extra fields. To make table sortable I have to show which DB fields is used for the column in header:

$header = array(
    'uid' => array('data' => t('UID'), 'field' => 'u.uid'),
    'name' => array('data' => t('Name'), 'field' => 'u.name'),
    'mail'=>array('data' => t('Mail'), 'field' => 'u.mail'),
    'last_access' => array('data' => t('Last Access'), 'field' => 'u.access'),
    'login_count' => array('data' => t('Login Count'),'field'=>'a.count_logins'),
    'join_date'=>array('data' => t('Join Date'), ),
    'post_count'=>array('data' => t('Posts Count'),),
    'comment_count'=> array('data' => t('Comment Count'),)
  );

then I created a query for this:

$query = db_select('users','u');
  $query = $query->extend('PagerDefault')->extend('TableSort');
  $query->leftJoin(db_select('mycustom_report','r')->fields('r',array('count(*)    AS  count_logins','uid'))->groupBy('uid'),'a','a.uid=u.uid');
  $uids = $query
    ->fields('u',array('uid','name','mail','access'))->fields('a',array('count_logins'))->where('u.uid!=0')
    ->limit(50)
    ->orderByHeader($header)
    ->execute()
    ->fetchAll();

this query gives me this error

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'r.countAScount_logins'

any ways to create count field with db_select method in drupal? db_query() seems does not support ->orderByHeader() method

my full query which works fine looks like:

select u.uid,u.created,u.name,u.mail,u.access,counted_login,counted_post,counted_comment 
  from users as u 
          left join (select r.uid, count(*) as counted_login  from mycustom_report as r group by r.uid ) as d on u.uid = d.uid 
          left join (select r.uid, count(*) as counted_post from node as r group by r.uid ) as p on p.uid = u.uid
          left join (select r.uid, count(*) as counted_comment from comment as r group by r.uid ) as c on c.uid = u.uid;
  • to_string rendered this SELECT u.uid AS uid, u.name AS name, u.mail AS mail, u.access AS access, a.count_logins AS count_logins FROM {users} u LEFT OUTER JOIN (SELECT r.countAScount_logins AS countAScount_logins, r.uid AS uid FROM {mycustom_report} r GROUP BY uid) a ON a.uid=u.uid WHERE (u.uid!=0) ORDER BY u.uid ASC – werqious Nov 6 '12 at 7:57
2

This error simply means that column does not exist in your database which means u have to write addExpression() method for solving these problem as follows and even you have not joined the table properly use your query as below

$query = db_select('users','u');
$query = $query->extend('PagerDefault')->extend('TableSort');
$query->leftJoin('mycustom_report','r','r.uid=u.uid');
$query->fields('r');
$query->fields('u');
$query->addExpression('COUNT(uid)', 'uid_count');
$result = $query->execute()->fetchAll();

this will surely removes your error the main issue with your query is that you are combining the db_query syntax with db_select

1

Here is the D7 approach to a count function. It will also rewrite it for you so you can have a single function/query and return the count or the actual query.

function example($count=FALSE) {
  $query = db_select('node', 'n')
    ->fields('n', array('nid'))
    ->condition('n.title', $title, '=');

  if($count) return $query->countQuery()->execute()->fetchField();
  // Or you can just return the results.
  return $query->execute();
}

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.