How to do a subquery in eloquent Laravel with WHERE

Question

Hello, I am using Laravel 5.6 and I am creating a query using eloquent Laravel, however I am having difficulty 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 via Eloquent I’m having difficulties, follow 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 appreciate it from now on.

I got it solved, follow 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 added this passage in eloquent, it makes the subquery right.

Answer 1

If I have, your difficulty is in putting together a consultation of the type:

WHERE (condicao1 AND condicao2) OR (condicao3 AND condicao4)

Another detail is that condicao1 and condicao3 are the same, so you could do:

WHERE condicao1 AND (condicao2 OR condicao4)

But it turns out that in condicao1 you need to compare one column with another, so I would do it this way:

$consulta->whereRaw('CAES.ID_CD_INTERNACAO = CAIN.ID_CD_INTERNACAO')
   ->where(function($query) {
      return $query->where(condicao2)->orWhere(condicao4);
})

And in condition2 and condition4 you pass the parameters [atributo do modelo], operador lógico and [valor a comparar]. If you need to use pure SQL clauses, use whereRaw' ou 'orWhereRaw.

Answer 2

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');