UNION Query with loadAssocList($key)

Question

So here is a small example of the query I play around with:

$db->setQuery('SELECT position, title FROM jos_modules
UNION (
SELECT position, title FROM jos_modules
)');
$data= $db->loadAssocList();

This returns all modules.

It does not matter if this does not make sense. It's just an example of a more complex query. The UNION approach is a must in the original query.

But i need the data indexed on a key like this:

$data= $db->loadAssocList('position');

If doing that, not all modules are returned. So it seems the indexing only catches the first result.

Can such a UNION query be done and results returned indexed on a key?

Answer 1

No, there's no such option. You have to do it yourself:

$data = [];

foreach ($db->loadAssocList() as $row)
{
    $data[$row['position']][] = $row;
}

Answer 2

The OP actually works as it should. The actual query that has been posted UNIONs the data with itself, so the records in both sets have the same keys. Only one set of data is returned because the first set is overwritten by the second set. The same issue would be seen in @Sharky's answer.

If you change the query to have different data sets:

$db->setQuery('SELECT position, title FROM #__modules UNION (SELECT id AS position, title FROM #__content)');
$data = $db->loadAssocList('position');

then both sets of data appear in the result:

array(121) {
  ["position-7"]=>
  array(2) {
    ["position"]=>
    string(10) "position-7"
    ["title"]=>
    string(10) "Login Form"
  }
  ["login"]=>
  array(2) {
    ["position"]=>
    string(5) "login"
    ["title"]=>
    string(5) "Login"
  }
...
  [202]=>
  array(2) {
    ["position"]=>
    string(3) "202"
    ["title"]=>
    string(14) "Example article 1"
  }
  [203]=>
  array(2) {
    ["position"]=>
    string(3) "203"
    ["title"]=>
    string(24) "Example article 2"
  }