solution10 / sql Goto Github PK

I tried to find a way to add a "JOIN" with an alias for the table. I only found the following way to implement this:

    $query->join(new Expression('`location` AS `work`'), 'work.id', '=', 'users.work_location_id');
    $query->join(new Expression('`location` AS `home`'), 'home.id', '=', 'users.home_location_id');

Using an expression does not seem to be the intended way for this, especially as quoting is not based on the given dialect.

Would it be possible to implement a solution like this:

    $query->join(['work' => 'location'], 'work.id', '=', 'users.work_location_id');

or like the workaround I am using now (the class Alias implements the ExpressionInterface for this):

    use Dialect\MySQL\Alias;
    //...
    $query->join(new Alias('location', 'home'), 'home.id', '=', 'users.home_location_id');

Or is there an implementation, which I missed?

I've spent days searching for a simple / clean sql query builder that is unit tested, psr-compliant, extensible, does not need a PDO connection, and without any unusual weirdness. Delighted to stumble across your package this morning. It looks awesome. Thanks for making it available.

Was thinking about making a pull request for a couple of new features, but wanted to check in with you first to see if the idea is worth pursuing...

Pagination:
https://github.com/whiteoctober/Pagerfanta and similar need 2 things to create a paginator: the total number of rows in the full result set, and a way to slice out just the current page. Adding something like

 public function count()
    {
        $parts = [
            'select count(*) as row_count',
            $this->buildFromSQL(),
            $this->buildJoinSQL(),
            $this->buildWhereSQL($this->dialect),
            $this->buildGroupBySQL(),
            $this->buildHavingSQL($this->dialect)
        ];
        return join(" ", array_filter($parts));
    }

just below Select->sql() would make it super easy to create an adapter class and would mean you don't have to clone the Select just to change the columns and drop off the order / limit.

Engine / driver specific flags:
Aura SQL https://github.com/auraphp/Aura.SqlQuery#mysql-query-objects-mysql allows you to inject flags into the query (eg for MySQL: INSERT DELAYED, SELECT DISTINCT, SELECT SQL_SMALL_RESULT). Adding a trait to collect flags

trait Flags {
    /** @var string[] simple array of flag names inserted into the query */
    private $flags = [];

    public function addFlag($flag) {
        $this->flags[]= $flag;
        return $this;
    }

    public function getFlags() {
        return join(' ', $this->flags);
    }
    // ... etc
}

would allow the injection of arbitrary driver specific flags in buildSelectSQL(), buildInsertSQL() etc to tune performance. Your code could just treat them as arbitrary strings injected and not care what they contain.

REPLACE or ON DUPLICATE KEY UPDATE
Again these are MySQL specific syntax, but the ability to change the name of the sql command in Insert->sql() from 'INSERT INTO' to 'REPLACE INTO' would be nice. Or the creation of a new class InsertOrUpdate.php that was able to do 'on duplicate key update' style queries.

Would you accept pull requests for any or all of the above?

Is it possible to parse SELECT ... INTO OUTFILE ... queries?