where affecting only one field in select

Question

where affecting only one field in select

Navigation

#1 by (1 votes)

0

Good morning, how do I make my 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.value" imposing only the return of type P, would be 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');

That way it does not work, passing the where internally in the given field.

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

mysql sql-select laravel laravel-5

asked by anonymous 24.05.2016 / 14:21

1 answer

Problems when querying a table by a webservice Bring Records That Do not Have - 3rd Table

score 1 · Answer 1

I managed, the way to solve it is to make another select internally, thus:

->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');