SQL to Laravel Query Builder

Overview

Marwan - SQL To Laravel Builder

SQL to Laravel Query Builder, A Converter written in PHP

Features

  • Converts SQL Queries to Laravel Query Builder.
  • Assists on building queries as instructed in Laravel Documentation.
  • Provides options to interact with, for generating different results.

Supports

Laravel 8

Demo

Online demo

Live demo and free usage is available here.

Get Started

Install by manual download:

Download the repository and install required packages by composer.json :

Packagist

You can also install it from packagist by running the following command :

composer require rexshijaku/sql-to-laravel-builder

Usage

Simple example

<?php

use RexShijaku\SQLToLaravelBuilder\SQLToLaravelBuilder;

require_once dirname(__FILE__) . './vendor/autoload.php';

$options = array('facade' => 'DB::');
$converter = new SQLToLaravelBuilder($options);

$sql = "SELECT COUNT(*) FROM members";
echo $converter->convert($sql);

This will produce the following result:

DB::table('members')->count();
A more complex example :
$sql = "SELECT
                department_id,
                count(*) 
            FROM
                members
                LEFT JOIN details AS d ON d.member_id = members.member_id 
            WHERE
                ( age = 25 OR ( salary = 2000 AND gender = 'm' ) ) 
                AND id > 15 
            GROUP BY
                department_id 
            HAVING
                height > 1.60";
echo $converter->convert($sql);

and this will generate the result below :

DB::table('members')
    ->select('department_id', DB::raw('count(*)'))
    ->leftJoin('details AS d', 'd.member_id', '=', 'members.member_id')
    ->where(function ($query) {
        $query->where('age', '=', 25)
              ->orWhere(function ($query) {
                            $query->where('salary', '=', 2000)
                                  ->where('gender', '=', 'm');
        });
    })
    ->where('id', '>', 15)
    ->groupBy('department_id')
    ->having('height', '>', 1.60)
    ->get();
Notice

If you need to change options, or get more comprehensive understanding of provided options then see the following section of Options. There are dozens of examples for every use case explained in the Query Builder documentation of Laravel 8, which are located in the examples folder.

Options

Some important options are briefly explained below:

Argument DataType Default Description
facade string DB:: Facade which allows the access to the Database functionality.
group boolean true Whether it should group key value pairs into a php array, or generate separate commands for each pair. See an example here.

How does it works ?

SQL-To-Laravel-Builder is built on top of PHP-SQL-Parser. While PHP-SQL-Parser is responsible for parsing the given SQL Query as input. The result of the PHP-SQL-Parser is the input of SQL-To-Laravel-Builder.

The structure has three main parts :

  1. Extractors classes - which help to pull out SQL Query parts in a way which are more understandable and processable by Builders.
  2. Builder classes - which help to construct Query Builder methods.
  3. Creator - which orchestrates the process between Extractors and Builders in order to produce parts of Query Builder.

Known issues

  • It is not tested in all cases.
  • Poor error handling.

Contributions

Feel free to contribute on development, testing or eventual bug reporting.

Support

For general questions about Marwan - SQL-To-Laravel-Builder, tweet at @rexshijaku or write me an email on [email protected]. To have a quick tutorial check the examples folder provided in the repository.

Author

Rexhep Shijaku

Thank you

All contributors who created and are continuously improving PHP-SQL-Parser, without it, this project would be much harder to be realized.

In memoriam

For the innocent lives lost (including Marwan al-Masri, aged just six) during the 2021 Israel–Palestine crisis.

License

MIT License

Copyright (c) 2021 | Rexhep Shijaku

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

You might also like...
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

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

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

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

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.

API abstracting communication with SQL providers (eg: MySQL) on top of PDO inspired by Java JDBC

SQL Data Access API Table of contents: About Configuration Execution Installation Unit Tests Examples Reference Guide About This API is a ultra light

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

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,

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.

Comments
  • Bad conversion.

    Bad conversion.

    I just found this package and found it very interesting. I have tested it with a query, it worked quite well, however it had an error.

    SQL Query:

    UPDATE ps_feature_value_lang a 
    JOIN ps_feature_value_lang b ON a.id_feature_value = b.id_feature_value and CHAR_LENGTH(b.value) > 0
    SET a.value = b.value
    WHERE a.value = ''
    

    Result:

    DB::table('ps_feature_value_lang a')
    ->where('a.value','=','')
    ->join('ps_feature_value_lang b','a.id_feature_value','=and>','b.id_feature_value')
    ->update(['a.value'=>DB::raw('b.value')]);
    

    The problem is in '=and>' CHAR_LENGTH(b.value) > 0 is also ignored.

    opened by mreduar 2
  • select query with count gives faulty result

    select query with count gives faulty result

    Example: Input: select count(*) from table_name where is_active = 0 Output: DB::table('table_name')->count()->where('is_active','=',0);

    Output is invalid eloquent, as it first execute count and on that count where clause cannot be executed and gives Erorr: "Call to a member function whereIn() on int"

    opened by yhinger 1
  • Double

    Double "as u"

    Awesome tool Found a small bug

    If you alias the table it gets parsed twice

    select * from users as u
    join roles as r on r.id = u.role_id
    where u.id = 1
    
    DB::table('users as u as u')...
    opened by sinnbeck 1
  • Without get()

    Without get()

    I want to get a Builder object but without get I want to keep working on it.

    possible?

    edit: Now I notice that it just generates a string without activation…

    opened by sbc640964 0
Releases(1.0.6)
Owner
Rexhep Shijaku
Rexhep Shijaku
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

Aura for PHP 419 Sep 28, 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

Jens Segers 6.2k Sep 22, 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

Ahmed Helal 9 Jul 15, 2022
A lightweight nearly-zero-configuration object-relational mapper and fluent query builder for PHP5.

Idiorm http://j4mie.github.com/idiormandparis/ Feature/API complete Idiorm is now considered to be feature complete as of version 1.5.0. Whilst it wil

Jamie Matthews 2k Sep 12, 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.

Kodols 8 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 )

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

null 3 Feb 14, 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.

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

Dieter Coopman 190 Sep 15, 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,

Aura for PHP 526 Sep 28, 2022
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

Agile Toolkit 255 Sep 19, 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

phpMyAdmin 348 Sep 21, 2022