1

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.

5

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));
  • I think this ís not the only mistake I made. So i changed that but it still doesn't work. – Towen May 25 '17 at 20:12
  • Yeah there was one more, edited the answer – Clive May 25 '17 at 20:19
  • After your last suggestion i noticed I had a small typo in my own code but not here in this question. Thank you for your answer. The problem is solved :) – Towen May 25 '17 at 20:52
  • Statement::fetchAllAssoc, so you should be able to do $friendlist = $result->fetchAllAssoc('some_column'); – mradcliffe May 25 '17 at 22:36

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.