1

I want to list only the users with a group_id value of 10.

Why doesn't the following query work?

$db    = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('a.id, a.name, a.username, b.group_id')           
      ->from ('#__users a')    
      ->join ('INNER', '#__user_usergroup_map b')
      ->where('b.group_id=10')  
      ->where('a.block=0')        
      ->order($order); 

$db->setQuery($query);
return $db->loadObjectList();
  • A good complete question would include why your query isn't working. If there is an error, we would like to see it. If it is the wrong result set, set up a db-fiddle.com demo for us and post the hyperlink. – mickmackusa Jun 26 at 22:41
1

Your question would be more complete if you stated why it doesn't "work".

I don't see anything wrong with the WHERE clause declarations (because the default glue is AND).

What does jump out at me is the fact that you are not expressing the columns which should be used to JOIN the two tables. This will be written with the ON keyword within the join() method.

Based on https://docs.joomla.org/Tables/user_usergroup_map

I'd say you mean to join like this:

->innerJoin("#__user_usergroup_map b ON a.id = b.user_id")

More generally, when a query doesn't work as expected inside your Joomla environment, I recommend that you $query->dump(), transfer your query to phpMyAdmin and see what transpires.

Here are some of my related posts that may prove to be enlightening:

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.