Simple sql builder for modx & minishop2

SimpleBuilder.php

<?php

namespace ExportPackage;

class SimpleBuilder
{
    const MS2_ALIAS_TABLE = 'ms2';
    const SITE_CONTENT_ALIAS_TABLE = 'resource';

    protected $ms2 = [];
    protected $resource = [];
    protected $tvs = [];
    protected $where = '';
    protected $limit = 0;
    protected $offset = 0;

    public function addResourceFields(...$fields): SimpleBuilder
    {
        $this->resource = array_merge($this->resource, $fields);
        return $this;
    }

    public function addMs2Fields(...$fields): SimpleBuilder
    {
        $this->ms2 = array_merge($this->ms2, $fields);
        return $this;
    }

    public function addTvFields(...$fields): SimpleBuilder
    {
        $this->tvs = array_merge($this->tvs, $fields);
        return $this;
    }

    public function where(string $column, string $operand, $value): SimpleBuilder
    {
        $column = $this->filterColumn($column);
        $value = $this->filterValue($value);

        if (!$this->where) {
            $this->where .= "{$column} {$operand} {$value} \n";
            return $this;
        }

        $this->where .= "AND {$column} {$operand} {$value} \n";
        return $this;
    }

    public function orWhere(string $column, string $operand, $value): SimpleBuilder
    {
        $column = $this->filterColumn($column);
        $value = $this->filterValue($value);

        if (!$this->where) {
            $this->where .= "{$column} {$operand} {$value} \n";
            return $this;
        }

        $this->where .= "OR {$column} {$operand} {$value} \n";
        return $this;
    }

    public function whereIn(string $column, $values): SimpleBuilder
    {
        $column = $this->filterColumn($column);

        $implode = implode(',', $values);

        if (!$this->where) {
            $this->where .= "{$column} IN ({$implode}) \n";
            return $this;
        }

        $this->where .= "AND {$column} IN ({$implode}) \n";
        return $this;
    }

    public function whereNotNull(string $column): SimpleBuilder
    {
        $column = $this->filterColumn($column);

        if (!$this->where) {
            $this->where .= "{$column} IS NOT NULL \n";
            return $this;
        }

        $this->where .= "AND {$column} IS NOT NULL \n";
        return $this;
    }
    public function whereNull(string $column): SimpleBuilder
    {
        $column = $this->filterColumn($column);

        if (!$this->where) {
            $this->where .= "{$column} IS NULL \n";
            return $this;
        }

        $this->where .= "AND {$column} IS NULL \n";
        return $this;
    }

    public function limit(int $limit): SimpleBuilder
    {
        $this->limit = $limit;
        return $this;
    }

    public function offset(int $offset): SimpleBuilder
    {
        $this->offset = $offset;
        return $this;
    }

    public function sql(): string
    {
        $sql = '';
        $contentAlias = static::SITE_CONTENT_ALIAS_TABLE;

        $select = $this->getSelectFields();

        $sql .= "SELECT $select FROM modx_site_content as {$contentAlias} \n";
        $sql .= $this->getLeftJoin();
        $sql .= $this->getWhere();
        $sql .= $this->limitAndOffset();

        return $sql;

    }

    protected function getSelectFields(): string
    {
        $contentAlias = static::SITE_CONTENT_ALIAS_TABLE;
        $ms2alias = static::MS2_ALIAS_TABLE;

        $out = '';
        if (count($this->resource)) {
            foreach ($this->resource as $item) {
                $item = $this->filterAddedColumns($item);
                $out .= "$contentAlias.$item,";
            }
        }

        if (count($this->ms2)) {
            foreach ($this->ms2 as $item) {
                $item = $this->filterAddedColumns($item);
                $out .= "$ms2alias.$item,";
            }
        }

        if (count($this->tvs)) {
            foreach ($this->tvs as $item) {
                $item = $this->filterAddedColumns($item);
                $out .= "{$item}_table.value as $item,";
            }
        }

        if (mb_substr($out, -1, 1) === ',') {
            $out = mb_substr($out, 0, -1);
        }

        return $out;
    }

    protected function getLeftJoin(): string
    {
        $out = '';
        $ms2alias = static::MS2_ALIAS_TABLE;
        $contentAlias = static::SITE_CONTENT_ALIAS_TABLE;

        if (count($this->ms2)) {
            $out .= "LEFT JOIN modx_ms2_products as {$ms2alias} on {$contentAlias}.id = ms2.id \n";
        }

        if (count($this->tvs)) {
            foreach ($this->tvs as $tv) {
                $originalName = $tv;
                $filteredName = $this->filterAddedColumns($tv);

                $out .= "LEFT JOIN modx_site_tmplvars as {$filteredName}_tv_name on {$filteredName}_tv_name.name = '{$originalName}' \n";
                $out .= "LEFT JOIN modx_site_tmplvar_contentvalues as {$filteredName}_table on {$contentAlias}.id = {$filteredName}_table.contentid and {$filteredName}_table.tmplvarid = {$filteredName}_tv_name.id \n";
            }
        }

        return $out;
    }

    protected function getWhere(): string
    {
        if (!$this->where) {
            return '';
        }

        return "WHERE $this->where";
    }

    protected function limitAndOffset(): string
    {
        $out = '';

        if ($this->limit) {
            $out .= "LIMIT {$this->limit} \n";
        }

        if ($this->offset) {
            $out .= "OFFSET {$this->offset} \n";
        }

        return $out;
    }

    protected function filterColumn(string $column): string
    {
        $column = $this->filterAddedColumns($column);

        $contentAlias = static::SITE_CONTENT_ALIAS_TABLE;
        $ms2alias = static::MS2_ALIAS_TABLE;

        foreach ($this->resource as $item) {
            $item = $this->filterAddedColumns($item);
            if ($item === $column) {
                return "$contentAlias.$column";
            }
        }

        foreach ($this->ms2 as $item) {
            $item = $this->filterAddedColumns($item);
            if ($item === $column) {
                return "$ms2alias.$column";
            }
        }

        foreach ($this->tvs as $item) {
            $item = $this->filterAddedColumns($item);
            if ($item === $column) {
                return "{$item}_table.value";
            }
        }

        return $column;
    }
    protected function filterValue($value)
    {
        if ($value === true) {
            $value = 1;
        }

        if ($value === false) {
            $value = 0;
        }

        if (is_null($value)) {
            return "null";
        }

        if (is_int($value)) {
            return $value;
        }

        return "'$value'";
    }

    protected function filterAddedColumns(string $column)
    {
        return str_replace(['-', ' '], '_', $column);
    }
}