CODEWITHSUNDEEP
laravel
Brendan
Brendan

Reputation: 109

WhereIN data for calculations for displaying results

I am trying to work out the best practice to calculate Avg Attendance.

I would like to show this on the Member View (html part is no issue)

I have the following 3 databases

Members DB

|id|first|last|..........
|1 |.....................
|2 |.....................

Rolls DB

|id|roll_id|member_id|status|
|1 |1      |1        |P     |
|2 |1      |2        |V     |
|3 |2      |1        |A     |
|4 |2      |2        |C     |

Rollmappings DB

|id|roll_date |roll_year|
|1 |2019-03-01|2019     |
|2 |2019-03-02|2019     |

I have the following in my Member Model

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

I have the following in my Roll Model

public function rollmapping()
{
    return $this->hasOne('App\Rollmapping', 'id','roll_id');
}

I would like to count all records which does not equal "A" So in the code blocks above Member 1 will needs to show me 50% attendance, and member 2 will show me 100% attendance.

I would need to have something in place which will auto roll over when we move into 2020 I do Have Carbon Date installed with the app as well

I got stuck on the link on pulling the year_roll into the select for the Roll and to count I can use:

$rollyear = Rollmapping::lastest()->value('roll_year');
$totalweeks = DB::table('rollmappings')
    ->where('roll_year', '=', $rollyear)
    ->get()
    ->count();

But I am stuck on on pulling the correct records from the roll using the roll_id as I only want the roll_id which have a year date of the current year.

Thanks

Upvotes: 0

Views: 36

Answers (1)

Rwd
Rwd

Reputation: 35200

If I understand you're question correctly then you should be able to get what you need using whereHas

$count = Roll::whereHas('rollmapping', function ($query) {
    $query->whereYear('roll_date', now()->year);
})->count();

If you would also like to exclude all the rolls where the status does not equal A then you would have something like:

$count = Roll::whereHas('rollmapping', function ($query) {
    $query->whereYear('roll_date', now()->year);
})
    ->where('status', '!=', 'A')
    ->count();

Upvotes: 1

Related Questions