Use `AND` in Laravel Query Builder, syntax error (orOn)

Question

I have this sql (MariaDB) query, it works ok:

SELECT
    admin_combustibles.nombre,
    admin_combustibles.id_combustible,
    admin_combustible_unidades.nombre AS unidades,
    admin_tipo_combustibles.nombre AS tipo_combustible,
    admin_indice_combustibles.valor_combustible
FROM
    admin_combustibles
INNER JOIN admin_combustible_unidades ON admin_combustibles.combustible_unidades_id = admin_combustible_unidades.id_combustible_unidad
INNER JOIN admin_tipo_combustibles ON admin_combustibles.tipo_combustible_id = admin_tipo_combustibles.id_tipo_combustible
LEFT  JOIN admin_indice_combustibles ON admin_combustibles.id_combustible = admin_indice_combustibles.combustible_id
AND admin_indice_combustibles.anio = 1992
AND admin_indice_combustibles.mes = 6
ORDER BY
    admin_combustibles.nombre

But when I try to make it through Laravel, I have some error because Laravel put some on the number 1992, in that way the query doesn't work.

Plus, I don't know how to do the AND on Laravel, I only have orOn but it doesn't work:

$datos = AdminCombustible::select([
    'admin_combustibles.nombre',
    'admin_combustibles.id_combustible',
    'admin_combustible_unidades.nombre AS unidades',
    'admin_tipo_combustibles.nombre AS tipo_combustible',
    'admin_indice_combustibles.valor_combustible'])
    ->join('admin_combustible_unidades', 'admin_combustibles.combustible_unidades_id', '=', 'admin_combustible_unidades.id_combustible_unidad')
    ->join('admin_tipo_combustibles', 'admin_combustibles.tipo_combustible_id', '=', 'admin_tipo_combustibles.id_tipo_combustible')

    ->leftJoin('admin_indice_combustibles', function ($join) {
        $join->on('admin_indice_combustibles.combustible_id', '=', 'admin_combustibles.id_combustible')->orOn('admin_indice_combustibles.anio', '=', 1992);
    })
    ->get();
    dd($datos);

I got an sql syntax error.

Any help on how to make that query with query builder of Laravel? (I put the entire query as DB::raw and it works, but I don't want to use raw() )

This could help - [Laravel Eloquent inner join with multiple conditions](https://stackoverflow.com/a/38695821/3226121) — ljubadr, Oct 25 '17 at 21:31

score 6 · Accepted Answer · answered Oct 25 '17 at 21:29

I think there might be two problems here. Since you are using and in your original query, you might be needing to use or instead of orOn on your join.

Also you will need to use DB::raw() on the parameters, otherwise Laravel will consider them columns....

$join->on('admin_indice_combustibles.combustible_id', '=', 'admin_combustibles.id_combustible')->on('admin_indice_combustibles.anio', '=', DB::raw('1992'));

Use `AND` in Laravel Query Builder, syntax error (orOn)

1 Answers1