Read fields_values table

For Joomla! 3.x Coding related discussions, you could also use: http://groups.google.com/group/joomla-dev-general

Moderators: ooffick, General Support Moderators

Forum rules
Post Reply
diasf
Joomla! Apprentice
Joomla! Apprentice
Posts: 45
Joined: Wed Nov 27, 2013 10:06 am

Read fields_values table

Post by diasf » Sun Sep 24, 2023 3:46 pm

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

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 17225
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: Read fields_values table

Post by toivo » Tue Sep 26, 2023 10:06 am

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 Talikka, Global Moderator

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 17225
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: Read fields_values table

Post by toivo » Tue Sep 26, 2023 10:44 am

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
Toivo Talikka, Global Moderator

diasf
Joomla! Apprentice
Joomla! Apprentice
Posts: 45
Joined: Wed Nov 27, 2013 10:06 am

Re: Read fields_values table

Post by diasf » Tue Sep 26, 2023 2:56 pm

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


Post Reply

Return to “Joomla! 3.x Coding”