loadAssocList() is not working properly when assigning a column name as the key parameter

Question

I have a complex query like below. I want the p.ComponentId values to be the first level keys of the multidimensional result set array. However, it does not work as expected. In my case, when I don't set a key, it returns 12 items in the array as expected. When I nominate a column as the parameter of loadAssocList(), it only returns one (the last) item.

Am I doing something wrong?

The query is this:

// Get database connector
  $db = JFactory::getDbo();

 // Prepare query to get all fields
  $query = $db->getQuery(true)
    ->select($db->qn('t.value'))    
    ->from($db->qn('#__rsform_properties', 'p'))
    ->leftJoin($db->qn('#__rsform_components', 'c').' ON ('.$db->qn('c.ComponentId').' = '.$db->qn('p.ComponentId').')')
    ->leftJoin($db->qn('#__rsform_translations', 't').' ON (CONCAT('.$db->qn('p.ComponentId').',\'.ITEMS\') LIKE '.$db->qn('t.reference_id').')')
    ->where($db->qn('c.FormId').'='.$db->q($args['form']->FormId))
    ->where($db->qn('t.form_id').'='.$db->q($args['form']->FormId))
    ->where($db->qn('p.PropertyName').'='.$db->q('ITEMS'))
    ->where($db->qn('c.Published').'='.$db->q(1))
    ->order($db->qn('c.Order'));
 $db->setQuery($query);

  // Get all fields
  $fields = $db->loadAssocList();

Which returns 12 results just fine.

But when i change the last row to:

  $fields = $db->loadAssocList('p.ComponentId');

I get this result:

array(1) {
  [""]=>
  array(1) {
    ["value"]=>
    string(2) "Ja"
  }
}

I also tried t.reference_id, but this provides the same result.

I was expecting something like this:

array(1) {
  ["$key"]=>
  array(1) {
    ["value"]=>
    string(2) "Ja"
  }
}

where $key would be the row's p.ComponentId value.

Answer 1

Most critical to fixing your issue is to include the p.ComponentId column in your select() call so that it is available at the time the result set is being constructed.

If you have your error reporting turned on/up, then I expect that you would have seen several of these:

Notice: Undefined index: p.ComponentId in ...

I have a few additional pointers to give, so I'll start with my suggested rewrite of your database interacting script then explain what I've done and why.

$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query
    ->select(
        $db->qn([
            'p.ComponentId',
            't.value'
        ])
    )
    ->from($db->qn('#__rsform_properties', 'p'))
    ->innerJoin(
        $db->qn('#__rsform_components', 'c')
        . ' ON ' . $db->qn('p.ComponentId') . ' = ' . $db->qn('c.ComponentId')
    )
    ->innerJoin(
        $db->qn('#__rsform_translations', 't')
        . ' ON ' . $query->concatenate([$db->qn('p.ComponentId'), $db->q('.ITEMS')]) . ' = ' . $db->qn('t.reference_id')
        . ' AND ' . $db->qn('c.FormId') . ' = ' . $db->qn('t.form_id')
    )
    ->where([
        $db->qn('c.FormId') . ' = ' . $db->q($args['form']->FormId),
        $db->qn('p.PropertyName') . ' = ' . $db->q('ITEMS'),
        $db->qn('c.Published') . ' = 1'
    ])
    ->order($db->qn('c.Order'));
$db->setQuery($query);
$fields = $db->loadAssocList('ComponentId');

I would expect your result set to resemble this structure now:

[
    1 => [
        "ComponentId" => 1,
        "value" => "Ja"
    ],
    2 => [
        "ComponentId" => 2,
        "value" => "Nein"
    ]
]

1. $db and $query need to be declared at the start of this process, because these variables will be leveraged in subsequent method calls (such as q(), qn(), and concatenate()).
2. I've expanded the internal syntax of some of the method calls to avoid excessively wide lines of code with the intent to make the code easier to read. You are welcome to compact the code into fewer lines if you wish, but be sure to obey PSR-12 coding standards and/or Joomla's coding standards.
3. Because of your posted WHERE conditions, I can infer that INNER JOIN is better suited than LEFT JOIN because you have no interest in rows from the joined tables that lack $args['form']->FormId as their form identifier. I have removed one of the WHERE conditions and added the necessary logic into the second innerJoin() call.
4. Your join ON conditions are sufficiently simple and do not require any parentheses to preserve the logic. For this reason, I recommend removing the unnecessary syntax.
5. Joomla has a concatenating helper function available which will make your code more "portable" (meaning it will be instantly functional on a wide spectrum of database drivers e.g. mysqli, sqlite, sqlsrv, etc...), so I recommend employing concatenate(). Unlike $db, it must be called from $query -- this is the reason that $query is declared as a separate variable before diving into query building syntax. There is a potential gotcha about the concatenate() call, but it will not impact your usage here.
6. For best performance, you must not use LIKE unless it is necessary. Because there are no wildcards (% or _) in your comparison value, a simple = comparison is all that is required.
7. It is not beneficial to quote-wrap a 1 for your query, so I've omitted that q() call.
8. When you call loadAssocList() and feed it the desired column as its parameter / first level key, you no longer need to declare the table alias. The result set generated by the SELECT will never include the table or alias that it came from. In fact, if you had two columns in the SELECT that came from two different tables, but shared the same name, then the former column would be overwritten by the latter one. In other words, make sure that your column names are uniquely identified so that you never accidentally exclude any data from the result set.
9. Before I edited your question, you referred to the result set as an array of objects, but you are calling loadAssocList() -- which generates an array of arrays. If you desire an array of objects, you'll want to call loadObjectList() instead. You will still be able to nominate the first level keys in the same fashion.

p.s. The reason that you were getting 1 row instead of 12 rows, was because the missing column was generating an empty value. PHP arrays may never have duplicate keys on the same level, so every subsequent row of data was overwriting the earlier row because of the duplicated (empty string) first level key.