3

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).

enter image description here

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:

enter image description here

Any ideas what is going on here?

4 Answers 4

2

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

4
  • Thanks Mathew well spotted! The solution I went with solved the problem but didn't explain what was happening so now I know!
    – doovers
    May 19, 2014 at 1:25
  • =^D Glad I could help. If you don't mind I'd love to hear how you solved it. It is always good to see a problem solved in different ways. Thanks in advance. May 19, 2014 at 1:28
  • I just changed my query around to sanitise the data it returned. See my answer to this post.
    – doovers
    May 19, 2014 at 2:12
  • Just an update about this. I figured out that when you use the district(COL) if you add WHERE COL != 0 it resolved the query issue, so you don't have to manually check with the if/else Mar 26, 2015 at 14:14
1

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.

1
  • You can more simply use $query->order("height"), right. I don't think you need to repeat the function calls. For completeness, could you add some sample db data to your question?
    – mickmackusa
    Mar 10, 2019 at 12:21
0

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));
1
  • Thanks for the response! The JText business was just a result of my laziness when adding the code, I will replace it with the language placeholder later :) I will update the question with the relevant info!
    – doovers
    May 13, 2014 at 1:02
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.