How to retrieve unique values before a parenthesis in JFormFieldList Filter query?

Question

I have a JHtml custom filter populated by a JFormField extended class. It is added in the view with the following code:

JHtmlSidebar::addFilter(
    '- Select Height -',
    'filter_height',
    JHtml::_('select.options', $heightOptions, "value", "text", $this->state->get('filter.height'), true)
);

It works as intended but for some reason the filter will not display the default text (- Select Height -) but instead shows the "select an option" string (I've included another filter - coded the same way - in the screen shot to show what it should look like).

After some messing around I figured out that I could fix it by changing my database query in the JFormField class. My original JFormField class is as follows:

JFormHelper::loadFieldClass('list');

class JFormFieldHeight extends JFormFieldList
{
    protected $type = 'Height';

    public function getOptions()
    {
        $options = array();

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

        $query->select("DISTINCT LEFT(a.description2, LOCATE('(', a.description2) - 1) AS height");
        $query->from('#__cadcam_disc AS a');
        $query->order("LEFT(a.description2, LOCATE('(', a.description2) - 1)");

        $db->setQuery($query);
        $options = $db->loadObjectList();

        if ($db->getErrorNum()) {
            JError::raiseWarning(500, $db->getErrorMsg());
        }

        return $options;
    }
}

This code causes the problem but if I remove the LOCATE function from the query it works fine. Obviously I need to keep the original query so this is not an option.

For info, the select box is loaded with the values in both cases including the default value but even selecting the default value will revert to the "select an option" string:

Any ideas what is going on here?

Answer 1

I think I found the problem. I was working on one of my own extensions when the same issue came up. Check the HTML output and see if there are two options with a 0 value.

This is the function that caused the issue

public function getAdminList()
{
    $db = JFactory::getDbo();
    $query  = $db->getQuery(true);

    $query->select('DISTINCT notify.admin_to_notify AS id');
    $query->from('#__babelu_exams_notification_profiles AS notify');

    $query->select('admin.name');
    $query->join('LEFT', '#__users AS admin ON admin.id = notify.admin_to_notify');

    $query->order('admin.name');

    $db->setQuery($query);
    return $db->loadObjectList();
}

As you can see we are both using the DISTINCT selector which is probably the root of the issue. Checking the HTML output, this is what the select looked like

<select name="filter[admin]" id="filter_admin" class="span12 small chzn-done" onchange="this.form.submit()" style="display: none;">
   <option value="">
       Filter by Administrator
   </option>
   <option value="0" selected="selected">
       Not Assigned
   </option>
   <option value="0" selected="selected"></option>
</select>

So I changed last part of the getAdminList() function slightly to add a title to the default before sending it to my view.

result = $db->loadObjectList();
    if ($result[0]->id == 0)
    {
        $result[0]->name = JText::_('COM_BABELU_EXAMS_RESULTS_NONE_ASSIGNED');
    }
    else
    {
        $notAssigned = new stdClass();
        $notAssigned->name = JText::_('COM_BABELU_EXAMS_RESULTS_NONE_ASSIGNED');
        $notAssigned->id = 0;
        array_unshift($result, $notAssigned);
    }

    return $result;

And the issue was resolved.

I hope this helps. Happy Joomla!ng

Answer 2

I figured that the cause of the issue was down to what the result of the query so I played around with it a bit and stripped out any non numeric strings that were returned like so:

$query->select("DISTINCT LEFT(a.description2, LOCATE('(', a.description2) - 1) AS height");
$query->from('#__cadcam_disc AS a');
$query->where("LEFT(a.description2, LOCATE('(', a.description2) - 1) > 0");
$query->order("LEFT(a.description2, LOCATE('(', a.description2) - 1) + 0");

And that rectified the situation.

I still don't understand the cause of the problem but at least it is fixed now.

Answer 3

You don't mention the Joomla version you're working with but:

1) If you're wanting "- Select Height -" I'm not sure why are you wrapping it in a JText::_() call first. JText::_() is used translate a key from a language file, e.g. if you had this in an English language file in /components/com_mycomponent/language/en-GB/en-GB.com_mycomponent.ini which had this line in it (along with many others)

 COM_MYCOMPONENT_PLACEHOLDER_SELECT_HEIGHT="- Select Height -"

Note the key on the left of the = has no spaces in it, and is keyed by the component. By default, if the key isn't found you should get back the text passed into the function, but, by removing the JText::_() call you can eliminate that as the issue.

2) You will need to add the code for your JFormField which is where the first item would normally be set, so we can see what the issue is.

A print_r($heightOptions) could also shed some light on whats going on.

Previously in an extended JFormFieldList, we prepend the first item to $options created from the database, e.g:

$noneSelected = new stdClass;
$noneSelected->value = '';
$noneSelected->text = '- ' . JText::_('COM_MYCOMPONENT_PLACEHOLDER_SELECT_HEIGHT') . ' -';
array_splice($options, 0, 0, array($noneSelected));

Answer 4

As a matter of showing some refinements / best practices, I'll address @doovers's posted solution.

$query = $db->getQuery(true)
    ->select("DISTINCT SUBSTRING_INDEX(description2, '(', 1) AS height");
    ->from("#__cadcam_disc");
    ->where("LOCATE('(', description2) > 0")
    ->order("height");

• SUBSTRING_INDEX() is the single function call equivalent to LEFT(LOCATE())
• A table alias (a) is not necessary because there is only one table being queried (no ambiguity to resolve).
• When filtering out values that do not have a ( in your WHERE clause, you can just check for a positive LOCATE() value (the first character would be at position 0, but the logic of your task means we don't need to check if > -1, ergo > 0 will do.
• The string manipulation in the SELECT clause which is given a column alias of height can be referred to in the ORDER BY clause. This is much easier on the eyes.

Here's a playground for anyone who wants to get to know these functions better.

https://www.db-fiddle.com/f/ci6ZfAQAPkbMKtak4T5kbm/0