Eloquent, how to get calculated sum of fields in a new field

Question

I have a DB table students with all the subjects final grades for each subject what is quite a number, and a number of other fields, like name, etc.

students (table is having more than 100 columns, each cell can have some pre-set ID, which later is translated to grades /some silly anti-hacking technique, where several numbers can represent the same grade/)

|| name  || surname|| section || math1||math 2||math 16|| physics 1||physics 2 ||... ||
|| Jonah || Smith  ||  A4     || 17   || 19   || 0     || 193      ||          ||    
|| John  ||Doe     ||  A3     ||  0   || 0    || 34    ||12        ||  0       || ...||
|| Jane  ||Doe     ||  A3     ||  0   || 0    || 48    ||12        ||  154     || ...||
|| Martin||Doe     ||  A3     || 17   || 34   || 96    ||10        || 225      || ...||

DESIRABLE RESULT

|| avg.grade || name  || surname|| section || math1||math 2||math 16|| physics 1||physics 2||... ||
|| 0.92      || John  ||Doe     || A3      ||0   || 0    ||...    ||12        || 0       || ...||
|| 0.81      || Jane  ||Doe     || A3      ||0   || 0    ||...    ||12        || 154     || ...||

There are literally NO other related tables, except for

• auxiliary in memory table with grades to IDs representation
• subjects and students' data

I want to get selected (using Laravel eloquent model) all the fields AND 'final grade', what is summ for all the selected ... above some threshold. (The presented below

SELECT *, a FROM `students` WHERE 
`name` LIKE "J%" AND `surname` LIKE "D" AND `section` = 'A3'
enter code here
AND (if(`math16`='12',0.76, 0)
+if(`geometry26`='13',0.76, 0) +if(`physics13`='325',1, 0)
+if(`programming06`='551',1, 0) +if(`biology18`='271',0.916, 0) 
+ .... )/18.216 as a > 0.75

'12','13','25','551','271' - are codes of grades.

Technically, I need to get all the students, who got more or less the same courses with some average grades for a given starting letter of name. (I mean, If I have John, who took specific courses [math16 with grade 0.76,biology18 with grade 0.91,...] and Jane [math16 with grade 0.76,programming06 with grade 100,...], I'd like to get their records together, with a sum of their grades, without the rest of students.

Probably, there is some other possibility to get all the rows with somehow identical fields together, but I cannot get how can I do so. Thanks

Answer 1

I would not recommend keeping the subjects in the students table, instead you should explore normalizing your database a bit further and adding a subjects and student_subject table. However with your current setup, you could consider creating the attribute on the model, and then appending it.

https://laravel.com/docs/5.7/eloquent-serialization#appending-values-to-json

class User {

    protected $appends = [
        'average_grade'
    ];

    public function getAverageGradeAttribute()
    {
        return $this->attributes['average_grade'] = ($this->math1 + $this->math2) / 2;
    }

}

Doing so will allow you to simply call the attribute while displaying it in your view, like so:

<table>
    <thead>
        <tr>
            <th>Student</th>
            <th>Average Grade</th>
        </tr>
    </thead>
    <tbody>
        @foreach($students as $student)
            <tr>
                <td>{{ $student->name }}</td>
                <td>{{ $student->average_grade }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

Answer 2

You can do the following to achieve your purpose. Assuming you want to get the sum total of individual summations of 2 fields in the table where the field names are field1 and field2 for the table whose model name is 'ModelName' then you can do the following:

$amount = ModelName::select(DB::raw('sum(field1 + field2) as total'))->get();