2x Feature request

[mrdamien]

Allow method chaining with column()

Example:

$fp = fopen('php://input', 'r');
$update->table('foo')
    ->where('id =', 4)
    ->column('bar', $fp, \PDO::PARAM_LOB)
    ->columns([
        'fizz' => 'buzz'
    ])
    ->perform();

// src/Clause/ModifyColumns.php
@@ -15,10 +15,11 @@ trait ModifyColumns
     protected $columns;
 
     public function column(string $column, ...$value)
     {
         $this->columns->hold($column, ...$value);
+        return $this;
     }
 
     public function columns(array $columns)
     {
         foreach ($columns as $key => $val) {

Handle NULL in conditionals

Currently sending NULL to a WHERE like so:
$select->columns('*')->from('table')->where('cond =', null);
Generates SQL SELECT * FROM table WHERE cond = :__1__
But we want SELECT * FROM table WHERE cond IS NULL

// src/Clause/Component/Conditions.php
@@ -52,10 +52,25 @@ class Conditions extends Component
         $this->list[$key] .= $expr;
     }
 
     protected function append(string $andor, string $expr, array $bindInline) : void
     {
+        if (! empty($bindInline) && $bindInline === [null]) {
+            $nospace = preg_replace('/\s/', '', $expr);
+            if ($nospace[-1] === '=') {
+                $trims = "\t= ";
+                $cond = ' IS';
+                if ($nospace[-2] === '!') {
+                    $cond .= ' NOT';
+                    $trims .= '!';
+                }
+                $cond  .= ' NULL';
+                $expr = rtrim($expr, $trims) . $cond;
+            }
+            $bindInline = [];
+        }
+
         if (! empty($bindInline)) {
             $expr .= $this->bind->inline(...$bindInline);
         }
 
         if (empty($this->list)) {

    public function testAutoNull()
    {
        $select = $this->newQuery()->from('t1')
            ->where('t1.c3 =', NULL)
            ->columns('t1.c1', 't1.c2', 't1.c3');

        $actual = $select->getStatement();

        $expect = '
            SELECT
                t1.c1,
                t1.c2,
                t1.c3
            FROM
                t1
            WHERE
                t1.c3 IS NULL
        ';
        $this->assertSameSql($expect, $actual);

        $select = $this->newQuery()->from('t1')
                ->where('t1.c3 !=', NULL)
                ->columns('t1.c1', 't1.c2', 't1.c3');

        $actual = $select->getStatement();

        $expect = '
            SELECT
                t1.c1,
                t1.c2,
                t1.c3
            FROM
                t1
            WHERE
                t1.c3 IS NOT NULL
        ';
        $this->assertSameSql($expect, $actual);
    }

[pmjones]

Hi @mrdamien,

The first one is a bug, easy to fix. Good catch!

The second one is probably not something I'd implement; doing regexes on SQL strings is something we did on Aura.SqlQuery quite a lot, and it was a burden. However, I am thinking of moving the whereEquals() logic from the Table subsystem over to here; that logic applies =, IN, and IS NULL appropriately to the condition based on the value:

$select->whereEquals['foo' => 'bar', 'baz' => null, 'dib' => ['zim', 'gir', 'irk']);
// WHERE foo = 'bar' AND baz IS NULL AND dib IN ('zim', 'gir', 'irk')

https://github.com/atlasphp/Atlas.Table/blob/1.x/src/Table.php#L90-L121

Thoughts?

[mrdamien]

Makes sense; and it's better. Would there also be a whereNotEquals() or similar?

[pmjones]

Hi @mrdamien -- I have now added whereEquals().

I don't expect a companion "whereNotEquals()" -- though it does provide a sort of symmetry, over the many years of using the query systems leading to Atlas Query I have never actually needed something like that.