Consultations between tables with Eloquent - Laravel 5

Asked

Viewed 4,496 times

7

Good, I have the following problem when using Laravel 5 in the relationship of tables:

I have these 4 tables (hypothetical names to illustrate the problem):

tabelas

Using the Laravel 5 models I connected them to each other:

Modelo Empresa

public function pessoa(){
  return $this->hasMany('App\Pessoas')
}

Model People

public function trabalho(){
  return $this->hasMany('App\Trabalho')
}
public function empresa(){
  return $this->belongsTo('App\Empresa')
}

Work model

public function custo(){
  return $this->hasMany('App\Custo')
}
public function pessoa(){
  return $this->belongsTo('App\Pessoa')
}

Modelo Custo

public function trabalho(){
  return $this->belongsTo('App\Trabalho')
}

My question is how to make the sum of the cost of all the people in a given company and all the jobs... For example, to know the total cost of a job I do something like:

$trabalho = new Trabalho->find($id)
$custoDoTrabalho = $trabalho->custo->sum('montante')

However, if you do the following:

$pessoas = new Empresa->find($id)->pessoa
$custoDosTrabalhosTodasAsPessoas = $pessoas->trabalho->custo->sum('montante')

Doesn’t work!

To achieve the intended result I have to write many lines (and I am lazy and the project is great and with many cases of these!)

$custo = 0;
$pessoas = new Pessoa->find($id);
$trabalhos = $pessoas->trabalho;
foreach($trabalhos as $trabalho)
      $custo+=$trabalho->custo->sum('montante')

Is there any way to use "Eloquent" relationships for this goal, or do I have to write everything down?

(sorry the text so large... thanks in advance!)

2 answers

3


$pessoas = new Empresa->find($id)->pessoa
$custoDosTrabalhosTodasAsPessoas = $pessoas->trabalho->custo->sum('montante')

That doesn’t work because $pessoas is a list. the right would be $pessoas[0]->trabalho->.... But that wouldn’t solve your problem in general.

To solve your problem of making the sum of the cost of all the people of a given company and of all the jobs, you can do only one consultation:

select sum(custos.montante) from empresas
join pessoas on (pessoas.empresa_id = empresas.id)
join trabalho on (trabalho.pessoa_id = pessoas.id)
join custos on (custos.trabalho_id = trabalho.id)
where empresas.id = ?

this sql you can run it raw:

DB::statement('teu sql...');

but using eloquent can do so:

Empresa::
     join('pessoas', function($query){
         $query->on('pessoas.empresa_id','=','empresas.id') 
      })
      ->join('trabalho', function($query){
         $query->on('trabalho.pessoa_id','=','pessoas.id') 
      })
      ->join('custos',function($query){
         $query->on('custos.trabalho_id','=','trabalho.id') 
      })
     ->where('empresas.id','=',1)
     ->sum('custos.montante')

another way:

DB::table('empresas')
         ->join('pessoas', 'pessoas.empresa_id', '=', 'empresas.id')
         ->join('trabalho', 'trabalho.pessoa_id', '=', 'pessoas.id')
         ->join('custos', 'custos.trabalho_id', '=', 'trabalho.id')
         ->where('empresas.id','=',1)
         ->sum('custos.montante');
  • I had already asked this question for a long time, however I have already solved the problem (I don’t remember how), but I liked your solution and it seems to me the most correct! Thank you for the reply!

  • I had already asked this question for a long time, however I have already solved the problem (I don’t remember how), but I liked your solution and it seems to me the most correct! Thank you for the reply!

1

No need to check for each person.. to add up the entire amount of all jobs:

$total = App\Custo::sum('montante');

To sum up the amount per job:

$trabalhoTotal = App\Trabalho::find(1)->custo->sum('montante');

Or:

$trabalhoTotal = App\Custo::where('trabalho_id',1)->sum('montante');
  • Because in this case it would work... but I badly exposed the case (it is incomplete), because linked to the table People there is another (Companies)... When doing: $total = App Cost::sum('amount'); Would take the amount of all people in all companies... I just want the amount of all people in a particular company... I will edit the question!

  • How do you want it to go back? Workoxcustos? Empresaxworkoxcusto? Personxcusto? @Ricardocruz

  • Cost... The sum of all the Costs of all the Works performed by all the People in a given Company.

Browser other questions tagged

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