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.

1 Answer 1

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));
4
  • I think this ís not the only mistake I made. So i changed that but it still doesn't work.
    – Towen
    May 25, 2017 at 20:12
  • Yeah there was one more, edited the answer
    – Clive
    May 25, 2017 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, 2017 at 20:52
  • Statement::fetchAllAssoc, so you should be able to do $friendlist = $result->fetchAllAssoc('some_column');
    – mradcliffe
    May 25, 2017 at 22:36

Your Answer

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

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