Query class for wordpress projects to improve query performance in high demanding ACF/meta_fields queries

Query.php

<?php

class Query
{
    private $postType;
    private $perPage;
    private $page;
    private $status;
    private $where = '';
    private $whereArray = [];
    private $order = [];
    private $orderArray = [];

    private $metas = [];

    private $postIds = [];

    private $categories = [];

    /**
     * Query constructor.
     */
    public function __construct(string $postType = 'any', int $page = 1, int $perPage = 10, string $status = 'publish')
    {
        $this->postType = $postType;
        $this->perPage = $perPage;
        $this->page = $page;
        $this->status = $status;
    }

    public function setWhere(array $where)
    {
        $this->whereArray = $where;

        return $this;
    }

    public function setOrder(array $order)
    {
        $this->orderArray = $order;

        return $this;
    }

    public function setCategories($categories)
    {
        $this->categories = $categories;

        return $this;
    }

    public function getWhere()
    {
        return $this->where;
    }

    public function getOrder()
    {
        return $this->order;
    }

    public function getQueryCategories()
    {
        return array_filter(array_map(function ($slugs, $category) {
            return $slugs ? [
                'taxonomy' => $category,
                'field' => 'slug',
                'terms' => $slugs,
            ] : null;
        }, $this->categories, array_keys($this->categories)));
    }

    public function processOrder(array $order)
    {
        $orderElements = array_reduce($order, function ($carry, $elem) {
            $this->addMetaToJoin($elem[0]);

            return $carry." {$elem[0]}.meta_value {$elem[1]} ,";
        }, '');

        if (!$orderElements) {
            return '';
        }

        $orderString = 'ORDER BY '.trim($orderElements, ',');

        $this->order = $orderString;

        return $orderString;
    }

    public function addMetaToJoin(string $meta)
    {
        !in_array($meta, $this->metas) ? array_push($this->metas, $meta) : null;
    }

    public function processWhereGroups(array $where)
    {
        $this->addMetaToJoin($where['key']);

        return $where['key'].'.meta_value '.$where['compare'].' \''.$where['value'].'\'';
    }

    public function processWhere(array $where)
    {
        $whereResult = '';
        $relation = array_key_exists('relation', $where) && $where['relation'] ? $where['relation'] : 'AND';

        if (array_key_exists('items', $where)) {
            $groupResult = '';
            $cleanedItems = array_filter($where['items']);
            foreach ($cleanedItems as $k => $item) {
                $isLast = $k === count($cleanedItems) - 1;
                $groupResult .= '('.$this->processWhere($item).') '.(!$isLast ? $relation : '').' ';
            }
            $whereResult .= $groupResult;
        } else {
            $whereResult .= $this->processWhereGroups($where);
        }

        $this->where = $whereResult;

        return $whereResult;
    }

    private function getJoinForMeta($meta)
    {
        global $wpdb;

        return "LEFT JOIN {$wpdb->prefix}postmeta as {$meta} ON (wp.id = {$meta}.post_id AND {$meta}.meta_key = '{$meta}')";
    }

    private function getValues(bool $getValues)
    {
        if (!$getValues) {
            return '';
        }
        global $wpdb;

        return array_reduce($this->metas, function ($carry, $meta) use ($wpdb) {
            return $carry.", {$meta}.meta_value as {$meta}_value";
        }, '');
    }

    public function getJoins()
    {
        return array_reduce($this->metas, function ($carry, $meta) {
            return $carry.$this->getJoinForMeta($meta).' ';
        }, '');
    }

    public function getPostIds(bool $getValues = false)
    {
        global $wpdb;
        global $sitepress;

        // The acf fields are only on Euskera
        $lang = 'eu'; //$sitepress->get_current_language();

        $this->processWhere($this->whereArray);
        $this->processOrder($this->orderArray);

        $sql = <<<QUERY
SELECT wp.id
{$this->getValues($getValues)}
FROM {$wpdb->prefix}posts as wp
{$this->getJoins()}
LEFT JOIN {$wpdb->prefix}icl_translations as i18n ON wp.id = i18n.element_id
WHERE wp.post_type = '{$this->postType}'
AND i18n.language_code = '{$lang}'
AND
(
  {$this->getWhere()}
)
{$this->getOrder()}
QUERY;

        //AND wps_end.meta_value >= {$dateNum};
        $this->postIds = $wpdb->get_results($sql, ARRAY_N);

        return $this->postIds;
    }

    public function extractIds(array $ids)
    {
        return array_map(function ($id) {
            return (int) $id[0];
        }, $ids);
    }

    public function getTranslatedIds()
    {
        global $sitepress;

        $queryIds = $this->getPostIds();
        $elemIds = $this->extractIds($queryIds);

        return array_values(array_filter(array_map(function ($id) use ($sitepress) {
            return apply_filters('wpml_object_id', $id, $this->postType, false, $sitepress->get_current_language());
        }, $elemIds)));
    }

    public function getWpQuery()
    {
        return [
            'post_type' => $this->postType,
            'posts_per_page' => $this->perPage,
            'paged' => $this->page,
            'post_status' => $this->status,
            'orderby' => 'post__in',
            'post__in' => $this->getTranslatedIds(),
            'suppress_filters' => false,
            'tax_query' => $this->getQueryCategories(),
        ];
    }

    public function getPosts()
    {
        return get_posts($this->getWpQuery());
    }
}

Usage

$query = new Query('post_type', $page);

$query->setWhere([
    'relation' => 'OR',
    'items'    => [
        [
            'relation' => 'AND',
            'items'    => [
                [
                    'key'     => 'field_1',
                    'compare' => '=',
                    'value'   => 'false',
                ],
                $foo ? [
                    'key'     => 'field_2',
                    'value'   => $foo,
                    'compare' => '>=',
                ] : null,
            ],
        ],
        [
            'relation' => 'OR',
            'items'    => [
                [
                    'relation' => 'AND',
                    'items'    => [
                        [
                            'key'     => 'field_1',
                            'compare' => '=',
                            'value'   => 'true',
                        ],
                        $foo ? [
                            'key'     => 'field_2',
                            'value'   => $foo,
                            'compare' => '>=',
                        ] : null,
                        $bar ? [
                            'key'     => 'field_3',
                            'value'   => $bar,
                            'compare' => '<=',
                        ] : null,
                    ],
                ],
                $foo && $bar ? [
                    'relation' => 'AND',
                    'items'    => [
                        [
                            'key'     => 'field_1',
                            'compare' => '=',
                            'value'   => 'true',
                        ],
                        [
                            'key'     => 'field_2',
                            'value'   => $foo,
                            'compare' => '<=',
                        ],
                        [
                            'key'     => 'field_3',
                            'value'   => $bar,
                            'compare' => '>=',
                        ],
                    ],
                ] : null,
            ],
        ],
    ],
]);