Avatar of Eduardo Fuerte
Eduardo Fuerte
Flag 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!
LaravelPHP

Avatar of undefined
Last Comment
Eduardo Fuerte

8/22/2022 - Mon
Chris Stanyon

Hey Eduardo,

Have you tried it using DB::raw

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

ASKER
Hi Chris

By doing this an error is generated:

img001
and

  info($metas);

Open in new window


is not presented.
ASKER CERTIFIED SOLUTION
Chris Stanyon

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Eduardo Fuerte

ASKER
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

Your help has saved me hundreds of hours of internet surfing.
fblack61
Chris Stanyon

Happy Days :)
Eduardo Fuerte

ASKER
Chris


Yes!

Thank you for another solution.

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