Read fields_values table

[diasf]

Hello

Still using J.3 and trying to use fields_values table as categories table aditional data.

I have 4 different fields_values records for each categories record and I'm trying to select the corresponding fields_values when reading categories:

$query = $db->getQuery(true)
->select($db->quoteName(array('loc.id','loc.title','loc.alias','fv.field_id','fv.value'), array('locid','designacao','alias','field_id','valor')))
->from('#__categories AS loc')
->join('LEFT','#__fields_values AS fv ON fv.item_id = loc.id')
->where($db->quoteName('loc.id') . ' = '.JRequest::getInt('id'));
$db->setQuery($query);
$r = $db->loadObjectList();

It's all right, it reads everything I need. However, $r has 4 rows and I need to join them to continue processing:

foreach ($r as $r1) {
if ($r1->field_id == 12) {$result->val12 = $r1->valor;}
if ($r1->field_id == 8 ) {$result->val8 = $r1->valor;}
if ($r1->field_id == 7) {$result->val7 = $r1->valor;}
if ($r1->field_id == 5) {$result->val5 = $r1->valor;}
$result->locid = $r1->locid;
$result->designacao = $r1->designacao;
$result->alias = $r1->alias;
}
return $result;

This works, but I am sure it's not the best way. Besides, the last 3 lines are processed in every cicle, that means 4 times. There would be a way to read as one output-record, not 4-output-records.

I tried to find a way to do something like
"Select ... fv.value as val12 where ... field_id.value = 12, ... fv.value as val8 where ... field_id.value = 8... etc" but found no way to do it.

Can please anyone help me how to do it?

Thank you very much for any help.

Fernando

[toivo]

There are easier ways to access the custom fields. Not sure if a variant of the following method, modified to access category fields, would work in Joomla 3. This code was tested only with user custom fields and Joomla 4.

Code: Select all

	$customFields = FieldsHelper::getFields('com_users.user', Factory::getUser($user->id), true);
	...
	echo '<b>' . $user->username . '</b><br>';
	foreach ($customFields as $field) {
		if ($field->group_id == $fieldGroup) {
			if (empty($field->value) && !$showAll) {
				continue;
			}
			echo $field->title . ': ' . $field->value . '<br>';
		}
	}

[toivo]

Found the following example from last year that shows the correct context to be used with FieldsHelper::getFields() when accessing category custom fields:
get custom fields in category default.php

[diasf]

Thank you toivo. I'll give it a try! The second method looks better than the first. Let's try.