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?

Sharky · Accepted Answer · 2023-06-07 13:11:54Z

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

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

Matt Garrod · Accepted Answer · 2023-06-07 21:03:34Z

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" }

Thanks but selecting from different tables was not the question. I need to select from the same table. — maidan
– maidan, Commented Jun 13, 2023 at 15:34
I'm sorry for misinterpreting your question. I must've been tired when I answered it because now it's clear that you want the results indexed on a non-unique field and so my answer was not going to achieve what you wanted. My apologies to @Sharky, as his answer does indeed seem the simplest solution to what you need. — Matt Garrod
– Matt Garrod, Commented Jun 13, 2023 at 22:31

Stack Exchange Network

UNION Query with loadAssocList($key)

2 Answers 2

Hot Network Questions

UNION Query with loadAssocList($key)

2 Answers 2

Related

Hot Network Questions