Link to home
Start Free TrialLog in
Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil

asked on

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

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!
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Hey Eduardo,

Have you tried it using DB::raw

->on('temp.ano', DB::raw($ano));
Avatar of Eduardo Fuerte

ASKER

Hi Chris

By doing this an error is generated:

User generated image
and

  info($metas);

Open in new window


is not presented.
ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Happy Days :)
Chris


Yes!

Thank you for another solution.

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