Doubt with sql in laravel?

1

Today I have a query that is returning me duplicate data, I made the sql change more than needed to change it.

How can I leave in the same structure as select (DB :: raw?

The current code looks like this:

    $atividades = $this->atividade->select(DB::raw
        ('sum(num_total_pessoas) as total_pessoas,
          sum(convite_prod) as total_convite_prod,
          sum(convite_apaa) as total_convite_apaa,
          sum(educativo_producao) as total_educativo_producao,
          sum(educativo_apaa) as total_educativo_apaa,
          sum(atend_social_producao) as total_atend_social_producao,
          sum(atend_social_apaa) as total_atend_social_apaa,
          MONTH(data) as data2,
          data_fim,plano_id,
          programa_id,
          tipo_publico_id'))->
    where('plano_id',$id)->
    groupBy('tipo_publico_id','data2','programa_id')->get();

My new query looks like this:

       echo "<hr/>teste de consulta nova <br/>";
   $results = DB::select(' select 
                            sum(a.num_total_pessoas) as total_pessoas,
                            sum(a.convite_prod) as total_convite_prod,
                            sum(a.convite_apaa) as total_convite_apaa,
                            sum(a.educativo_producao) as total_educativo_producao,
                            sum(a.educativo_apaa) as total_educativo_apaa,
                            sum(a.atend_social_producao) as total_atend_social_producao,
                            sum(a.atend_social_apaa) as total_atend_social_apaa,
                            MONTH(a.data) as data2,
                            a.data_fim,plano_id,
                            a.programa_id,
                            a.tipo_publico_id
                            from atividades a 
                            group by a.tipo_publico_id');
   var_dump($results);
    
asked by anonymous 20.01.2017 / 19:21

1 answer

1

Just set the group by of the previous query, you do not need to remove DB :: raw.

$atividades = $this->atividade->select(DB::raw
    ('sum(num_total_pessoas) as total_pessoas,
      sum(convite_prod) as total_convite_prod,
      sum(convite_apaa) as total_convite_apaa,
      sum(educativo_producao) as total_educativo_producao,
      sum(educativo_apaa) as total_educativo_apaa,
      sum(atend_social_producao) as total_atend_social_producao,
      sum(atend_social_apaa) as total_atend_social_apaa,
      MONTH(data) as data2,
      data_fim,plano_id,
      programa_id,
      tipo_publico_id'))->
where('plano_id',$id)->
groupBy('tipo_publico_id')->get(); // alteração foi feita aqui
    
20.01.2017 / 19:29