How to get Custom Fields related to Joomla Content?

Question

I want to know how they get the values of custom user fields within Joomla articles. This is to contribute ideas and ways to solve it.

Answer 1

Let's say you have two custom fields in your database. In the #__fields_values table:

• 1 relates to the custom field called "genre"
• 2 relates to the custom field called "somethingelse"

To use pure sql to extract a single article's custom values and assign meaningful column aliases you can use a technique called a pivot -- it effectively "rotates" the alignment of the data 90° so-to-speak.

$userId = JFactory::getUser($this->item->created_by)->id;
$db = JFactory::getDbo();
$query = $db->getQuery(true)
            ->select([
                  "MAX(CASE WHEN field_id = 1 THEN value ELSE NULL END) AS genre",
                  "MAX(CASE WHEN field_id = 2 THEN value ELSE NULL END) AS somethingelse"
              ])
            ->from("#__fields_values")
            ->where("item_id = " . (int)$userId);
            ->group("item_id")
echo $query->dump();  // display the rendered query
$db->setQuery($query);
echo "<pre>";
var_export($db->loadObject());  // output the data

From this snippet, the following info will be presented.

SELECT MAX(CASE WHEN field_id = 3 THEN value ELSE NULL END) AS name,MAX(CASE WHEN field_id = 4 THEN value ELSE NULL END) AS location
FROM abcde_fields_values
WHERE item_id = 1
GROUP BY item_id

---

stdClass::__set_state(array(
  'genre' => 'Sci-Fi',
  'somethingelse' => 'Some random text',
))

The result set is a single row of associative keyed values. If a particular value doesn't exist for an article, null will be assigned as the fallback value.

Not using a pivot will mean your result set will have n rows with one value in each row.

While less compelling when dealing with a single article, this coding technique is a clean, direct, and powerful way to package multiple articles (just be sure to generate the result set with loadObjectList()).

Use the MAX(CASE(... syntax in the SELECT as many times as you like to continue grabbing more data that may exist in the "aggregrate" generated by GROUP BY.

Answer 2

With the following code you can obtain the values directly.

$userId = JFactory::getUser($this->item->created_by)->id;
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('value');
$query->from($db->quoteName('#__fields_values'));
$query->where($db->quoteName('item_id')." = ".$db->quote($userId));

$db->setQuery($query);
$fields = $db->loadColumn();
foreach ($fields as $field) {
    echo $field;
}