Framework: [FR] Allow query builder to build without a table

[judgej]

The query builder in 4.2.x has to start by specifying a table. However, queries (MySQL at least) do not need to select from a table.

For example, during a sync process, I needed to know the current clock time of a remote database by selecting NOW(). The following solved that specific case:

$db_time = DB::connection('my_db')->selectOne('SELECT NOW() AS now')->now;

However, as a generic case, that does not allow me to build the query any further, since selectOne returns the results and not a query builder object to extend. Only DB::table('name') or DB::connection('whatever')->table('name') return a query builder object ready to go, and the name is mandatory.

So how about allowing the table name to be left empty/blank/null? Or maybe skip the table method and provide something like DB::builder() to return the builder without a table.

I suspect the SQL builder or grammar processor will need to be changed to account for there not necessarily being a table to select from.

From what I remember, Oracle got around this problem by providing a single-row table called DUAL, and that helped ensure there was always a table available, even when you were not selecting data from a table.

[judgej]

On 26/09/2014 23:56, Arjay Angeles wrote:

I think you can achieve the results you want by using raw queries.

$data = DB::select( DB::raw('SELECT NOW() AS now') );
return $data[0]->now;

Yes you can do this, but it still returns the selected results, as in my
example, and not a Query\Builder object for extending further, for example when
putting queries together in a UNION.

It is always possible to avoid the query builder by plugging in raw SQL.
However, the object of the query builder is to avoid having to do that. It
provides the ability to build complex queries in a flexible manner and in a
variety of ways.

[GrahamCampbell]

Closing due to no interest.

[andonovn]

Now you can actually use DB::query() which will return you an instance of the builder. Just wanted to say that for anyone else that may come here in the future after googling (like me).

Not sure if that was possible back in 2014 where this issue was opened. And yeah, sorry for posting in outdated issue, just thought I may safe someone else's time

[judgej]

Awesome point @andonovn

Whether this was possible or not in 2014, it was certainly never offered as a solution.