Problem to convert a SQL query to an Laravel Query Builder

Question

I want to convert this SQL Query to LAravel SQL Builder but i don't know why I already read the laravel documentation but I can't find the answer

SQL Query:

SELECT
    CONTRATO,
    CASE WHEN RAZONSOCIAL IS NULL OR RAZONSOCIAL = ''
        THEN ISNULL(PATERNO, '') + ' ' + ISNULL(MATERNO, '') + ' ' + ISNULL(NOMBRES, '') 
        ELSE RAZONSOCIAL
    END AS NOMBRE,
    CADENAUNICA,
    CLAVERASTREO,
    FECHAASIENTO,
    IDGARANTIA,
    RutaGuardadoPDF,
    RutaGuardadoXML
FROM
    DEUDORES AS D
LEFT JOIN
    RELACIONES AS R
    ON D.RUGID = R.RUGID
WHERE CONTRATO = 'A1412015'
AND D.AcreedorId IN (
    SELECT
        ACREEDORID
    FROM
        [MasterCLI].[dbo].[Rrug]
    WHERE FIDEICOMISOID = 5
)

and i do this code in laravel

$AcreedorFide = \DB::connection('sqlsrv')
    ->table('Rrug')
    ->select('AcreedorID')
    ->where('Fideicomiso', $fideicomiso)
    ->get();

$BoletaRUG = \DB::connection('RUG')
    ->table('Deudores')
    ->select(
        'Contrato',
        'CadenaUnica',
        'ClaveRastreo',
        'FechaAsiento',
        'IdGarantia',
        'RutaGuardadoPDF',
        'RutaGuardadoXML'
    )
    ->select(DB::raw('CASE WHEN RAZONSOCIAL IS NULL'))
    ->leftJoin('Relaciones','Deudores.RugId', '=', 'Relaciones.RugId')
    ->where('Contrato', $Contrato)
    ->wherein('Acreedorid', $AcreedorFide)
    ->get();

Answer 1

The most SQL-like way of remaking this query in the Query Builder is the following:

DB::connection('sqlsrv')->query()
    ->select(
        'CONTRATO',
        'CADENAUNICA',
        'CLAVERASTREO',
        'FECHAASIENTO',
        'IDGARANTIA',
        'RutaGuardadoPDF',
        'RutaGuardadoXML'
    )
    ->selectRaw(
        "CASE WHEN RAZONSOCIAL IS NULL OR RAZONSOCIAL = '' ".
            "THEN ISNULL(PATERNO, '') + ' ' + ISNULL(MATERNO, '') + ' ' + ISNULL(NOMBRES, '') ". 
            "ELSE RAZONSOCIAL ".
        "END AS NOMBRE"
    )
    ->from('DEUDORES', 'D')
    ->leftJoin('RELACIONES AS R', 'D.RUGID', '=', 'R.RUGID')
    ->where('CONTRATO', '=', 'A1412015')
    ->whereIn('D.AcreedorId', function ($query) {
        $query->select('ACREEDORID')
              ->from('MasterCLI.dbo.Rrug')
              ->where('FIDEICOMISOID', '=', 5);
    })
    ->get();

For anyone stuck on translating queries, as the comments have stated, the easiest way to remake large, complex-looking queries in the builder is by dumping the query using ->toSql() instead of ->get() and checking if it's missing something or not. Trial and error.

My personal advice is to start from the subqueries and work your way up. Also, Some differences/shorthands:

• Instead of DB::connection(...)->query()->select(...)->from('DEUDORES', 'D')->where(...),
  you can specify the main table you're querying first:
  DB::connection(...)->table('DEUDORES', 'D')->select(...)->where(...)
• If the comparison operator is '=' you can leave it implied. In this example:
  • leftJoin('RELACIONES AS R', 'D.RUGID', '=', 'R.RUGID')
    becomes leftJoin('RELACIONES AS R', 'D.RUGID', 'R.RUGID')
  • where('CONTRATO', '=', 'A1412015') becomes where('CONTRATO', 'A1412015')
• If you use the same connection as the default one (DB_CONNECTION in your .env file), you can skip the connection(...) in the queries. DB::connection(...)->table(...) becomes DB::table(...).