Laravel 4.2 Query to Database

Eloquent is reallt good and very powerful but sometimes its just better to write the query yourself. With that in mind I'd look into using DB::raw()

Check out the documentation https://laravel.com/docs/4.2/queries#unions

You can do as https://laravel.com/docs/4.2/queries#unions suggests:

$first = DB::table('users')->whereNull('first_name');

$users = DB::table('users')->whereNull('last_name')->union($first)->get();

For what you are trying to do a raw query will be faster. So you can use your query as is and using DB::raw() to achieve this, like so:

$query  = DB::(table)
    ->select(DB::raw("SELECT * FROM ( SELECT ce.empresa_id, CONCAT(ce.nombre, ' ',      ce.apellido) AS nombre, ce.grupo_contable FROM cliente_empresa AS ce UNION SELECT cc.empresa_id, cc.nombre, cc.grupo_contable FROM cuenta_contable AS cc UNION SELECT cci.empresa_id, cci.grupo_iva AS nombre, cci.cuenta_contable AS grupo_contable FROM cuenta_contables_iva AS cci ) AS cuentasContables WHERE cuentasContables.empresa_id = 1 AND (cuentasContables.nombre LIKE '%a%' OR cuentasContables.grupo_contable LIKE '%100%')"))
    ->get()

Let us know.

Thank you both for your answers, I have tried with DB::Raw like this:

$type = DB::raw("SELECT * FROM (SELECT '1' as tipo, ce.id, ce.empresa_id, CONCAT(ce.nombre, '', ce.apellido) AS nombre, ce.grupo_contable FROM cliente_empresa AS ce UNION SELECT '2' as tipo, cc.id, cc.empresa_id, cc.nombre, cc.grupo_contable FROM cuenta_contable AS cc UNION SELECT '3' as tipo, cci.id, cci.empresa_id, cci.grupo_iva AS nombre, cci.cuenta_contable AS grupo_contable FROM cuenta_contables_iva AS cci) AS cuentasContables WHERE cuentasContables.empresa_id = {$this->empresa->id} AND (cuentasContables.grupo_contable LIKE {$grupoContable})")->get();

And I get the message:

Call to undefined method Illuminate\Database\Query\Expression::get()

Why is that? Thank you.

I believe you should use this:

DB::table('target table')->select(DB::raw('insert big query here'))->get();

Thank you Hawkleaf, the problem is that I'm not targeting a specific table. As shown in the big query, there are 3 table options (cliente_empresa, cuenta_contable, cuenta_contables_iva) and depending on the input the method obtains, a query is made to ONE of those 3 options.

Something like:

Tables CuentaTypes:

• cliente_empresa
• cuenta_contable
• cuenta_contable_iva

Search in CuentaTypes where the input is stored. Once found, get THESE lines in the table

• tipo(type),
• id,
• empresa_id,
• nombre(name) + apellido(lastname)
• grupoContable

I don't know what you are trying to achieve. You may be try relationships there. Easy to access.

Have you tried using the UNION?