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();
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:
