0

I know this is probably the dumbest question ever but I'm just not getting warm with SQL.

I am writing this function which loads all users that have a role assigned:

function mymodule_get_users_by_role($rolename) {
  $role = user_role_load_by_name($rolename);
  $uids = db_select('users_roles', 'ur')
      ->fields('ur', array('uid'))
      ->condition('ur.rid', $role->rid, '=')
      // ->leftJoin('users', 'us', '(us.uid = ur.uid AND us.status = 1)')
      ->execute()
      ->fetchCol();
  return user_load_multiple($uids);
}

But I only want those users which's status = 1. I could do it afterwards but I really hope there is a way within the QuerySelect to add this filter beforehands. The commented line just produces a white screen of death so I'ma go with a nope. I dont even know if leftJoin is the right thing to do.

Thanks!

1
  • Are you, basically, asking how to make a join?
    – Mołot
    Jun 18, 2015 at 13:35

1 Answer 1

2

The following query should be self-explanatory.

$query=db_select('table1','t1');
$query->join('table2','t2','t1.nid = t2.nid');

$query->condition('t1.uid','1')
      ->condition('t2.uid','3')
      ->fields('t1',array('uid','nid'))
      ->fields('t2',array('date'));
1
  • $query = db_select('users_roles', 't1') ->join('users','t2', 't1.uid = t2.uid') ; $query->condition('t2.status', '1'); creates also a wod
    – Alex
    Jun 18, 2015 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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