ElementIndexForRecords.php

<?php
/**
 * How to create an ElementIndex via custom Record for Craft CMS 3.x
 *
 * @copyright Copyright (c) 2018 anubarak
 */

namespace modules\mymodule\elements\db;
use craft\base\Element;
use craft\db\Query;
use craft\db\QueryAbortedException;
use craft\elements\db\ElementQuery;
use craft\helpers\StringHelper;

class CustomElementQuery extends ElementQuery
{

    // some public properties
    // ===================================================
    /**
     * a public property
     *
     * @var string $myVariable
     */
    public $myVariable;


    /**
     * @var array $orderBy
     */
    public $orderBy = ['date' => SORT_DESC];

    /**
     * Custom search for an attribute
     *
     * @param $value
     * @return $this
     */
    public function myVariable($value)
    {
        $this->myVariable = $value;

        return $this;
    }


    /**
     * This method builds usually the entire query and returns the ElementQuery
     * instead of building an ElementQuery we just use the normal records
     *
     * @param $builder
     *
     * @return $this|\craft\db\Query|null
     * @throws \craft\db\QueryAbortedException
     */
    public function prepare($builder)
    {
        // elementType is passed from your main Element Class via `Element::find()`
        $class = $this->elementType;
        // create a normal Query for your Records
        $this->query = (new Query())->from(['mytablename' => '{{%mytablename}}']);

        // select whatever you need... 
        $this->query->select(
            [
                'mytablename.id',
                'mytablename.type',
                'mytablename.name',
                'mytablename.enabled',
                'mytablename.siteId',
                'mytablename.dateUpdated',
                'mytablename.dateCreated',
            ]
        );

        // include some custom conditions if you like
        $this->query
            ->andWhere(['=', 'mytablename.siteId', $this->siteId])
            ->offset($this->offset)
            ->limit($this->limit);

        // if a search parameter is given we need to add filters...
        // keep in mind we can't rely on Crafts search because we don't have an element :)
        if ($this->search) {
            $this->query->filterWhere(['like', 'mytablename.headline', $this->search]);
            $this->query->orFilterWhere(['like', 'mytablename.name', $this->search]);
            $this->query->orFilterWhere(['like', 'mytablename.text', $this->search]);
            $this->query->orFilterWhere(['like', 'mytablename.id', $this->search]);
            $this->query->orFilterWhere(['like', 'mytablename.typeId', $this->search]);
        }

        // if there are any other custom conditions wrap them propery
        if($this->myVariable){
            $this->query->andWhere(['=', 'mytablename.myVariable', $this->myVariable]);
        }

        // include an order
        if ($this->orderBy !== null && empty($this->orderBy)) {
            $this->orderBy = 'dateCreated desc';
        }

        if (!empty($this->orderBy)) {
            $this->query->orderBy($this->orderBy);
        }
        
        // in case you have any statuses include them as well
        $this->_applyStatusParam($class);

        // return your custom query
        return $this->query;
    }

    protected function statusCondition(string $status)
    {
        switch ($status) {
            case Element::STATUS_ENABLED:
                return ['=', 'mytablename.enabled', 1];
                break;
            case Element::STATUS_DISABLED:
                return ['=', 'mytablename.enabled', 0];
                break;
        }
    }

    /**
     * @param string $class
     *
     * @throws \craft\db\QueryAbortedException
     */
    private function _applyStatusParam(string $class)
    {
        /** @var string|ElementInterface $class */
        if (!$this->status || !$class::hasStatuses()) {
            return;
        }

        $statuses = $this->status;
        if (!is_array($statuses)) {
            $statuses = is_string($statuses) ? StringHelper::split($statuses) : [$statuses];
        }

        $condition = ['or'];

        foreach ($statuses as $status) {
            $status = StringHelper::toLowerCase($status);
            $statusCondition = $this->statusCondition($status);

            if ($statusCondition === false) {
                throw new QueryAbortedException('Unsupported status: '.$status);
            }

            if ($statusCondition !== null) {
                $condition[] = $statusCondition;
            }
        }

        $this->query->andWhere($condition);
    }
}