2

I am writing a db_select in D7 to get various user fields, including the URI's of user's avatars (if it exists).

My initial attempt looked this:

$query = db_select('node', 'n');
$query->join('users', 'u', 'n.uid = u.uid');
$query->join('file_managed', 'f',
  'f.fid = u.picture OR u.picture = :null', array(':null' => 0));
$query->fields('f',array('uri'));

This almost do the job. The only glitch is that if the user has no avatar, the field uri always contains the uri pointing to f.fid #1. In that case, I want it be missing or empty.

As a workaround, I now execute two db_selects: First one for all users that have an avatar:

$query->join('file_managed', 'f', f.fid = u.picture);

Then one for all users without:

$query->join('file_managed', 'f', u.picture = :null', array(':null' => 0));

I then just concatenate the results. This produces the desired result, but is a bit clumsy.

I would like to do it with one db_select if that is possible.

1 Answer 1

1

Just do a left join on 'files_managed' table

$query = db_select('node', 'n');
$query->join('users', 'u', 'n.uid = u.uid');
$query->leftjoin('file_managed', 'f', 'f.fid = u.picture');
$query->fields('f',array('uri'));
$query->fields('n',array('nid'));
$query = $query->execute();
dpm($query->fetchAll());
4
  • Does this work when both f.fid and u.picture are not set?
    – apaderno
    Sep 18, 2015 at 5:55
  • It should, u.picture is set as 0 as default value which will bring uri as NULL
    – arpitr
    Sep 18, 2015 at 6:04
  • Thanks! I need to read up on the subtle differences between join and leftjoin. Sep 18, 2015 at 7:06
  • Inner vs Left stackoverflow.com/questions/5706437/…
    – arpitr
    Sep 18, 2015 at 7:09

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.