protected function where_sql(\ebi\Dao $dao, &$from, \ebi\Q $q, array $self_columns, $require_where = null, $alias = true)
{
if ($q->is_block()) {
$vars = $and_block_sql = $or_block_sql = [];
$where_sql = '';
foreach ($q->ar_and_block() as $qa) {
list($where, $var) = $this->where_sql($dao, $from, $qa, $self_columns, null, $alias);
if (!empty($where)) {
$and_block_sql[] = $where;
$vars = array_merge($vars, $var);
}
}
if (!empty($and_block_sql)) {
$where_sql .= ' (' . implode(' and ', $and_block_sql) . ') ';
}
foreach ($q->ar_or_block() as $or_block) {
list($where, $var) = $this->where_sql($dao, $from, $or_block, $self_columns, null, $alias);
if (!empty($where)) {
$or_block_sql[] = $where;
$vars = array_merge($vars, $var);
}
}
if (!empty($or_block_sql)) {
$where_sql .= (empty($where_sql) ? '' : ' and ') . ' (' . implode(' or ', $or_block_sql) . ') ';
}
if (empty($where_sql)) {
$where_sql = $require_where;
} else {
if (!empty($require_where)) {
$where_sql = '(' . $require_where . ') and (' . $where_sql . ')';
}
}
return [$where_sql, $vars];
}
if ($q->type() == Q::MATCH && sizeof($q->ar_arg1()) > 0) {
$query = new \ebi\Q();
$target = $q->ar_arg2();
$ob = $columns = [];
foreach ($self_columns as $column) {
if (empty($target) || in_array($column->name(), $target)) {
$columns[$column->name()] = $dao->prop_anon($column->name(), 'type');
}
}
foreach ($columns as $cn => $ct) {
$and = [];
foreach ($q->ar_arg1() as $cond) {
$op = null;
if (substr($cond, 0, 1) == '-') {
$cond = substr($cond, 1);
$op = Q::NOT;
}
switch ($ct) {
case 'number':
case 'serial':
case 'integer':
case 'timestamp':
case 'date':
case 'time':
case 'intdate':
$and[] = Q::eq($cn, $cond, $op);
break;
case 'string':
case 'email':
case 'alnum':
$and[] = Q::contains($cn, $cond, $op);
break;
case 'boolean':
case 'mixed':
default:
}
}
if (!empty($and)) {
$ob[] = call_user_func_array(['\\ebi\\Q', 'b'], $and);
}
}
$query->add(call_user_func_array(['\\ebi\\Q', 'ob'], $ob));
return $this->where_sql($dao, $from, $query, $self_columns, null, $alias);
}
$and = $vars = [];
foreach ($q->ar_arg2() as $base_value) {
$or = [];
foreach ($q->ar_arg1() as $column_str) {
$value = $base_value;
$column = $this->get_column($column_str, $self_columns);
$column_alias = $this->column_alias_sql($column, $q, $alias);
$is_add_value = true;
switch ($q->type()) {
case Q::EQ:
if ($value === null) {
$is_add_value = false;
$column_alias .= ' is null';
break;
}
$column_alias .= ' = ' . ($value instanceof \ebi\Daq ? '(' . $value->unique_sql() . ')' : '?');
break;
case Q::NEQ:
if ($value === null) {
$is_add_value = false;
$column_alias .= ' is not null';
break;
}
$column_alias .= ' <> ' . ($value instanceof \ebi\Daq ? '(' . $value->unique_sql() . ')' : '?');
break;
case Q::GT:
$column_alias .= ' > ' . ($value instanceof \ebi\Daq ? '(' . $value->unique_sql() . ')' : '?');
break;
case Q::GTE:
$column_alias .= ' >= ' . ($value instanceof \ebi\Daq ? '(' . $value->unique_sql() . ')' : '?');
break;
case Q::LT:
$column_alias .= ' < ' . ($value instanceof \ebi\Daq ? '(' . $value->unique_sql() . ')' : '?');
break;
case Q::LTE:
$column_alias .= ' <= ' . ($value instanceof \ebi\Daq ? '(' . $value->unique_sql() . ')' : '?');
break;
case Q::CONTAINS:
case Q::START_WITH:
case Q::END_WITH:
$column_alias = $this->format_column_alias_sql($column, $q, $alias);
$column_alias .= ($q->not() ? ' not' : '') . ' like(?)';
$value = ($q->type() == Q::CONTAINS || $q->type() == Q::END_WITH ? '%' : '') . $value . ($q->type() == Q::CONTAINS || $q->type() == Q::START_WITH ? '%' : '');
break;
case Q::IN:
$column_alias .= ($q->not() ? ' not' : '') . ($value instanceof \ebi\Daq ? ' in(' . $value->unique_sql() . ')' : ' in(' . substr(str_repeat('?,', sizeof($value)), 0, -1) . ')');
break;
case Q::DISCORD:
$and[] = '1 = 2';
$is_add_value = false;
break;
}
if ($value instanceof \ebi\Daq) {
$is_add_value = false;
$vars = array_merge($vars, $value->ar_vars());
}
$add_join_conds = $dao->join_conds($column->name());
if (!empty($add_join_conds)) {
$column_alias .= ' and ' . $this->where_cond_columns($add_join_conds, $from);
}
$or[] = $column_alias;
if ($is_add_value) {
if (is_array($value)) {
$values = [];
foreach ($value as $v) {
$values[] = $q->ignore_case() ? strtoupper($this->column_value($dao, $column->name(), $v)) : $this->column_value($dao, $column->name(), $v);
}
$vars = array_merge($vars, $values);
} else {
$vars[] = $q->ignore_case() ? strtoupper($this->column_value($dao, $column->name(), $value)) : $this->column_value($dao, $column->name(), $value);
}
}
}
$and[] = ' (' . implode(' or ', $or) . ') ';
}
return [implode(' and ', $and), $vars];
}