Found a small bug in the feature introduced here:

https://github.com/shadowhand/latitude#aliases https://github.com/shadowhand/latitude/pull/35/files

The issue is with the alias-code posted here:

use Latitude\QueryBuilder\Alias as a;

$select = SelectQuery::make(
        a::make('id', 'user_id')
    )
    ->from('users');

echo $select->sql();

Aliases do not work in the selectquery constructor and not in columns()... ;-)

Result:

PHP Fatal error: Uncaught TypeError: preg_split() expects parameter 2 to be string, object given in vendor/latitude/latitude/src/Identifier.php:79

Using code: dev-master

Maintainer edit: Subject was Can't select from an sub query.

this is part of my long sql, I want to select from an sub query as table tmp1, but this library doesn't support. and given error indentify

SelectQuery::make()->from(Alias::Make(SelectQuery::make('user', ['id']))->sql(), tmp1)->sql());

        // Destination ID's
        $destinations = [75, 97, 103];

        $fields = [];
        foreach ($destinations as $destination_id) {
            $fields[] = field('destination_id')->eq($destination_id);
        }

        // This works, but it's assuming there are always 3 elements
        $this->sql_ob->andWhere(group(
            $fields[0]
            ->or($fields[1])
            ->or($fields[2])
        ));

I need a function / method that can take the array of $queries, and add them into the andWhere statement. The problem is that the '->or' method is required on the subsequent elements.

If that's not possible, is there a method to add Raw SQL into a where statement?

This library is amazing. Perfect syntax and no pesky PDO connections.

I just wonder if it's always safe with SQLite? I like to build small toy-project sites with SQLite to avoid a central database.

And I noticed the engine defaults to "Common", which sounds like it may be safe... since I guess "Common" means it avoids using MySQL-based SQL language features...

And since I control the conditions via what conditions I call on this library, I will never risk producing non-SQLite syntax...?

Could you please update the readme to at least mention the state of SQLite compatibility? :-)

Hi. I need to create queries programatically, but seems that this library doesn't have methods to append more statements without remove the olders. For example:

$query = $builder->select($basic_columns);

if ($more_columns) {
    $query = $query->columns($more_columns);
}

Adding more columns, removes the older columns. But this behaviour is not consistent. There's some functions that always add statements without override the previous. For example:

$query = $builder->select($columns)
    ->join($table, $criteria)
    ->join($table, $criteria)
    ->orderBy($column)
    ->orderBy($column)

Other capabilities have methods to specifically append instead override, like where, andWhere and orWhere.

I suggest to have a consistent way to all statements. Some options:

• Override always and provide a and method to append. For example:

  $query->orderBy($column)
        ->andOrderBy($other_column)
  

• Other option is append always. This makes the andWhere useless:

  $query->column($basic_columns)
        ->column($more_columns)
        ->where($a_condition)
        ->where($other_condition)
  

• And finally, have a way to get the current value to manipulate it before override:

  $columns = $query->getColumns();
  $columns[] = $new_column;
  $query->column($columns);
  

I don't have a strong preference for any option (and you may have a better idea), but would love to have a way to archieve this. What do you think?

Is there a way to do a nested join? Like:

SELECT t1.field1, t2.field2, t3.field3
FROM table1 as t1 
    LEFT JOIN table2 as t2
        LEFT JOIN table3 as t3
        ON t3.id = t2.table3_id
    ON t2.id = t1.table2_id
WHERE t1.id = 1

Thanks

Hi. I'm trying to upgrade a database related library with this latitude (nice work, btw) and have a query built in the following way

$query = $builder->update('post')
    ->set([
        'title' => param('New title'),
        'body' => param('New body'),
        'point' => fn('POINT', 1, 2),
    ])
    ->where(field('id')->eq(2));

The arguments of the POINT function are not converted to params. I have something like this:

UPDATE `post` SET `title` = ?, `body` = ?, `point` = POINT(`1`, `2`) WHERE `id` = ?

but what I want is this.

UPDATE `post` SET `title` = ?, `body` = ?, `point` = POINT(?, ?) WHERE `id` = ?

I've tried to use params inside the function:

fn('POINT', param(1), param(2))

but that throws an exception because fn only accept strings as arguments. Is any way to archieve this?

Following #31... I just figured out that I could not remove the orderBy part when cloning a query for pagination. The produced query is valid SQL but this leads to a performance issue (no need to sort results if the only goal is to count them).

Since the query is entirely mutable, I think everything should be resetable.

So here's a new PR to ensure every part can be reset. Only the join part is a little touchy, so I added a resetJoins() method, and refactored the unit test.

What do you think?

I have stumbled upon a situation where I'd like to first "build" a Criteria object and then apply it to the query using, for example, andWhere() method.

I can not (or I don't know how to do it) chain criterias as they're set in certain conditions and grouped in several ANDs and ORs. What I normally do with, for example arrays, I create an empty array, then I fill it with values (while looping and processing conditions). Optionally I do check if array is empty and then apply it (if it is not empty) or not (if it is).

So I'm thinking the same way about Criterias and I can not "create" an empty Criteria object without breaking the query (in a weird way).

Example code:

$criteria = new Criteria();

foreach ($conditions_1 as $condition_1) {
    foreach ($conditions_2 as $condition_2) {
        if ($condition_1 and $condition_2) {
            $criteria->or(field('foo')->eq(true)->and(field('bar')->eq(true));
        } else if ($condition_1) {
            $criteria->or(field('foo')->eq(true));
        } else {
            // Do nothing (to explicitly emphasize that in some cases built Criteria may be empty at the end
        }
    }
}

if ($criteria) {
    $query->andWhere($criteria);
}

Can anybody please point me a way to go? Maybe there is an another approach for such situation?

First off, this is an awesome component! I'm hoping that I can use it in a project of mine.

However, I'm having trouble generating a statement similar to the following:

SELECT a.fullname, a.ID, a.Score FROM my_table a INNER JOIN ( SELECT fullname, MAX(Score) Score FROM my_table GROUP BY fullname ) b ON a.fullname = b.fullname AND a.Score = b.Score

Is this something that is currently supported? I don't quite understand how to nest a SELECT statement inside of a JOIN like above. Thanks!

change the sql() method like under

<?php
declare(strict_types=1);

namespace Latitude\QueryBuilder;

final class Query
{
    /** @var string */
    private $sql;

    /** @var array */
    private $params;

    public function __construct(
        string $sql,
        array $params
    ) {
        $this->sql = $sql;
        $this->params = $params;
    }

    public function sql(): string
    {
        // return $this->sql; // original 
        return str_replace('"', "`",$this->sql); // modified by for MySQL
    }

    public function params(): array
    {
        return $this->params;
    }
}

Hello, I use your library to generate sql for Clickhouse server.

I cannot use question mark because the Clickhouse php library doesn't support them. Is there any way to name parameter?

My query looking like that:

SELECT "date", "id" FROM "users" WHERE ("date" >= ? AND "date" <= ?);

I need something like that:

SELECT "date", "id" FROM "users" WHERE ("date" >= :date AND "date" <= :date);

or that:

SELECT "date", "id" FROM "users" WHERE ("date" >= {date} AND "date" <= {date});

Maybe this is already possible with the library? Eventually, is there a method to mix query and parameters? That way, I can cheat by managing the params myself

I'm looking to do something similar to this... https://stackoverflow.com/questions/6332043/sql-order-by-multiple-values-in-specific-order

Where I already know the specific order to apply before creating the query.

In raw sql this would be written like so...

WHERE
    id IN ('a','b','c') ...
ORDER BY
   CASE id
      WHEN 'a' THEN 3
      WHEN 'b' THEN 1
      WHEN 'c' THEN 2
   END, id

Consolidating discussion from #71, #66, #58, #31 to document the plan for implementing modifications to queries. This applies mainly to SELECT but also other queries.

WIP: This issue will be modified until a clear plan is made.

Currently some methods in Latitude are additive and some are setters. For instance, columns() always changes the column list and join() always adds a new join. A full list:

method name | add | set ------------ | --- | --- columns() | | x addColumns() | x | from() | | x addFrom() | x | join() | x | where() | | x andWhere() | x | orWhere() | x | groupBy() | x | orderBy() | x | limit() | | x offset() | | x having() | | x

Not all methods need to be additive and not all methods need to be setters. It should be possible to reset values that are additive.

I'm getting this error:

Error: Call to undefined method Latitude\QueryBuilder\Query\UpdateQuery::limit()

and have noticed that limit capability is missing in Update queries. orderBy is missing too.

Refer to changes make in #58; the following methods should be modified:

• Merge functionality of addColumns() into columns()
• Merge functionality of addFrom() into from()