sql2builder/sql2builder.github.io

Convert legacy SQL to Laravel query builder.

sql2builder.github.io

Convert legacy SQL to Laravel query builder.

Run in local

1. npm install
2. npm run start

sql2builder.github.io's Issues

Not running for `Update` from MySQL

I want to run UPDATE todo SET todo = "data update" WHERE todo.id = 14;

but the result are given SQL must start with select or SELECT

Convert failed

xx

Readme for local install

Dear author,

I am trying to run your project locally, but cannot succeed. Can you please provide a short readme on running the sql2builder converter locally?

Best wishes,
Tural

Support for: INSERT ... SET expression

at this moment, this expression is 'silently' throwing an error complaining about 'VALUES' key that does not exists. It should combine UPDATE features

Convert failed

xx

Convert failed

xx

can't do limits

SELECT pondok.no_pondok FROM pondok ORDER BY pondok.id DESC limit 1;

DB::table('pondok') ->select('pondok.no_pondok') ->orderBy('pondok.id','desc') ->get();

it should be like this
DB::table('pondok') ->select('pondok.no_pondok') ->orderBy('pondok.id','desc') ->limit(1) ->get();

so much advice from me, thank you

Found a bug

I'm having a problem with this.

whereNotIn... possible bug

try this: https://sql2builder.github.io?base64sql=c2VsZWN0ICogZnJvbSBteVRhYmxlIHdoZXJlIG15S2V5RmllbGQgaW4gKHNlbGVjdCBteUtleUZpZWxkIGZyb20gbXlTZWNvbmRUYWJsZSB3aGVyZSBvdGhlcktleT0nYXNkYXNkJykgb3JkZXIgYnkgbXlLZXlGaWVsZA==

converts to whereNotIn and the sql has no .. SELECT ...NOT IN...
I think that it must be whereIn
Thx

Left join method not closing

SELECT
    order_notes.*
FROM
    order_new
    LEFT JOIN order_notes
                  ON order_notes.order_id = order_new.id AND order_notes.parent_order_note_id = 0 AND
                     order_notes.order_note_nr = 0
    LEFT JOIN mrt
                  ON mrt.id = order_notes.serial_id AND order_notes.serial_id > 0
    LEFT JOIN label
                  ON label.id = mrt.label_id AND mrt.label_id > 0
    LEFT JOIN id
                  ON id.id = label.id
WHERE
    order_new.id = 114

produces:

DB::table('order_new')
->select('order_notes.*')
->leftJoin('order_notes',function($join) {
	$join->on('order_notes.order_id','=','order_new.id')
	->where('order_notes.parent_order_note_id','=',0)
	->where('order_notes.order_note_nr','=',0);
}
->leftJoin('mrt',function($join) {
	$join->on('mrt.id','=','order_notes.serial_id')
	->where('order_notes.serial_id','>',0);
}
->leftJoin('label',function($join) {
	$join->on('label.id','=','mrt.label_id')
	->where('mrt.label_id','>',0);
}
->leftJoin('id','id.id','=','label.id')
->where('order_new.id','=',114)
->get();

There's a ) misssing after each leftJoin

Feature: Support Union

This request is to add a feature for SQL Unions.

Laravel Docs: Laravel Eloquent Unions
MySQL Docs: MySQL 8.1 UNION Clause

Union Example

Example SQL input:

(select * from `users` where `last_name` is null) union (select * from `users` where `first_name` is null)

Example Eloquent output:

DB::table('users')
->whereNull('last_name')
->union(
            DB::table('users')
            ->whereNull('first_name')
)
->get();

Union All Example

Example SQL input:

(select * from `users` where `last_name` is null) union all (select * from `users` where `first_name` is null)

Example Eloquent output:

DB::table('users')
->whereNull('last_name')
->unionAll(
            DB::table('users')
            ->whereNull('first_name')
)
->get();

Union with orderBy on id

Example SQL input:

(select * from `users` where `last_name` is null) 
union
(select * from `users` where `first_name` is null) 
order by `id` asc

Example Eloquent output:

DB::table('users')
->whereNull('last_name')
->unionAll(
            DB::table('users')
            ->whereNull('first_name')
)
->orderBy('id')
->get();

Additional Context

While Eloquent doesn't handle returning a UNION from multiple models well (everything will be shoved into the first model's class), it does handle it well as a subselect with multiple models. It makes it much easier to scan across many different models as part of a very complex where.

Bug with Table Aliases

I'm currently having trouble trying to convert SQL with table aliases. So with the following query:

select
	*
from
	purchase_order_headers poh
where
	not exists (
	select
		pr.id
	from
		purchase_requests pr
	where
		pr.purchase_order_header_id = poh.id)

I get results like the following code:

DB::table('purchase_order_headers')
    ->select('*')
    ->whereNotExists(function ($query) {
        $query->from('purchase_requests')
            ->select('purchase_requests.id')
            ->where('purchase_requests.purchase_order_header_id','=',DB::raw('poh.id'));
    }
    ->get();

The problem is in DB::raw('poh.id'), so instead of getting poh.id it should be purchase_order_headers.id. Hope this can be fixed, and thanks for this awesome tool!