Skip to content

Instantly share code, notes, and snippets.

@pavel-one
Last active February 26, 2022 12:48
Show Gist options
  • Save pavel-one/75c0c0c966e78b6f209868c238e41abc to your computer and use it in GitHub Desktop.
Save pavel-one/75c0c0c966e78b6f209868c238e41abc to your computer and use it in GitHub Desktop.
Simple sql builder for modx & minishop2
<?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);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment