Query with join doesn't work

Question

I have custom tables and want to execute this query.

select displayname from foolzz_users_profile
join foolzz_user_has_user on (fk_user1=1 or fk_user2=1)
where fk_users_uid != 1;

The "1" numbers will be replaced with the users uid. This is my query in the module.

$user = \Drupal\user\Entity\User::load(\Drupal::currentUser()->id());
$uid = $user->get('uid')->value;

$query = \Drupal::database()->select('foolzz_users_profile', 'e');
$query->fields('e', array('fk_users_uid,displayname'));
$query->join('foolzz_user_has_user','d','d.fk_user1=:uid OR d.fk_user2=:uid',array(':uid' => $uid));
$query->condition('e.fk_users_uid', $uid, '<>');

$friendlist = $query->execute()->fetchAssoc();

I'm searching for hours and still couldn't get it to work.

Answer 1

A couple of things...firstly

$query->condition('data.fk_users_uid', !$uid);

!$uid returns false when used on a positive non-zero integer in PHP (and true for zero, the anonymous user).

You probably meant:

$query->condition('data.fk_users_uid', $uid, '<>');

Secondly, you're using the same alias for both tables. That'll never work. Change the one in the join to data2 or something:

$query->join('foolzz_user_has_user', 'data2', 'data2.fk_user1=:uid OR data2.fk_user2=:uid', array(':uid' => $uid));