We help IT Professionals succeed at work.

Could you point how to adjust this Laravel Eloquent query to remove the  `  at   `2019`  ?

Eduardo Fuerte
Eduardo Fuerte asked
on
Hi Experts

Could you point how to adjust this Laravel Eloquent query to remove the  ` at `2019`?

Accordingly with:
 $metas = XXXXXXX
            ::rightjoin(\DB::raw('(
                SELECT
                    distribuidores.id as distribuidor_id
                    ,distribuidores.cnpj
                    ,distribuidores.razaosocial
                    ,meses.mes
                    ,meses.nomeMes
                    ,meses.abreviatura
                    ,periodos.id as periodo_id
                    ,periodos.mes as periodo_mes
                    ,periodos.ano
                FROM distribuidores, meses, periodos
            ) as temp'), function($join) use($ano){
                $join->on('distribuidormetas.distribuidor_id', 'temp.distribuidor_id')
                     ->on('distribuidormetas.periodo_id', 'temp.periodo_id')
                     ->on('temp.mes', 'temp.periodo_mes')
                     ->on('temp.ano', (int)$ano);
            })
            ->where(function($query) use($distribuidor_id){
                if(!empty($distribuidor_id)){
                    $query->where('temp.distribuidor_id', \DB::raw($distribuidor_id));
                }
            })
            ->orderBy('temp.razaosocial', 'asc')
            ->orderBy('temp.mes', 'asc')
            ->select([
                'temp.distribuidor_id',
                'temp.cnpj',
                'temp.razaosocial',
                'temp.mes',
                'temp.nomeMes',
                'temp.abreviatura as mesAbreviado',
                \DB::raw('ifnull(distribuidormetas.valor, 0) as meta')
            ])
            ->distinct()
            ->toSql();

Open in new window





The problem remains

Produces:

select distinct `temp`.`distribuidor_id`, `temp`.`cnpj`, `temp`.`razaosocial`, `temp`.`mes`, `temp`.`nomeMes`, `temp`.`abreviatura` as `mesAbreviado`, ifnull(distribuidormetas.valor, 0) as meta from `distribuidormetas` right join (
                SELECT
                    distribuidores.id as distribuidor_id
                    ,distribuidores.cnpj
                    ,distribuidores.razaosocial
                    ,meses.mes
                    ,meses.nomeMes
                    ,meses.abreviatura
                    ,periodos.id as periodo_id
                    ,periodos.mes as periodo_mes
                    ,periodos.ano
                FROM distribuidores, meses, periodos
            ) as temp on `distribuidormetas`.`distribuidor_id` = `temp`.`distribuidor_id` and `distribuidormetas`.`periodo_id` = `temp`.`periodo_id` and `temp`.`mes` = `temp`.`periodo_mes` and `temp`.`ano` = `2019` order by `temp`.`razaosocial` asc, `temp`.`mes` asc

Open in new window



Even changing to:

 //->on('temp.ano', (int)$ano);
 ->on('temp.ano', 2019);

Open in new window


The problem remains.

Thanks in advance!
Comment
Watch Question

View Solution Only

Chris StanyonWebDev
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hey Eduardo,

Have you tried it using DB::raw

->on('temp.ano', DB::raw($ano));
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Hi Chris

By doing this an error is generated:

img001
and 

  info($metas);

Open in new window


is not presented.
WebDev
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Ah sorry. I'd assumed you'd already imported the DB NameSpace. Seems that you haven't so just add the backslash to it:

->on('temp.ano', \DB::raw($ano));
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Perfectly!!!

select distinct `temp`.`distribuidor_id`, `temp`.`cnpj`, `temp`.`razaosocial`, `temp`.`mes`, `temp`.`nomeMes`, `temp`.`abreviatura` as `mesAbreviado`, ifnull(distribuidormetas.valor, 0) as meta from `distribuidormetas` right join (
                SELECT
                    distribuidores.id as distribuidor_id
                    ,distribuidores.cnpj
                    ,distribuidores.razaosocial
                    ,meses.mes
                    ,meses.nomeMes
                    ,meses.abreviatura
                    ,periodos.id as periodo_id
                    ,periodos.mes as periodo_mes
                    ,periodos.ano
                FROM distribuidores, meses, periodos
            ) as temp on `distribuidormetas`.`distribuidor_id` = `temp`.`distribuidor_id` and `distribuidormetas`.`periodo_id` = `temp`.`periodo_id` and `temp`.`mes` = `temp`.`periodo_mes` and `temp`.`ano` = 2019 order by `temp`.`razaosocial` asc, `temp`.`mes`

Open in new window

Chris StanyonWebDev
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Happy Days :)
Eduardo FuerteDeveloper and Analyst

Author

Commented:
Chris


Yes!

Thank you for another solution.

It seens that Eloquent has a lot of intricate tricks....