Grammar for VALUES/ROWs

[rudiedirkx]

I use this package to load a bunch of specific records by multi column combinations instead of a query:

with ids (a, b) as (
  VALUES ROW(534, 1), ROW(3804, 3), ROW(3804, 1), ROW(531, 10), ROW(531, 7)
)
select *
from site_checks
inner join ids on ids.a = site_checks.a and ids.b = site_checks.b

But I had to type the VALUES and ROWs myself. VALUES might be standard SQL, but ROW() is db specific. MySQL wants it, SQLite doesn't, that's all I know.

Seems like a job for grammar? Maybe including the VALUES, or maybe not, or maybe even per ROW.

(I don't even know how to call grammar from my custom relationship, but I could probably find out 😄)

[staudenmeir]

Hi @rudiedirkx, I've never seen this use case. I'll look into it.

[staudenmeir]

What Laravel version are you using?

[rudiedirkx]

Laravel 10.6.0 MySQL 8.0.35

I used to do this in SQLite (I don't remember why), but its syntax is different (no ROW, just brackets). I finally had a use case in a 'real' MySQL project, and found out MySQL uses ROW. I'm okay with hardcoding MySQL support, but it's not great.

[rudiedirkx]

I'm using this in rdx/laravel-aggregate-relationships:MultiColumnHasMany.php currently. Usage:

// protected $fillable = ['a_id', 'b_id'];
// ...
function similar_records() {
  return $this->multiColumnHasMany(self::class, ['a_id' => 'a_id', 'b_id' => 'b_id']);
}

MultiColumnHasMany also works without this CTE package, but the query is much faster with CTE, even if it injects 200 hardcoded combinations/ROWs. CTE is cool.

[staudenmeir]

Do you know about the improved database expressions in Laravel 10? https://github.com/laravel/framework/pull/44784

They are great for your use case:

<?php

namespace App\Expressions;

use Illuminate\Contracts\Database\Query\Expression;
use Illuminate\Database\Grammar;
use Illuminate\Database\Query\Grammars\MySqlGrammar;

class Values implements Expression
{
    public function __construct(
        protected array $rows
    ) {
        //
    }

    public function getValue(Grammar $grammar): string
    {
        switch (true) {
            case $grammar instanceof MySqlGrammar:
                $rows = [];

                foreach ($this->rows as $row) {
                    $values = array_map(
                        fn ($value) => $grammar->escape($value),
                        $row
                    );

                    $rows[] = 'ROW(' . implode(', ', $values) . ')';
                }

                return 'VALUES ' . implode(', ', $rows);
            // TODO
        }
    }
}

DB::table('ids')
    ->withExpression(
        'ids',
        (new Values([[1, 2], [3, 4]]))->getValue(DB::connection()->getQueryGrammar())
    )->get(),

If withExpression() supports expressions natively, it would look like this:

DB::table('ids')
    ->withExpression(
        'ids',
        new Values([[1, 2], [3, 4]])
    )->get(),

The PR's author also collects these classes in a package where you could propose a VALUES implementation: https://github.com/tpetry/laravel-query-expressions

[rudiedirkx]

But but but this package already has CTE Grammar. VALUES/ROW seem very CTE grammar to me. tpetry/laravel-query-expressions doesn't add grammar. How about a PR? I know MySQL and SQLite, and I can look into the rest. Althoouugh, I wouldn't even know how to inject it into the query/withExpression()...

[staudenmeir]

IMO, this is a very niche use case and so I don't see it as part of the package.