How to make a subquery in laravel eloquent with WHERE

Question

How to make a subquery in laravel eloquent with WHERE

Navigation

#1 by (-2 votes)

2

Hello, I'm using laravel 5.6 and I'm creating a query query using the eloquent laravel, but I'm having trouble creating a subquery

What I want to create is simple in SQL server:

    ,DT_ALTA = (SELECT TOP(1) 
                        DH_DESOSPITALIZACAO 
                    FROM 
                        TB_CAPTA_EVO_STATUS_CAES AS CAES 
                    WHERE 
                        (CAES.ID_CD_INTERNACAO = CAIN.ID_CD_INTERNACAO
                        AND  FL_DESOSPITALIZACAO IN (4, 5, 6, 16, 19) )
                        OR (CAES.ID_CD_INTERNACAO = CAIN.ID_CD_INTERNACAO
                        AND   FL_FINALIZACAO = 'S')
                    ORDER BY CAES.DH_DESOSPITALIZACAO DESC)

However, I've been struggling with eloquent, my query:

  $mot = db::table('TB_CAPTA_INTERNACAO_CAIN as CAIN')
                            ->join('TB_CRM_PESSOAS_CRMP as CRMP','CAIN.ID_CD_PACIENTE','=','CRMP.ID_CD_PESSOA')
                            ->join('TB_CAPTA_CFG_ORIGEM_CAGO as CAGO','CAIN.ID_CD_ORIGEM','=','CAGO.ID_CD_ORIGEM')
                            ->join('TB_CRM_PACIENTES_CRPC as CRPC','CRMP.ID_CD_PESSOA','=','CRPC.ID_CD_PESSOA')
                            //->join('TB_CAPTA_EVO_STATUS_CAES as CAES','CAES.ID_CD_INTERNACAO','=','CAIN.ID_CD_INTERNACAO')
                            ->select('CAIN.ID_CD_INTERNACAO','CAIN.DH_ADMISSAO_HOSP','CAIN.DH_INICIO_ACOMPANHAMENTO','CRMP.NM_PESSOA','CRMP.NM_SOBRENOME','CAGO.DS_TITULO','CRPC.NR_CREDENCIAL','CRMP.DH_NASCIMENTO','CRMP.ID_CD_PESSOA')
                            ->where('IN_SITUACAO',$request->get('situacao'));

If anyone can help me, I'm grateful.

I was able to solve it, it follows the resolution code:

->addSelect(db::raw("(SELECT TOP 1 CAES.DH_DESOSPITALIZACAO FROM TB_CAPTA_EVO_STATUS_CAES as CAES WHERE CAES.ID_CD_INTERNACAO = CAIN.ID_CD_INTERNACAO) as DH_DESOSPITALIZACAO" ))

I've added this snippet to eloquent, it does the right subquery.

php sql laravel

asked by anonymous 13.09.2018 / 15:28

1 answer

Return to latest Date by column? Function has real-time screen, lock the screen with time!

score -2 · Answer 1

Use this as a reference.

   $q->where('campo1', function($q) use ($campo2)
    {
       $q->from('tabela')
        ->selectRaw('min(campo1)')
        ->where('campo1', '>=', $campo2)
        ->where('campo3', $campo3);
    })->orderBy('campo1', 'desc');