Database query to select a specific year

Question

I am trying to convert a small php script to a component for Joomla, So far it is working well. But I would like to make some adjustments.

The view shows data in a table of numbers from specific dates over the last 10 years. I have added the column sort to allow sorting of certain columns but wonder if there is a method that allows selection of specific years by default? I would like it to show the current year and then have a drop down list to select prior years as the original script did.

This is the query in my model, any suggestions on how to achieve this would be appreciated Steve

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

        // Select the required fields from the table.
        $query
            ->select(
                $this->getState(
                    'list.select', 'DISTINCT a.*'
                )
            );

        $query->from('`#__fts` AS a');

        // Join over the users for the checked out user.
        $query->select('uc.name AS uEditor');
        $query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out');

        // Join over the created by field 'created_by'
        $query->join('LEFT', '#__users AS created_by ON created_by.id = a.created_by');

        if (!JFactory::getUser()->authorise('core.edit', 'com_fts'))
        {
            $query->where('a.state = 1');
        }

        // Filter by search in title
        $search = $this->getState('filter.search');

        if (!empty($search))
        {
            if (stripos($search, 'id:') === 0)
            {
                $query->where('a.id = ' . (int) substr($search, 3));
            }
            else
            {
                $search = $db->Quote('%' . $db->escape($search, true) . '%');
            }
        }


        // Add the list ordering clause.
        $orderCol  = $this->state->get('list.ordering');
        $orderDirn = $this->state->get('list.direction');

        if ($orderCol && $orderDirn)
        {
            $query->order($db->escape($orderCol . ' ' . $orderDirn));
        }

        return $query;
    }

Answer 1

You can get the data directly from the request, setting the current year as the default value:

$app = JFactory::getApplication();
$year = (int) $app->input->getInt( 'yearInput' , date('Y') );
$query->where( 'a.yearColumn = ' . $year );

Or you can use the $this->state->getState(); method:

$year = $this->state->getState('filter.year');
$query->where( 'a.yearColumn = ' . $year );

How do you make sure filter.year is populated? If your model class extends JModelList then you can override populateState() to populate filter.year

protected function populateState($ordering = 'ordering', $direction = 'ASC')
{
    $app = JFactory::getApplication();

    $value = (int) $app->input->getInt('yearInput', date('Y'));
    $this->setState('filter.year', $value);

    // Set additional state variables or call parent::populateState($ordering, $direction)
}

populateState() will be called the first time you use $this->getState() so there is no need to call it explicitly.

Someone can correct me if I'm wrong but I believe that if you set the field name of your input field to filter.year then you wont even need to override populateState(), as it'll be populated automatically. In that case you can simply call $year = (int) $this->getState('filter.year', date('Y'));. You would need to make sure $this->context is truthy before the first $this->getState() is called (else no state will be populated except for list.start and list.limit and they'll both be set to 0).

For the internals of JModelList (aka ListModel) see https://github.com/joomla/joomla-cms/blob/staging/libraries/src/MVC/Model/ListModel.php