Where affecting only one field in select

Asked

Viewed 71 times

0

Good morning, how do I make my Where clause affect only one (1) field in my search? For example:

$query = DB::table('proventosdesc as proven')
            ->join('calculo_rh as calc', 'proven.pessoaId', '=', 'calc.pessoaId')
            ->join('pessoa', 'calc.pessoaId', '=', 'pessoa.id')
            ->select('pessoa.nome as a', 'calc.cargo as b', 'calc.departamento as c',
                    DB::raw("SUM(proven.valor) as d"))
            ->groupby('proven.pessoaId');

I want to leave a condition only for the last field "proven.valor" imposing only the return of type P, serious something like this:

$query = DB::table('proventosdesc as proven')
            ->join('calculo_rh as calc', 'proven.pessoaId', '=', 'calc.pessoaId')
            ->join('pessoa', 'calc.pessoaId', '=', 'pessoa.id')
            ->select('pessoa.nome as a', 'calc.cargo as b', 'calc.departamento as c',
                    DB::raw("SUM(proven.valor) as d")->where('proven.tipo, '=', 'P')
            ->groupby('proven.pessoaId');

This way it doesn’t work, passing the Where internally in the given field.

I didn’t want this condition to affect all select but only this last field, because I will need to make another condition the same way for another field of the same select... Could someone please help me? I am willing to offer any information if it was not clear the doubt !

mysql laravel laravel-5 select-sql

  • Want only the last row of this query returned?

  • No, I want my entire select to work, but with conditions only for one field and not for the entire select, if I put the Where clause at the end, before or after the groupby would work perfectly, but then I would take the whole select, I want it to work only for the last field.

  • Want to grab everything but then treat the last item of the array separately, already with php? That’s how I would do

  • I edited there, thank you very much Miguel !

  • You must answer your own question and ask accepted when you can. This makes it easier for other colleagues to identify the solution on this page. Congratulations

1 answer

1

I got it, the way to solve it is by doing another select internally, staying like this:

->select('pessoa.nome as a', 'calc.cargo as b', 'calc.departamento as c',
                    DB::raw("(Select SUM(valor) From proventosdesc where pessoaId = calc.pessoaId and tipo = 'P') as d"))
            ->groupby('proven.pessoaId');

Browser other questions tagged mysql laravel laravel-5 select-sql

You are not signed in. Login or sign up in order to post.