How to populate custom field dropdown with values from DB

[Elvis]

Hi!

I trying to create a new custom field, which will have a select field with menu items from certain menu type, I got stuck at populating this select field with values from database.

You can see the code here: https://github.com/esedic/listmenus/blob/2b0ad43e139954a653e5284568542090bbd4266b/plg_listmenus/fields/listmenus.php#L42

Also I need to access these 3 object properties (id, title, link) on frontend, so the goal is to have in table #__fields_values in `value` column record like this:

{"id":"115","title":"Article","link":"index.php?option=com_content&view=article&id=6"}

I got to this point:

If I print DB query correct values are returned:

Array ( 

   [0] => stdClass Object ( 

        [id] => 101 

        [title] => Home 

        [link] => index.php?option=com_content&view=featured ) 

   [1] => stdClass Object ( 

        [id] => 115 

        [title] => Article 

        [link] => index.php?option=com_content&view=article&id=1 ) 

)

But I don't know, how to get this in the select field.

 

Any help would be appreciated.

[Viper]

You need to get parent options, so you can set their values in xml form. And merge parent and current options.

protected function getOptions()
{
    // Accepted modifiers
    $hash = md5($this->element);

    if (!isset(static::$options[$hash]))
    {
        static::$options[$hash] = parent::getOptions();
        $db = JFactory::getDbo();

        // Construct the query
        $query = $db->getQuery(true)
            ->select('id AS value, title AS text')
            ...;

        // Setup the query
        $db->setQuery($query);

        // Return the result
        if ($options = $db->loadObjectList())
        {
            static::$options[$hash] = array_merge(static::$options[$hash], $options);
        }
    }

    return static::$options[$hash];
}

But imho it's better to use Factory::getMenu() and search desired menu with loop. Because it's do not required an extra query to select menus.

[Roger Creagh]

Hi Elvis,

Further to what viper said:

A select list has only two parameters for each row - the value that will be returned, called "value" and the text that will be displayed, called "text"

So you need to build the string you want to display into a field called "text" something like this:

->select('CONCAT(firstname, " ", lastname) AS text');

so that the array only contains two items per row - "value" (which is the id field in your case) and "text"

then simply:

        $options = $db->loadObjectList();

        // Merge any additional options in the XML definition.

        $options = array_merge(parent::getOptions(), $options);

        return $options;

You could equally well get the data as you are and then build the string you want in php and create a new array with the correct text and return that.

Don't forget to merge in any options from the parent (eg a default value)

RogerCO

On Sat, 2021-04-24 at 02:22 -0700, Elvis wrote: