I noticed this "heavy" code that's doing three separate str_replace calls which each has to create a string, parse it and return each string. Which is going to suck if the string is a very long one:

And you do them in a special order to ensure backslashes are processed first, etc...

I am not 100% sure but I THINK that all of that can be handled like this instead:

$str = 'a\\_b\\c%'; // Input: a\_b\c%
var_dump(strtr($str, ['\\' => '\\\\', '%' => '\\%', '_' => '\\_'])); // Result: a\\\_b\\c\%

As PHP's manual says, strtr will not replace a replacement, so that is why I think this should be safe: http://php.net/strtr

And as you can see, it's just a SINGLE function call which handles it all in native C code! No more repeated, wasteful copying back/forth of strings between native str_replace() and PHP. :-)

PS: Any other code locations that do this kinda escaping benefits from the same idea... :-) Maybe the library has a generic escape() / quote()? I haven't started using it yet, but this idea would be good there too.

I want to use the LIKE operator for filtering a query. I already found that there is a LikeContains.php but I cannot figure out where to use it properly. I would have expected it to be an options like the other field comparisons. Furthermore, an example in the documentation would help a lot since LIKE is nothing utterly special.

PHP Fatal error:  Uncaught TypeError: Argument 2 passed to Latitude\\QueryBuilder\\on() must be of the type string, object given...

I'm trying to use the fn() function to do a concat on the join statement. However the fn is returning an object, but on() needs a string.

How do I pluck the string out of the fn function in the example below?...

join(alias('urls', 'u'), on('u.source', fn('CONCAT', 'node/', 'n.nid')));

(loving this class so far, the more I throw at it the better it is, great job!)

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

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 there. Sorry if this is a naive question, I've read the documentation but I can't find how to do a MAX as in select max(column).

Doing a $builder->select(alias('max(my_column)', 'my_alias')) will result in:
SELECT "max(my_alias"."my_column)"

Thank you.

Adding Psalm to the Travis CI test script would offer stronger type-safety guarantees, while also reducing the likelihood of undiscovered bugs.

This is mostly a note for myself; Psalm doesn't work right now because of an unknown error, and I've opened vimeo/psalm#401 to address it.

I suspect that once the Iterator bug is squashed, there will be few (if any) type violations in Latitude, based on a quick scroll through the code.

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:

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.

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? :-)

Hello, I want to add +1 to a column in a update prepared statement, but latitude put "column +1" in the params. Here is my code :

$query = $this->query
    ->update($this->table, [
        'nombre_vues' => 'nombre_vues + 1'
    ])
    ->where(field('id')->eq($id))
    ->compile();
$requete = $this->bdd->prepare($query->sql());
$requete->execute($query->params());

Here is the params :
array (size=2) 0 => string 'nombre_vues + 1' (length=15) 1 => string '54' (length=2)
Can you help me ? Thank you ! 😊

Hi! You have a wrong link ("available online") in README.md

I open this issue in Refernce to #69

Maybe I'm missing something but I am unable to get this to work.

Pre-Informations:

The library is used for a project which needs to build querys dynamically.

I have a Helper Class which looks like this:

class Query
{
    /**
     * instantiates a new QueryFactory with the SQLServer Engine
     *
     * @return QueryFactory
     */
    public static function create() : QueryFactory
    {
        return new QueryFactory(new SqlServerEngine());
    }

    /**
     * instantiates a new SelectQuery
     *
     * @param string ...$columns (optional) Columns
     * @return SelectQuery
     */
    public static function select(string ...$columns) : SelectQuery
    {
        return self::create()->select(...$columns);
    }
}

First working test

$query = Query::select()
            ->from('user')
            ->where(field('fkuserworkflowstatus')->in(6, 7))
            ->orderBy('pkuser')
            ->offset(0)
            ->limit(10)
            ->compile();

Test with Array (not working)

$status = [6, 7];

$query = Query::select()
            ->from('user')
            ->where(field('fkuserworkflowstatus')->in($status))
            ->orderBy('pkuser')
            ->offset(0)
            ->limit(10)
            ->compile();

Workaround

$status = [6, 7];

$query = Query::select()
            ->from('user');

if (is_array($status) {
    $query = $query->where(call_user_func_array([field('fkuserworkflowstatus'), 'in'], $status));
}

$query = $query->orderBy('pkuser')
            ->offset(0)
            ->limit(10)
            ->compile();

This is a working solution but in my opinion not a good one.

I hope someone can show me the correct or a better way to do it.

Documentation states:

In INSERT and UPDATE queries, boolean and null values will be added directly the query, rather than as placeholders

This functionality is not present in version 3.0.0.

Version = 1.1.0

Code to reproduce

use Latitude\QueryBuilder\InsertQuery;
use Latitude\QueryBuilder\Expression;

$query = InsertQuery::make('t1', [
    'created_at' => Expression::make('NOW()')
]);

var_dump($query->sql());  // INSERT INTO t1 (created_at) VALUES (NOW())
var_dump($query->sql());  // INSERT INTO t1 (created_at) VALUES ()

Expected

Each $query->sql() should return the same.

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!

$insert = InsertQuery::make('public.users', $values);
$sql = $insert->sql();

Fails with:

Latitude\QueryBuilder\IdentifierException "Invalid SQL identifier: public.users"

Now we can not use SQL_CALC_FOUND_ROWS
May you please support this?

I just discover your lib it's just what i'll need!
This issue is just a suggestion for an enhancement.

The Postgres join could support the using short syntax.

left join schema.table using (field_id)

        // 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?

Hi! Many thanks for the library.

Example query:

$factory->select()->from('foo')->where(field('bar')->in([1,2,3,4]));

Generated SQL is:

SELECT * FROM foo WHERE bar IN (?)

Parameters: [1,2,3,4]. The issue is that PDO does not support array parameters like this one, so I suggest to enhance the Latitude\QueryBuilder\Partial\Parameter class to handle field('bar')->in([1,2,3,4]) properly. It could be done like so:

public function __construct($value)
{
    if (\is_bool($value) || $value === null) {
        $this->sql = var_export($value, true);
    } elseif (\is_array($value)) {
        $this->sql = str_repeat('?,', \count($value) - 1) . '?';
        array_push($this->params, ...$value);
    } else {
        $this->params[] = $value;
    }
}

As of now I had to write some functions and classes to override the ‘invalid’ behaviour:

function field($name): CriteriaBuilder
{
    return new CriteriaBuilder(identify($name));
}

function array_param($value): StatementInterface
{
    // custom class with the logic mentioned above
    return isStatement($value) ? $value : new ArrayParameter($value);
}

function array_param_all(array $values): array
{
    return array_map('array_param', $values);
}

function array_listing(array $values, string $separator = ', '): Listing
{
    return new Listing($separator, ...array_param_all($values));
}

I can't say if the change could break backward compatibility but it would be nice to have parameters binding fixed.

I can submit PR if you like.

https://github.com/shadowhand/latitude#identifier-escaping

So I have this piece of code....

$select = SelectQuery::make()->from('transit_feeds')->where(Conditions::make('agencyid = ?', 5));
print_r($select->sql(Identifier::make()));

And I wanted to escape all everything for the sake of security, so the sql returned this and didn't escape the where column...

SELECT * FROM `transit_feeds` WHERE agencyid = ?

However, when as per in the identifier escaping document, it would escape everything it if I put in Identifier::make(), but it's not if you don't define the table AS such as, so I tried putting a letter in...

$select = SelectQuery::make()->from('transit_feeds f')->where(Conditions::make('f.agencyid = ?', 5));
print_r($select->sql(Identifier::make()));

This would be returned...

SELECT * FROM `transit_feeds` AS `f` WHERE `f`.`agencyid` = ?

I don't mind putting the quotes around in my first example, but I'm just curious if this would be a good practice to have Latitude escaping columns even if the tables aren't defined AS unless I'm missing something here? 😳

Cheers!

I'd like to remove the offset / limit settings on a query (to work out the total count of results of a more complex query)

I've seen this pull request which seems to cover what I'm referring to:
#31

But I'm not sure it's still working as I'd guess the code has been reformatted a fair bit since then as its not working for my setup (using the BasicEngine).

		$count_sql->limit(NULL);
		$count_sql->offset(NULL);

PHP Fatal error: Uncaught TypeError: Argument 1 passed to Latitude\\QueryBuilder\\Query\\SelectQuery::limit() must be of the type integer, null given

The readme links to https://shadowhand.github.io/latitude/ for online documentation. This redirects to https://latitude.shadowhand.me/ which fails to resolve.

While using CONCAT to combine database field names with non database field names, an error will be thrown unless using literal. In order to include a space between two field names, the space must be enclosed within single quotes and double quotes.

->select(alias(func('CONCAT', 'fieldNameA', literal("' '"), 'fieldNameB'), 'combinedString'))

Is this the best way to include a space or a string in a CONCAT?

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;
    }
}

Is it possible to group one group inside another? I need to recreate this Query for an existing search tool.

The existing sql is like so...

WHERE something = 1
AND (
            po.po_duration_magnitude <= 90 
            AND po.po_duration_unit = 'Days'
        ) OR (
            po.po_duration_magnitude <= 12 
            AND po.po_duration_unit = 'Weeks'
        )

My best guess on doing this was to nest a group inside another group like so...

    $sql_ob->andWhere(
            group(
                group(
                    field('po.po_duration_magnitude')->eq('90')
                    ->or(field('po.po_duration_unit')->eq('Days'))
                ),
                group(
                    field('po.po_duration_magnitude')->eq('12')
                    ->or(field('po.po_duration_unit')->eq('Weeks'))
                )
            )
        );

But, this doesn't work... :(

Any suggestions on how to achieve this?

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?

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

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

version = 1.0.1

Code to reproduce

require(__DIR__ . '/vendor/autoload.php');
use Latitude\QueryBuilder\SelectQuery;
use Latitude\QueryBuilder\Conditions;

$select = SelectQuery::make()
    ->from('t1')
    ->leftJoin('t2',  Conditions::make('t2.t1_id = t1.id'))
    ->leftJoin('t3',  Conditions::make('t3.t2_id = t2.id'))
;

echo $select->sql();
// SELECT * FROM t1 LEFT JOIN t2 ON t2.t1_id = t1.id, LEFT JOIN t3 ON t3.t2_id = t2.id
// mysql error:  Syntax error near 'LEFT JOIN t3 ON t3.t2_id = t2.id' at line 1

Expected

echo $select->sql();
// SELECT * FROM t1 LEFT JOIN t2 ON t2.t1_id = t1.id LEFT JOIN t3 ON t3.t2_id = t2.id

JOINs should not be separated by a comma.

Hi there,

Is there any documentation that explains how to start a db transaction & how to perform a rollback & implicit commits?

Thanks

Hi. Similar to #47, is there a way to add extra info to an INSERT query eg INSERT IGNORE tablename... to ignore duplicate keys?

What do you think about adding a generic queryFlags() method to AbstractQuery?

How can i use not equal ( != ) ?

Hi there

I'm trying to execute the following time format expression:

$this->select(Expression::make('TIME_FORMAT(sessions.start_time, "%H:%i") as time', ), 
 "sessions.title")
        ->where(Conditions::make('date >= ?', date("Y-m-d")))
        ->orderBy(['date', 'ASC'])->limit(8);`

but it fails everytime saying :
Return value of Latitude\QueryBuilder\Expression::sql() must be of the type string, boolean returned

Thanks

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());

How can i use AND in query ?

I'm getting the following error:

PHP Fatal error:  Uncaught Error: Call to undefined method Latitude\\QueryBuilder\\Query\\SelectQuery::order(

When I try to use the order function. Here's a simplified version of my code...

use Latitude\QueryBuilder\Engine\BasicEngine;
use Latitude\QueryBuilder\QueryFactory;
use function Latitude\QueryBuilder\alias;
use function Latitude\QueryBuilder\order;

$builder = new QueryFactory(new BasicEngine());

$query = $builder->select('pc.*')
			->from(alias(SHARED_DB.'.programme_cache', 'pc'))
			->order('pc.pc_p_id', 'desc')
                         ->compile();

I think I must be getting the syntax of the order function wrong, but the docs doesn't really seem to cover this function very well.

latitude.shadowhand.me DNS doesn't seem to lead anywhere.

I have a branch where I added basic support for CREATE TABLE, ALTER TABLE, and DROP TABLE schema modification queries (with accompanying tests), would you be interested in it?

It seems that \Latitude\QueryBuilder\Ruler\Visitor() does not support grouping at all.
I wrote grouping test for \Latitude\QueryBuilder\CriteriaTest, that fails:

class CriteriaTest extends TestCase
{
    public function testGrouping()
    {
        $criteria = $this->factory->criteria('users.active = true and (users.id = 100 or users.role = "admin")');

        $this->assertSql('users.active = true and (users.id = ? or users.role = ?)', $criteria);
        $this->assertParams([100, 'admin'], $criteria);
    }
}

Test result:

Failed asserting that two strings are identical.
--- Expected
+++ Actual
@@ @@
-'users.active = true and (users.id = ? or users.role = ?)'
+'users.active = true and users.id = ? or users.role = ?'

The same query fetched directly from \Hoa\Ruler\Ruler::interpret gives:

$model = new \Hoa\Ruler\Model();
$model->expression =
    $model->and(
        $model->{'='}(
            $model->variable('users')
                ->attribute('active'),
            true
        ),
        $model->or(
            $model->{'='}(
                $model->variable('users')
                    ->attribute('id'),
                100
            ),
            $model->{'='}(
                $model->variable('users')
                    ->attribute('role'),
                'admin'
            )
        )
    );

All the links in the Documentation section of latitude/docs/index.md are broken.

How to add columns into a SELECT query that retreive their value by a subselect (for each row). Sorry, somehow hard to explain. I'll make an example.

Assume the following two tables:

Table "Designs"

Table "Tags"

I want to retrieve a list of all designs and have their tags as a comma-separated list (in a single query). I would usually do something like this:

SELECT 
   DesignID, 
   Name, 
   (SELECT ... FROM Tags t WHERE t.DesignID = d.DesignID) Tags 
FROM Designs d

How to accomplish this with Latitude?

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?

see https://scrutinizer-ci.com/g/shadowhand/latitude/inspections/e0ec5b50-95e9-4005-af8d-f442f5f18dd6/issues/files/src/Identifier.php?status=all&orderField=path&order=asc&honorSelectedPaths=0#inspectioncomment-61858587

With the "andWhere", I'm struggling to work out how to do a comparison of one column vs another column.

$example->andWhere(field('price_a')->lt('price_b'));
which produces:
AND price_a > "price_b"

Which isn't quite right in this case since it's a column comparison. I'm looking at the criteria and expressions and feeling quite dumb that I just can't work out how to apply them to this case.

Any suggestions?

Greetings,

First, thank you for this amazing query builder.
While writting some specific case, I encountered an issue and haven't found anything matching it on the docs.

Let's imagine we want to get all fields from a table and specific fields from another one, in a joint query.
I'd do something like this :

$this->builder->select(
            'a.*',
            'b.field_x',
            'b.field_y',
            'b.field_z'
)

Unfortunately, the query builder is adding the backquotes around the *, as shown on this dump :

SELECT `a`.`*`, `b`.`field_x`, `b`.`field_y`, `b`.`field_z`

which obviously lead to this error :

Column not found: 1054 Unknown column 'a.*' in 'field list'

Atm my only way to make this query work is to use

str_replace(".`*`", ".*", $queryBuilder->sql())

Did I missed something ? If not, I guess the * should be an exception to the "back quote everything" rule !

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

Similar to this request: #31

I'd like to be able to reset the groupby setting, is this possible currently? if not, could it be added as a feature?

I'm building a class which uses latitude as the backbone, but it means I'm not certain what a user will add or remove from the query as it's being built. The groupby is set as a default, but it needs to be overwritten as required.

The other fields such as orderby were much simpler to see how to amend the latitude code to suggest a fix, but I can't see the same structure with orderby to make a suggestion on how to amend.

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.

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