Filter by categories and category title in the list of items

[zeus07]

Hi!
I creating my custom component for Joomla 5. This is like a list of workers with different fields + I connected for this component native Joomla categories.

Categories working (I mean creating, edit, delete), but I can't understand how to create filter by categories in the list of workers and displaying the category titles in the table of list.

Below you can see code for file WorkersModel.php:

Code: Select all

<?php

namespace VPJoomla\Component\Workers\Administrator\Model;

use Joomla\CMS\Factory;
use Joomla\CMS\MVC\Model\ListModel;
use Joomla\CMS\Table\Table;
use Joomla\Database\ParameterType;
use Joomla\Utilities\ArrayHelper;

defined('_JEXEC') or exit();

class WorkersModel extends ListModel {

	/**
	 * Конструктор.
	 * @param   array  $config  Массив с конфигурационными параметрами.
	 */
	public function __construct($config = []){
		// Добавляем валидные поля для фильтров и сортировки.
		if (empty($config['filter_fields'])) {
			$config['filter_fields'] = array(
            	'id', 'a.id',
            	'name', 'a.name',
				'catid', 'a.catid', 'category_title',
				'alias', 'a.alias',
				'country', 'a.country',
				'city', 'a.city',
				'sex', 'a.sex',
				'availability', 'a.availability',
            	'ordering', 'a.ordering',
            	'state', 'a.state',
            	'created', 'a.created',
            	'modified', 'a.modified'
        	);
		}

		parent::__construct($config);
	}


	/**
     * Method to get a list of items.
     *
     * @return  mixed   An array of data items on success, false on failure.
     */
    public function getItems()
    {
        $items = parent::getItems();
 
        return $items;
    }




	/**
	 * Метод для построения SQL запроса для загрузки списка данных.
	 * @return  string  SQL запрос.
	 */
	protected function getListQuery(): string
	{

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

		$query->select(
            $this->getState(
                'list.select',
                [
                    $db->quoteName('a.id'),
                    $db->quoteName('a.name'),
	                $db->quoteName('a.catid'),
	                $db->quoteName('a.alias'),
	                $db->quoteName('a.country'),
	                $db->quoteName('a.city'),
	                $db->quoteName('a.sex'),
	                $db->quoteName('a.availability'),
                    $db->quoteName('a.state'),
                    $db->quoteName('a.created'),
                    $db->quoteName('a.modified'),
					$db->quoteName('a.ordering'),
				]
            )
        )
			->select($db->quoteName('c.title', 'category_title'))
			->from($db->quoteName('#__workers', 'a'))
			->where($db->quoteName('wa.extension') . ' = ' . $db->quote('com_workers.worker'))
			->join('LEFT', $db->quoteName('#__categories', 'c'), $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid'));



        // Фильтр по состоянию полученному из запроса
        $published = (string) $this->getState('filter.published');

        if (is_numeric($published))
        {
            $query->where($db->quoteName('a.state') . ' = '.$published);
        }
        elseif ($published === '')
        {
            $query->where('(' . $db->quoteName('a.state') . ' = 0 OR ' . $db->quoteName('a.state') . ' = 1)');
        }

		// Фильтр по категории
		$categoryId = $this->getState('filter.category_id', []);
		if (!\is_array($categoryId)) {
			$categoryId = $categoryId ? [$categoryId] : [];
		}

		if (\count($categoryId)) {
			$categoryId       = ArrayHelper::toInteger($categoryId);
			$categoryTable    = Table::getInstance('Category', '\\Joomla\\CMS\\Table\\');
			$subCatItemsWhere = [];

			foreach ($categoryId as $key => $filter_catid) {
				$categoryTable->load($filter_catid);

				// Because values to $query->bind() are passed by reference, using $query->bindArray() here instead to prevent overwriting.
				$valuesToBind = [$categoryTable->lft, $categoryTable->rgt];

				// Bind values and get parameter names.
				$bounded = $query->bindArray($valuesToBind);

				$categoryWhere = $db->quoteName('c.lft') . ' >= ' . $bounded[0] . ' AND ' . $db->quoteName('c.rgt') . ' <= ' . $bounded[1];

				$subCatItemsWhere[] = '(' . $categoryWhere . ')';
			}

			$query->where('(' . implode(' OR ', $subCatItemsWhere) . ')');
		}

		//Фильтр поиска по стране
		$country = $this->getState('filter.country');
		if (!empty($country)) {
			$query->where($db->quoteName('a.country') . ' = ' . $db->quote($country));
		}

		//Фильтр поиска по полу
		$sex = (int) $this->getState('filter.sex');
		if ($sex) {
			$query->where($db->quoteName('a.sex') . ' = ' . $db->quote($sex));
		}

		//Фильтр поиска по доступности
		$availability = (string) $this->getState('filter.availability');
		if (\in_array($availability, ['0','1'])) {
			$availability = (int) $availability;
			$query->where($db->quoteName('a.availability') . ' = :availability')
				->bind(':availability', $featured, ParameterType::INTEGER);
		}
 
        // Фильтр поиска по названию.
        $search = $this->getState('filter.search');
 
        if (!empty($search))
        {
            $search = $db->quote('%' . str_replace(' ', '%', $db->escape(trim($search), true) . '%'));
            $query->where('(a.name LIKE ' . $search . ')');
        }


		// Добавляем сортировку.
		$orderCol  = $this->state->get('list.ordering', 'id');
		$orderDirn = $this->state->get('list.direction', 'desc');
		$query->order($db->escape($orderCol . ' ' . $orderDirn));



		return $query;
	}

	protected function populateState($ordering = 'a.id', $direction = 'desc')
{
    $app = Factory::getApplication();
    $input = $app->input;

    $search = $this->getUserStateFromRequest($this->context . '.filter.search', 'filter_search');
    $this->setState('filter.search', $search);

    $published = $this->getUserStateFromRequest($this->context . '.filter.published', 'filter_published', '');
    $this->setState('filter.published', $published);

	$country = $this->getUserStateFromRequest($this->context . '.filter.country', 'filter_country', '');
	$this->setState('filter.country', $country);

	$sex = $this->getUserStateFromRequest($this->context . '.filter.sex', 'filter_sex', '');
	$this->setState('filter.sex', $sex);

	$availability = $this->getUserStateFromRequest($this->context . '.filter.availability', 'filter_availability', '');
	$this->setState('filter.availability', $availability);

    // Set the default ordering and direction if not already set
    parent::populateState($ordering, $direction);
}

protected function getStoreId($id = ''): string
{
	// Compile the store id.
	$id .= ':' . $this->getState('filter.search');
	$id .= ':' . serialize($this->getState('filter.category_id'));
	$id .= ':' . $this->getState('filter.country');
	$id .= ':' . $this->getState('filter.sex');
	$id .= ':' . $this->getState('filter.availability');

	return parent::getStoreId($id);
}


	/* Prepare a faqcontentiten record for saving in the database */
	protected function prepareTable($table): void
    {
	// Set ordering to the last item if not set
		if (empty($table->ordering))
		{
			$db = $this->getDatabase();
			$query = $db->getQuery(true)
				->select('MAX(ordering)')
				->from('#__workers');

			$db->setQuery($query);
			$max = $db->loadResult();

			$table->ordering = $max + 1;
		}
	}

    /**
     * Method to change the published state of one or more records.
     *
     * @param array    &$pks   A list of the primary keys to change.
     * @param integer $value  The value of the published state.
     *
     * @return  boolean  True on success.
     *
     * @since   4.0.0
     */
    public function publish(array &$pks, int $value = 1) {
        if (empty($pks)) {
            return false; // No records to update
        }

        $db = $this->getDatabase();

        // Sanitize the value
        $value = (int) $value;

        try {
            $query = $db->getQuery(true)
                ->update($db->quoteName('#__workers'))
                ->set($db->quoteName('state') . ' = ' . $value)
                ->whereIn($db->quoteName('id'), $pks);

            $db->setQuery($query);
            $db->execute();

            return true;
        } catch (\Exception $e) {
            return false;
        }
    }
    /**
     * Method to change the published state of one or more records to unpublished.
     *
     * @param array &$pks A list of the primary keys to change.
     *
     * @return  boolean  True on success.
     */
    public function unpublish(array &$pks) {
        return $this->publish($pks, 0);
    }

    /**
     * Method to change the published state of one or more records to archived.
     *
     * @param array &$pks A list of the primary keys to change.
     *
     * @return  boolean  True on success.
     */
    public function archive(array &$pks) {
        return $this->publish($pks, 2);
    }

    /**
     * Method to change the published state of one or more records to trashed.
     *
     * @param array &$pks A list of the primary keys to change.
     *
     * @return  boolean  True on success.
     */
    public function trash(array &$pks) {
        return $this->publish($pks, -2);
    }

}

This is for code of file filter_workers.xml:

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<form>
    <fields name="filter">
        <field
                name="search"
                type="text"
                hint="JSEARCH_FILTER"
        />

        <field
                name="published"
                type="status"
                label="JOPTION_SELECT_PUBLISHED"
                class="js-select-submit-on-change"
                extension="com_workers"
        >
            <option value="">JOPTION_SELECT_PUBLISHED</option>
        </field>
        <field
                name="category_id"
                type="category"
                label="JCATEGORY"
                multiple="true"
                extension="com_workers"
                layout="joomla.form.field.list-fancy-select"
                hint="JOPTION_SELECT_CATEGORY"
                class="js-select-submit-on-change"
                published="0,1,2"
        />
        <field
                name="country"
                type="country"
                label="COM_WORKERS_WORKERS_SELECT_COUNTRY"
                class="js-select-submit-on-change"
        >
            <option value="">COM_WORKERS_WORKERS_SELECT_COUNTRY</option>
        </field>
        <field
                name="sex"
                type="sex"
                label="COM_WORKERS_WORKERS_SELECT_SEX"
                class="js-select-submit-on-change"
        >
            <option value="">COM_WORKERS_WORKERS_SELECT_SEX</option>
        </field>
        <field
                name="availability"
                type="availability"
                label="COM_WORKERS_WORKERS_SELECT_AVAILABILITY"
                class="js-select-submit-on-change"
        >
            <option value="">COM_WORKERS_WORKERS_SELECT_AVAILABILITY</option>
        </field>

    </fields>

    <fields name="list">
        <field name="fullordering"
               type="list"
               label="JGLOBAL_SORT_BY"
               class="js-select-submit-on-change"
               default="a.published DESC"
               validate="options"
        >
            <option value="">JGLOBAL_SORT_BY</option>
            <option value="a.ordering ASC">JGRID_HEADING_ORDERING_ASC</option>
            <option value="a.ordering DESC">JGRID_HEADING_ORDERING_DESC</option>
            <option value="category_title ASC">JCATEGORY_ASC</option>
            <option value="category_title DESC">JCATEGORY_DESC</option>
            <option value="a.state ASC">JSTATUS_ASC</option>
            <option value="a.state DESC">JSTATUS_DESC</option>
            <option value="a.created ASC">JDATE_ASC</option>
            <option value="a.created DESC">JDATE_DESC</option>
            <option value="a.modified ASC">COM_WORKERS_MODIFIED_ASC</option>
            <option value="a.modified DESC">COM_WORKERS_MODIFIED_DESC</option>
            <option value="a.name ASC">COM_WORKERS_NAME_ASC</option>
            <option value="a.name DESC">COM_WORKERS_NAME_DESC</option>
            <option value="a.id ASC">JGRID_HEADING_ID_ASC</option>
            <option value="a.id DESC">JGRID_HEADING_ID_DESC</option>
        </field>
        <field
                name="limit"
                type="limitbox"
                label="JGLOBAL_LIST_LIMIT"
                default="25"
                class="js-select-submit-on-change"
        />
    </fields>
</form>

Help please where I did mistake? I thin in DB query, because now in the admi panel I can't see my list of items. But I have 7 items.

Снимок экрана 2024-04-30 в 11.39.16.png

Thanks for help!

[zeus07]

I fixed my filter by category, but have some another issue.
At first, latest code for file WorkersModel.php:

Code: Select all

<?php

namespace VPJoomla\Component\Workers\Administrator\Model;

use Joomla\CMS\Factory;
use Joomla\CMS\MVC\Model\ListModel;
use Joomla\CMS\Table\Table;
use Joomla\Database\ParameterType;
use Joomla\Utilities\ArrayHelper;

defined('_JEXEC') or exit();

class WorkersModel extends ListModel {

	/**
	 * Конструктор.
	 * @param   array  $config  Массив с конфигурационными параметрами.
	 */
	public function __construct($config = []){
		// Добавляем валидные поля для фильтров и сортировки.
		if (empty($config['filter_fields'])) {
			$config['filter_fields'] = array(
            	'id', 'a.id',
            	'name', 'a.name',
				'catid', 'a.catid', 'category_title',
				'alias', 'a.alias',
				'country', 'a.country',
				'city', 'a.city',
				'sex', 'a.sex',
				'availability', 'a.availability',
            	'ordering', 'a.ordering',
            	'state', 'a.state',
            	'created', 'a.created',
            	'modified', 'a.modified'
        	);
		}

		parent::__construct($config);
	}


	/**
     * Method to get a list of items.
     *
     * @return  mixed   An array of data items on success, false on failure.
     */
    public function getItems()
    {
        $items = parent::getItems();
 
        return $items;
    }




	/**
	 * Метод для построения SQL запроса для загрузки списка данных.
	 * @return  string  SQL запрос.
	 */
	protected function getListQuery(): string
	{

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

		$query->select(
			$this->getState(
				'list.select',
				[
					$db->quoteName('a.id'),
					$db->quoteName('a.name'),
					$db->quoteName('a.catid'),
					$db->quoteName('a.alias'),
					$db->quoteName('a.country'),
					$db->quoteName('a.city'),
					$db->quoteName('a.sex'),
					$db->quoteName('a.availability'),
					$db->quoteName('a.state'),
					$db->quoteName('a.created'),
					$db->quoteName('a.modified'),
					$db->quoteName('a.ordering'),
				]
			)
		)
			->select($db->quoteName('c.title', 'category_title'))
			->from($db->quoteName('#__workers', 'a'))
			->join('LEFT', $db->quoteName('#__categories', 'c'), $db->quoteName('c.id') . ' = ' . $db->quoteName('a.catid'));



        // Фильтр по состоянию полученному из запроса
        $published = (string) $this->getState('filter.published');

        if (is_numeric($published))
        {
            $query->where($db->quoteName('a.state') . ' = '.$published);
        }
        elseif ($published === '')
        {
            $query->where('(' . $db->quoteName('a.state') . ' = 0 OR ' . $db->quoteName('a.state') . ' = 1)');
        }

		// Фильтр по категории
		$categoryId = $this->getState('filter.category_id');
		if (!empty($categoryId)) {
			// Check if $categoryId is an array
			if (is_array($categoryId)) {
				// Convert array to string
				$categoryId = implode(',', $categoryId);
			}

			// Log the category ID being passed to the method
			error_log('Category ID: ' . $categoryId);

			$query->where($db->quoteName('a.catid') . ' IN (' . $categoryId . ')');

			// Log the SQL query
			error_log('SQL query: ' . $query);
		}

		// Filter by country
		$country = $this->getState('filter.country');
		if (!empty($country)) {
			$query->where($db->quoteName('a.country') . ' = ' . $db->quote($country));
		}

		//Фильтр поиска по полу
		$sex = (int) $this->getState('filter.sex');
		if ($sex) {
			$query->where($db->quoteName('a.sex') . ' = ' . $db->quote($sex));
		}

		//Фильтр поиска по доступности
		$availability = (string) $this->getState('filter.availability');
		if (\in_array($availability, ['0','1'])) {
			$availability = (int) $availability;
			$query->where($db->quoteName('a.availability') . ' = :availability')
				->bind(':availability', $featured, ParameterType::INTEGER);
		}
 
        // Фильтр поиска по названию.
        $search = $this->getState('filter.search');
 
        if (!empty($search))
        {
            $search = $db->quote('%' . str_replace(' ', '%', $db->escape(trim($search), true) . '%'));
            $query->where('(a.name LIKE ' . $search . ')');
        }


		// Добавляем сортировку.
		$orderCol  = $this->state->get('list.ordering', 'id');
		$orderDirn = $this->state->get('list.direction', 'desc');
		$query->order($db->escape($orderCol . ' ' . $orderDirn));



		return $query;
	}

	protected function populateState($ordering = 'a.id', $direction = 'desc')
{
    $app = Factory::getApplication();
    $input = $app->input;

    $search = $this->getUserStateFromRequest($this->context . '.filter.search', 'filter_search');
    $this->setState('filter.search', $search);

    $published = $this->getUserStateFromRequest($this->context . '.filter.published', 'filter_published', '');
    $this->setState('filter.published', $published);

	$country = $this->getUserStateFromRequest($this->context . '.filter.country', 'filter_country', '');
	$this->setState('filter.country', $country);

	$sex = $this->getUserStateFromRequest($this->context . '.filter.sex', 'filter_sex', '');
	$this->setState('filter.sex', $sex);

	$availability = $this->getUserStateFromRequest($this->context . '.filter.availability', 'filter_availability', '');
	$this->setState('filter.availability', $availability);

    // Set the default ordering and direction if not already set
    parent::populateState($ordering, $direction);
}

protected function getStoreId($id = ''): string
{
	// Compile the store id.
	$id .= ':' . $this->getState('filter.search');
	$id .= ':' . serialize($this->getState('filter.category_id'));
	$id .= ':' . $this->getState('filter.country');
	$id .= ':' . $this->getState('filter.sex');
	$id .= ':' . $this->getState('filter.availability');

	return parent::getStoreId($id);
}


	/* Prepare a faqcontentiten record for saving in the database */
	protected function prepareTable($table): void
    {
	// Set ordering to the last item if not set
		if (empty($table->ordering))
		{
			$db = $this->getDatabase();
			$query = $db->getQuery(true)
				->select('MAX(ordering)')
				->from('#__workers');

			$db->setQuery($query);
			$max = $db->loadResult();

			$table->ordering = $max + 1;
		}
	}

    /**
     * Method to change the published state of one or more records.
     *
     * @param array    &$pks   A list of the primary keys to change.
     * @param integer $value  The value of the published state.
     *
     * @return  boolean  True on success.
     *
     * @since   4.0.0
     */
    public function publish(array &$pks, int $value = 1) {
        if (empty($pks)) {
            return false; // No records to update
        }

        $db = $this->getDatabase();

        // Sanitize the value
        $value = (int) $value;

        try {
            $query = $db->getQuery(true)
                ->update($db->quoteName('#__workers'))
                ->set($db->quoteName('state') . ' = ' . $value)
                ->whereIn($db->quoteName('id'), $pks);

            $db->setQuery($query);
            $db->execute();

            return true;
        } catch (\Exception $e) {
            return false;
        }
    }
    /**
     * Method to change the published state of one or more records to unpublished.
     *
     * @param array &$pks A list of the primary keys to change.
     *
     * @return  boolean  True on success.
     */
    public function unpublish(array &$pks) {
        return $this->publish($pks, 0);
    }

    /**
     * Method to change the published state of one or more records to archived.
     *
     * @param array &$pks A list of the primary keys to change.
     *
     * @return  boolean  True on success.
     */
    public function archive(array &$pks) {
        return $this->publish($pks, 2);
    }

    /**
     * Method to change the published state of one or more records to trashed.
     *
     * @param array &$pks A list of the primary keys to change.
     *
     * @return  boolean  True on success.
     */
    public function trash(array &$pks) {
        return $this->publish($pks, -2);
    }

}

After each filter the panel with filters just hiding, but at the clean Joomla 5.1 there is no. Why? Some JS error maybe? But in my component no one JS file.

[MarkRS]

You need to include the possible filters in your config array built in the __construct method.

So your published filter needs the entry 'published' added to that array.

[zeus07]

You need to include the possible filters in your config array built in the __construct method.

So your published filter needs the entry 'published' added to that array.

Yes, right. But I have filters in this array. Do you meant this? Or something else?

Code: Select all

public function __construct($config = []){
		// Добавляем валидные поля для фильтров и сортировки.
		if (empty($config['filter_fields'])) {
			$config['filter_fields'] = array(
            	'id', 'a.id',
            	'name', 'a.name',
				'catid', 'a.catid', 'category_title',
				'alias', 'a.alias',
				'country', 'a.country',
				'city', 'a.city',
				'sex', 'a.sex',
				'availability', 'a.availability',
            	'ordering', 'a.ordering',
            	'state', 'a.state',
            	'created', 'a.created',
            	'modified', 'a.modified'
        	);
		}

		parent::__construct($config);
	}

[MarkRS]

Yes, right. But I have filters in this array. Do you meant this? Or something else?

I may have misunderstood you, but I thought you are saying the problem is that the filter bar disappears so you can't see what your list is filtered by. My solution is for that problem.

I had a quick look at your getListQuery (you don't need to write the getItems() if it's just calling the parent) and the first filter it shows is for "published". You don't have that in the config array. I'm guessing that you've got the ordering fields only. If you include all the filters as well, just the name of the filter not the field(s) it's working with, then the filter bar should remain visible after the filtered list is displayed.

I've also got code for this in my htmlview, putting the active filter fields in the title bar along with the number of rows displayed, but perhaps that's unnecessary when the filter bar remains visible.