A SQL query builder with zero dependencies

Last update: Aug 4, 2022

Latitude Query Builder

Become a Supporter Latest Stable Version License Build Status

A SQL query builder with zero dependencies. Attempts to be PSR-1, PSR-2, and PSR-4 compliant.

Install

composer require latitude/latitude

Documentation

The latest stable documentation is available online and can be found in the docs/ directory.

License

Latitude is licensed under MIT and can be used for any personal or commercial project. If you really like it, feel free to buy me a beer sometime!

GitHub

https://github.com/shadowhand/latitude
Comments
  • 1. Small bug in Aliases

    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

    Reviewed by aitte2 at 2018-02-26 19:47
  • 2. Select::from() should accept queries as tables

    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());
    
    Reviewed by yeluolei at 2017-12-15 08:32
  • 3. Inserting Raw 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?

    Reviewed by riseoflex88 at 2018-12-13 11:49
  • 4. Safe with SQLite?

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

    Reviewed by aitte2 at 2018-02-26 16:38
  • 5. Allow to append more statements

    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?

    Reviewed by oscarotero at 2018-05-01 09:06
  • 6. Nested Joins

    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

    Reviewed by tmonte at 2017-07-28 16:50
  • 7. Parameters inside functions

    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?

    Reviewed by oscarotero at 2018-04-30 10:39
  • 8. Reset all the parts!

    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?

    Reviewed by bpolaszek at 2017-09-19 14:28
  • 9. How to first create Criteria and then add it to the query

    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?

    Reviewed by roomcays at 2021-12-29 14:55
  • 10. JOIN with nested SELECT statement

    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!

    Reviewed by nathanielconroy at 2017-09-30 03:16
  • 11. correcting Query.php for working with MySQL

    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;
        }
    }
    
    
    
    
    
    Reviewed by rootoor225 at 2020-09-25 23:33
  • 12. Is there a way to name the binding parameters?

    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

    Reviewed by bmovi at 2021-11-30 16:37
  • 13. ORDER BY case

    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
    
    Reviewed by riseoflex88 at 2019-05-08 10:28
  • 14. Queries cannot be modified once created

    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.

    Reviewed by shadowhand at 2018-05-21 12:30
  • 15. Missing limit in mysql update queries

    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.

    Reviewed by oscarotero at 2018-05-07 22:07
  • 16. Make all query building methods append-only

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

    • Merge functionality of addColumns() into columns()
    • Merge functionality of addFrom() into from()
    Reviewed by shadowhand at 2018-05-01 16:40
SQL to Laravel Query Builder

Marwan - SQL To Laravel Builder SQL to Laravel Query Builder, A Converter written in PHP Features Converts SQL Queries to Laravel Query Builder. Assis

Aug 3, 2022
Independent query builders for MySQL, PostgreSQL, SQLite, and Microsoft SQL Server.

Aura.SqlQuery Provides query builders for MySQL, Postgres, SQLite, and Microsoft SQL Server. These builders are independent of any particular database

Aug 3, 2022
A MongoDB based Eloquent model and Query builder for Laravel (Moloquent)

Laravel MongoDB This package adds functionalities to the Eloquent model and Query builder for MongoDB, using the original Laravel API. This library ex

Aug 13, 2022
Get MYSQL statement from query builder in laravel helper

Get MYSQL statement laravel This package allows to get mysql statement that query builder in laravel made it for debugging purposes. Basic usage Dump

Jul 15, 2022
Simple MySQL library for PHP 5.4+ includes Query Builder, PDO Native functions, Helper functions for quick use.

Simple MySQL library for PHP 5.4+ includes Query Builder, PDO Native functions, Helper functions for quick use.

Jul 14, 2022
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way. ( WEBSITE VERSION )
MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query and get result in a fastest way. ( WEBSITE VERSION )

Mysql Optimizer mysql optimizer also known as MOP is a php query handling and manipulation library providing easy and reliable way to manipulate query

Feb 14, 2022
Low code , Zero Configuration ORM that creates models, config, database and tables on the fly.
 Low code , Zero Configuration ORM that creates models, config, database and tables on the fly.

?? ARCA ORM ?? Low code , Zero Configuration ORM that creates models, config, database and tables on the fly. ?? ???? Made in India ???? Complete docu

Jul 14, 2022
SQL database access through PDO.

Aura.Sql Provides an extension to the native PDO along with a profiler and connection locator. Because ExtendedPdo is an extension of the native PDO,

Jul 30, 2022
ATK Data - Data Access Framework for high-latency databases (Cloud SQL/NoSQL).
ATK Data - Data Access Framework for high-latency databases (Cloud SQL/NoSQL).

ATK Data - Data Model Abstraction for Agile Toolkit Agile Toolkit is a Low Code framework written in PHP. Agile UI implement server side rendering eng

Aug 5, 2022
A validating SQL lexer and parser with a focus on MySQL dialect.

SQL Parser A validating SQL lexer and parser with a focus on MySQL dialect. Code status Installation Please use Composer to install: composer require

Aug 9, 2022
A php securised login system, using Hash, Salt and prevent from SQL Injections

A Basic Secure Login Implementation Hashed & Salted password ( only hashed in ShA-512 for now ) No SQL injection possible Prevent XSS attacks from the

Mar 6, 2022
Extract SQL statements from migrations

This is my package MigrationToSql To install: composer require bcleverly/migrationtosql --dev This repo is here to help you extract the SQL queries fr

Apr 15, 2022
A minimalistic implementation of asynchronous SQL for PHP.

libSQL A minimalistic implementation of asynchronous SQL for PHP. Installation via DEVirion Install the DEVirion plugin and start your server. This wi

Jul 30, 2022
Tiny php mysql lib (PDO-based) with handy fetch/update functionality, supports both SQL and parametric queries

Micro PHP mysql lib (~ 200 lines of code) with ultra powerful CRUD for faster than ever development: parametric fetch/insert/update/delete (based on a

Feb 22, 2022
A Laravel package to output a specific sql to your favourite debugging tool. The supported log output is Laravel Telescope, Laravel Log, Ray, Clockwork, Laravel Debugbar and your browser.
A Laravel package to output a specific sql to your favourite debugging tool. The supported log output is Laravel Telescope, Laravel Log, Ray, Clockwork, Laravel Debugbar and your browser.

Laravel showsql A Laravel package to output a specific sql to your favourite debugging tool, your browser or your log file. Use case You often want to

Jul 28, 2022
The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

The Enobrev\ORM library is a small framework of classes meant to be used for simply mapping a mysql database to PHP classes, and for creating simply SQL statements using those classes.

Jan 7, 2022
Eloquent Filter is a package for filter data of models by the query strings. Easy to use and fully dynamic.
Eloquent Filter is a package for filter data of models by the query strings. Easy to use and fully dynamic.

Eloquent Filter Eloquent Filter adds custom filters to your Eloquent Models in Laravel. It's easy to use and fully dynamic. Table of Content Introduct

Aug 13, 2022
Database Abstraction Layer, Schema Introspection, Schema Generation, Query Builders

Cycle DBAL Secure, multiple SQL dialects (MySQL, PostgreSQL, SQLite, SQLServer), schema introspection, schema declaration, smart identifier wrappers,

Jul 13, 2022
The query filter bundle allows you to filter data from QueryBuilder and the Database
The query filter bundle allows you to filter data from QueryBuilder and the Database

The query filter bundle allows you to filter data from QueryBuilder and the Database. you can filter multiple columns at the same time and also you can filter relation fields with two-level deep and without any join in your query builder.

Apr 8, 2022