Home / Questions / 390103
Accepted
Sonia Toledo
Asked: 2020-09-17 12:37:52 +0800 CST 2020-09-17 12:37:52 +0800 CST

Subqueries in Laravel return no results

  • 772

I'm already desperate thinking about why I can't get any results from the sql subqueries in LARAVEL. this is my code

$Sql= "SELECT cuenta_judicial.IDNRO,demandas2.CI, 
                (SELECT TITULAR from demandado 
                where demandado.CI=demandas2.CI 
                OR (demandado.CI is null 
                AND demandado.IDNRO=demandas2.CI) ) AS TITULAR,
            demandas2.DEMANDA, 
            demandas2.CTA_BANCO, 
            (SELECT sum( mov_cta_judicial.IMPORTE) 
            from mov_cta_judicial 
            where mov_cta_judicial.CTA_JUDICIAL= cuenta_judicial.IDNRO and mov_cta_judicial.TIPO_MOVI='E' 
                AND mov_cta_judicial.TIPO_EXT='C') as CAPITAL,
            (SELECT sum( mov_cta_judicial.IMPORTE) from mov_cta_judicial where mov_cta_judicial.CTA_JUDICIAL= cuenta_judicial.IDNRO and mov_cta_judicial.TIPO_MOVI='E' and mov_cta_judicial.TIPO_EXT='L') as LIQUIDACION ,
            (SELECT sum( mov_cta_judicial.IMPORTE) from mov_cta_judicial where mov_cta_judicial.CTA_JUDICIAL= cuenta_judicial.IDNRO and mov_cta_judicial.TIPO_MOVI='D') AS DEPOSITO 
            from cuenta_judicial,demandas2 
            WHERE demandas2.CTA_BANCO=cuenta_judicial.CTA_JUDICI";

 
 $lista= DB::select($Sql);

I execute that same query in PHPmyAdmin and it returns all the records. But when wanting to do it in LARAVEL NOTHING. Does anyone know why? what should I do?

NO problems when I run it like this: enter image description here

But in laravel nothing

php

2 Answers

  1. Best Answer

    First of all, I really appreciate the comments from those who reacted to my post. After much trying, I decided to modify the sql statement slightly like this, and it worked for me:

    SELECT cuenta_judicial.IDNRO,demandas2.CI, 
(SELECT TITULAR from demandado where demandado.CI=demandas2.CI OR 
(demandado.CI is null AND demandado.IDNRO=demandas2.CI)  limit 1) AS TITULAR,
demandas2.DEMANDA,demandas2.CTA_BANCO, 
(SELECT sum( mov_cta_judicial.IMPORTE) from mov_cta_judicial 
 where mov_cta_judicial.CTA_JUDICIAL= cuenta_judicial.IDNRO and mov_cta_judicial.TIPO_MOVI='E' AND mov_cta_judicial.TIPO_EXT='C' limit 1) as CAPITAL,
(SELECT sum( mov_cta_judicial.IMPORTE) from mov_cta_judicial where mov_cta_judicial.CTA_JUDICIAL= cuenta_judicial.IDNRO and mov_cta_judicial.TIPO_MOVI='E' and mov_cta_judicial.TIPO_EXT='L' limit 1) as LIQUIDACION ,
(SELECT sum( mov_cta_judicial.IMPORTE) from mov_cta_judicial where mov_cta_judicial.CTA_JUDICIAL= cuenta_judicial.IDNRO and mov_cta_judicial.TIPO_MOVI='D' limit 1) AS DEPOSITO from cuenta_judicial,demandas2 WHERE demandas2.CTA_BANCO=cuenta_judicial.CTA_JUDICI

    The only difference from the initial statement is the limit 1, which I have added to each subquery. For some reason Laravel (or some other factor I'm not aware of) requires to explicitly limit the result of a subquery (even though in my case, I knew that each subquery returned only one record and not several).

    • 1

  2. You are missing a return;when in a controller to make a query, you have to return the query. Example:

    $lista= DB::select('SELECT cuenta_judicial.IDNRO,demandas2.CI, 
            (SELECT TITULAR from demandado 
            where demandado.CI=demandas2.CI 
            OR (demandado.CI is null 
            AND demandado.IDNRO=demandas2.CI) ) AS TITULAR,
        demandas2.DEMANDA, 
        demandas2.CTA_BANCO, 
        (SELECT sum( mov_cta_judicial.IMPORTE) 
        from mov_cta_judicial 
        where mov_cta_judicial.CTA_JUDICIAL= cuenta_judicial.IDNRO and mov_cta_judicial.TIPO_MOVI='E' 
            AND mov_cta_judicial.TIPO_EXT="C") as CAPITAL,
        (SELECT sum( mov_cta_judicial.IMPORTE) from mov_cta_judicial where mov_cta_judicial.CTA_JUDICIAL= cuenta_judicial.IDNRO and mov_cta_judicial.TIPO_MOVI="E" and mov_cta_judicial.TIPO_EXT="L") as LIQUIDACION ,
        (SELECT sum( mov_cta_judicial.IMPORTE) from mov_cta_judicial where mov_cta_judicial.CTA_JUDICIAL= cuenta_judicial.IDNRO and mov_cta_judicial.TIPO_MOVI="D") AS DEPOSITO 
        from cuenta_judicial,demandas2 
        WHERE demandas2.CTA_BANCO=cuenta_judicial.CTA_JUDICI');

return response()->json([
    'status' => 'success',
    'data'   => [$lista]
  ]);

#:: O puedes usar
return response($lista);

    I just highlighted that you already know that you have to import the DB in the controller.

    use DB;
    • 1