Count through two Models

I have three tables, settlements table hasMany add_members table and ciphers table hasMany add_members table. In Settlement and Cipher Model I put function:

public function members()
    {
        return $this->hasMany('App\AddMember');
    }

so now, when I need count how many rows in add_members table I have but with reon_id in settlements table I did like this:

$first_count = Settlement::where('town_id', Auth::user()->town_id)
  ->with('members')
  ->where('reon_id', '1')
  ->count();

and that working, but now I need second count which also counting add_members rows with reon_id == 1 in settlements table but woth one more relationships where cipher == 0 in ciphers table. If I do with belongsTo relationship through AddMember Model I'm getting error:

(2/2) QueryException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'reon_id' in 'where clause'

$second_count = Settlement::where('town_id', Auth::user()->town_id)
      ->with('members')
      ->where('reon_id', '1')
      ->Cipher::with('members')
      ->where('cipher', '0')
      ->count();

I know that this second is wrong but I don't know hot to get with reon_id == 1 in Settlement model and cipher == 0 in Cipher model...

Talinon

8 years ago

Level 51

Give this a try:

$count = Settlement::where('town_id', Auth::user()->town_id)
    ->with('members', 'ciphers')
    ->where('reon_id', '1')
    ->get()
    ->sum(function ($settlements) {
        return count($settlements->members) +
        $settlements->ciphers->where('cipher', '=', 0)->count();
    });

shone83

8 years ago

Level 1

I get: (1/1) RelationNotFoundException Call to undefined relationship [ciphers] on model [App\Settlement].

Settlement and Cipher model are not connected. They only connected with AddMember Model. add_member table has settlement_id and cipher_id.

suhail

8 years ago

Level 1

I think you may need to rethink the relationships, or maybe the details in the question are not complete!?

Can a user be a member so you have user_id in the addMember model? Has a member a settlement?

I think you also need to add the table name in the where clause :

  ->with('members')
  ->where('members.reon_id', '1') // notice the table name before the field 
  ->count();

shone83

8 years ago

Level 1

I think that details are complete, and relationships maybe need to work in different way I don't know.

Anyway, user are too connected with a member but that's not important to this problem. I'm not suppose to add code with town_id but I forget to delete.

Tables are like this:

add_member: id settlement_id cipher_id name last_name etc...

settlements: id reon_id name

Settlement model has:

public function members()
    {
        return $this->hasMany('App\AddMember');
    }

ciphers: id cipher name

Cipher model has:

public function members()
    {
        return $this->hasMany('App\AddMember');
    }

and I need a number on add_member rows where reon_id is 1 which I get like this:

$first_count = Settlement::where('town_id', Auth::user()->town_id)
  ->with('members')
  ->where('reon_id', '1')
  ->count();

and that working fine. But, I also need a variable with number on add_member rows where reon_id is 1 and where cipher is 0 (cipher column in ciphers table).

Now it have more detail I hope...

Please or to participate in this conversation.