Get Position subjects in exams by Students using Laravel

Published 7 months ago by kosky2005

I have a table called results with the following columns ID, Reg Number,Name, Subjects,Programmes,Year,Term,score. I want get positions of every subject by student where Programmes = Art and year = 2017 and Term = 1. I need help to achieve the below

ID  Reg Number  Name    Subjects    Programmes  Year    Term    score   Subject Postion
1   1   Emma    English Arts    2017    1   67  2
2   2   Frank   English Arts    2017    1   90  1
3   3   Bright  English Arts    2017    1   67  2
4   4   Mark    English Arts    2017    1   88  3
5   1   Emma    Maths   Arts    2017    1   24  4
6   2   Frank   Maths   Arts    2017    1   78  1
7   3   Bright  Maths   Arts    2017    1   39  3
8   4   Mark    Maths   Arts    2017    1   67  2
9   1   Emma    Science Arts    2017    1   24  3
10  2   Frank   Science Arts    2017    1   68  2
11  3   Bright  Science Arts    2017    1   68  2
12  4   Mark    Science Arts    2017    1   80  1
13  1   Emma    Orals   Arts    2017    1   64  4
14  2   Frank   Orals   Arts    2017    1   77  3
15  3   Bright  Orals   Arts    2017    1   74  2
16  4   Mark    Orals   Arts    2017    1   90  1

The last column which is the Subject_Postion is what i want to achieve.

This the code i have so far $getResults = DB::table('results') ->leftjoin(DB::raw('((SELECT regNumber, score, programmes,term,year, subject_position from (SELECT regNumber, score, programmes,term,year, subject_position, CASE WHEN @prevRank = score THEN @curRank WHEN @prevRank := score THEN @curRank := @curRank + 1 END AS subject_position FROM results p, (SELECT @curRank :=0, @prevRank := NULL) r2

                          where programmes ="'.$getProgramme.'" and  `year` = "'.$getLevel.'" and term = "'.$getTerm.'"
                          and year = "'.$getYear.'" ORDER BY score Desc) as t3 )'), function($joinn)
        {
            $joinn->on('regNumber', '=', 'results.regNumber')                    

        })      
        
        ->where('programmes',$getProgramme)
        ->where('year',$getYear)
        ->where('term',$getTerm)
        ->where('level',$getLevel)
        ->get();
skliche
skliche
7 months ago (156,590 XP)

Please take the time to format your question so we can actually read it, e.g. quote the output: https://help.github.com/articles/basic-writing-and-formatting-syntax/#quoting-code

Also, what have you tried so far and what problems have you stumbled upon?

skliche
skliche
7 months ago (156,590 XP)

Instead of killing yourself with SQL, use Eloquent:

  1. Create an empty model, e.g. Result
  2. Retrieve the data grouped by Subjects
  3. Iterate over the collection, sort descending by Score and calculate the position by iterating over the sorted part.

Incomplete code so you'll have at least some of the fun on your own:

Result::all()->groupBy('Subjects')->each(function($subject) {
    $subject->sortByDesc('Score')->each(function($record) {
        // determine the rank by comparing the previous Score with the current
        $record->setAttribute('Position', $rank);
        $record->save();
    });
});
kosky2005

Hello, Thank Alot. You have done great. I am new in eloquent and laravel and trying to learn. This has gotten me stacked for about 2 days trying to find solution. i seems to follow your analysis but dont know how to complete it. I am not saving the data to DB, i want to return it to view. Can you you please complete it, so i can transfer the understanding to the actual work i am doing?

skliche
skliche
7 months ago (156,590 XP)

If this is your high school home assignment I'll come and spank you ...

Not the most elegant way but it should get you up and running:

$results = Result::all()->groupBy('Subjects')->map(function($subject) {
    $rank = 0; $score = -1;
    return $subject->sortByDesc('Score')->map(function($record) use (&$rank, &$score) {
        if ($score != $record->getAttribute('Score')) {
            $score = $record->getAttribute('Score');
            $rank++;
        }

        $record->setAttribute('Position', $rank);

        return collect($record->getAttributes());
    });
});

$results now contains a Collection of the models' data that is keyed by the subject (e.g. "English"). Each item of that collection contains the data for each student.

Dump it (dump($results);) first and understand its contents before you try to do anything in a view with it.

kosky2005

Thank you. i tried this and i am getting this error. Call to undefined method Illuminate\Database\Query\Builder::map()

    $getProgramme = Input::get('getProgramme');
    $getYear = Input::get('getYear');
    $getTerm = Input::get('getTerm');
    $getLevel = Input::get('getLevel');


    $results = ViewResults::select('regNumber','fullName','subject','exams100','programmes','ternYear','termDesc','level')
        ->groupBy('subject')
        ->map(function($subject) {
        $rank = 0; $score = -1;
        return $subject->sortByDesc('exams100')->map(function($record) use (&$rank, &$score) {
            if ($score != $record->getAttribute('exams100')) {
                $score = $record->getAttribute('exams100');
                $rank++;
            }

            $record->setAttribute('Position', $rank);

            return collect($record->getAttributes());
        });
    })
    ->where('programmes',$getProgramme)
    ->where('ternYear',$getYear)
    ->where('termDesc',$getTerm)
    ->where('level',$getLevel)
    ->get();


    dd($results);
skliche
skliche
7 months ago (156,590 XP)

Looks like you are not using Eloquent.

Create a model:

php artisan make:model Result

Then replace

ViewResults::select('regNumber','fullName','subject','exams100','programmes','ternYear','termDesc','level')

with

\App\Result::where('programmes',$getProgramme)
    ->where('ternYear',$getYear)
    ->where('termDesc',$getTerm)
    ->where('level',$getLevel)
    ->get()

and remove the where and get lines below the mapping (the lines directly above your dd() statement).

kosky2005

i have and i am getting empty collection Collection {#11711 ▼ #items: [] i used ViewResults instead of the results for the model }

This is when i added the input parameters.

    $getProgramme = Input::get('getProgramme');
    $getYear = Input::get('getYear');
    $getTerm = Input::get('getTerm');
    $getLevel = Input::get('getLevel');


    $results = ViewResults::all()
        ->groupBy('subject')
        ->map(function($subject) {
        $rank = 0; $score = -1;
        return $subject->sortByDesc('exams100')->map(function($record) use (&$rank, &$score) {
            if ($score != $record->getAttribute('exams100')) {
                $score = $record->getAttribute('exams100');
                $rank++;
            }

            $record->setAttribute('Position', $rank);

            return collect($record->getAttributes());
        });
    })
    ->where('programmes',$getProgramme)
    ->where('ternYear',$getYear)
    ->where('termDesc',$getTerm)
    ->where('level',$getLevel);
    //->get();


    dd($results);
skliche
skliche
7 months ago (156,590 XP)

Try this first:

$results = Results::all()
    ->groupBy('subject')
    ->map(function($subject) {
        $rank = 0; $score = -1;
        return $subject->sortByDesc('exams100')->map(function($record) use (&$rank, &$score) {
                if ($score != $record->getAttribute('exams100')) {
                $score = $record->getAttribute('exams100');
                $rank++;
            }

            $record->setAttribute('Position', $rank);

            return collect($record->getAttributes());
        });
    });


dd($results);
kosky2005

Ok. Sorry for worrying you. but you are only help now to get to through this.

Collection {#23116 ▼
  #items: array:31 [▼
    "English" => Collection {#10418 ▶}
    "Mathematics" => Collection {#9123 ▶}
    "Intergrated Science" => Collection {#7836 ▶}
    "ICT" => Collection {#6731 ▶}
    "Social Studies" => Collection {#5431 ▶}
    "Physics" => Collection {#5207 ▶}
    "Animal Husbandry" => Collection {#5123 ▶}
    "General Agric" => Collection {#5038 ▶}
    "Chemistry" => Collection {#4814 ▶}
    "Economics" => Collection {#4407 ▶}
    "Financial Accounting" => Collection {#4268 ▶}
    "Cost Accounting" => Collection {#4155 ▶}
    "IBM" => Collection {#4019 ▶}
    "Elective Mathematics" => Collection {#3640 ▶}
    "Government" => Collection {#3023 ▶}
    "Geography" => Collection {#2817 ▶}
    "History" => Collection {#2772 ▶}
    "CRS" => Collection {#2322 ▶}
    "Literature in English" => Collection {#1871 ▶}
    "Akuapem Twi" => Collection {#1650 ▶}
    "Ewe" => Collection {#1519 ▶}
    "French" => Collection {#1458 ▶}
    "Biology" => Collection {#1188 ▶}
    "Textiles" => Collection {#1050 ▶}
    "Leather Work" => Collection {#978 ▶}
    "Graphics $ Design" => Collection {#839 ▶}
    "G.K.A" => Collection {#623 ▶}
    "Clothing & Textiles" => Collection {#620 ▶}
    "Management in Living" => Collection {#489 ▶}
    "Food and Nutrition" => Collection {#23115 ▶}
    "" => Collection {#23117 ▶}
  ]
}

i am getting this collection now. how to i i now find with the input parameters to get the desire results with the subjtech position.

skliche
skliche
7 months ago (156,590 XP)

Ok, so we do get data. Let's do this step by step and add the first where condition:

$getProgramme = Input::get('getProgramme');
dump($getProgramme);
$getYear = Input::get('getYear');
dump($getYear);
$getTerm = Input::get('getTerm');
dump($getTerm);
$getLevel = Input::get('getLevel');
dump($getLevel);

$results = Results::where('programmes', $getProgramme)
    // ->where('ternYear',$getYear)
    // ->where('termDesc',$getTerm)
    // ->where('level',$getLevel)
    ->get()
    ->groupBy('subject')
    ->map(function($subject) {
        $rank = 0; $score = -1;
        return $subject->sortByDesc('exams100')->map(function($record) use (&$rank, &$score) {
                if ($score != $record->getAttribute('exams100')) {
                $score = $record->getAttribute('exams100');
                $rank++;
            }

            $record->setAttribute('Position', $rank);

            return collect($record->getAttributes());
        });
    });


dd($results);

If the first where condition works, removes the comments from the next one and so on.

If you don't get any results, make sure that a) the inputs are ok and b) you actually have rows in your database that satisfy the criteria.

skliche
skliche
7 months ago (156,590 XP)

Just saw your last sentence, what is the 'subjtech position'?

kosky2005

Sorry that was typo error, I wanted to say position

skliche
skliche
7 months ago (156,590 XP)

You can output the data in a view like this:

@foreach($results as $key => $subject)
    <h1>Subject {{ $key }}</h1>
    @foreach($subject as $record)
        {{ $record->get('Position') }}: {{ $record->get('Name') }}, {{ $record->get('Score') }}<br>
    @endforeach
@endforeach

Note that I'm using Score there while it may be exams100 as you've used that. Since I don't know what your table looks like in real life you'll have to adopt the names.

If you expand one of the results using the little triangle (▶) ...

"English" => Collection {#10418 ▶}

you should be able to expand it even further until you see the actual data including the position.

kosky2005

Ok. So this is what i have been able to do so far

        $getScores = ViewResults::where('programmes', $getProgramme)
             ->where('ternYear',$getYear)
             ->where('termDesc',$getTerm)
             ->where('level',$getLevel)
            ->get()
            ->groupBy('subject')

            ->map(function($subject) {
                $rank = 0; $score = -1;
                return $subject->sortByDesc('exams100')->map(function($record) use (&$rank, &$score) {

                    if ($score != $record->getAttribute('exams100'))

                    {
                        $score = $record->getAttribute('exams100');
                        $rank++;
                    }

                    $record->setAttribute('Position', $rank);

                    return collect($record->getAttributes());
                });
            });

         $net = $getScores->collapse();

        $getResults = $net->all();

and i am getting this collection

array:71 [▼
  0 => Collection {#552 ▼
    #items: array:18 [▼
      "id" => 208
      "regNumber" => "Dash/15/16/A001"
      "fullName" => "AGBADZI DONKOR"
      "subject" => "English"
      "exams30" => 24.0
      "exams70" => 25.0
      "exams100" => 49.0
      "grade" => "C6"
      "remarks" => "Credit"
      "house" => "Unity"
      "programmes" => "Agriculture Science"
      "groups" => "Core"
      "ternYear" => "2016 / 2017"
      "termDesc" => "Second Term"
      "nextTerm" => "2017-05-04"
      "no_on_roll" => "10"
      "level" => "Two"
      "Position" => 1
    ]
  }
  1 => Collection {#551 ▼
    #items: array:18 [▼
      "id" => 210
      "regNumber" => "Dash/15/16/A002"
      "fullName" => "AMETEFE CHRISTIAN"
      "subject" => "English"
      "exams30" => 23.0
      "exams70" => 25.0
      "exams100" => 48.0
      "grade" => "C6"
      "remarks" => "Credit"
      "house" => "Justice"
      "programmes" => "Agriculture Science"
      "groups" => "Core"
      "ternYear" => "2016 / 2017"
      "termDesc" => "Second Term"
      "nextTerm" => "2017-05-04"
      "no_on_roll" => "10"
      "level" => "Two"
      "Position" => 2
    ]
  }
  2 => Collection {#550 ▶}
  3 => Collection {#549 ▶}
  4 => Collection {#548 ▶}

Now how do i get this form of collection

Collection {#416 ▼
  #items: array:71 [▼
    0 => {#414 ▼
      +"id": 208
      +"regNumber": "Dash/15/16/A001"
      +"fullName": "AGBADZI DONKOR"
      +"subject": "English"
      +"exams30": 24.0
      +"exams70": 25.0
      +"exams100": 49.0
      +"grade": "C6"
      +"remarks": "Credit"
      +"house": "Unity"
      +"programmes": "Agriculture Science"
      +"groups": "Core"
      +"ternYear": "2016 / 2017"
      +"termDesc": "Second Term"
      +"nextTerm": "2017-05-04"
      +"no_on_roll": "10"
      +"level": "Two"
      +"sregNumber": "Dash/15/16/A001"
      +"ranked": "1"
    }
    1 => {#415 ▼
      +"id": 197
      +"regNumber": "Dash/15/16/A001"
      +"fullName": "AGBADZI DONKOR"
      +"subject": "Mathematics"
      +"exams30": 23.0
      +"exams70": 35.0
      +"exams100": 58.0
      +"grade": "C4"
      +"remarks": "Credit"
      +"house": "Unity"
      +"programmes": "Agriculture Science"
      +"groups": "Core"
      +"ternYear": "2016 / 2017"
      +"termDesc": "Second Term"
      +"nextTerm": "2017-05-04"
      +"no_on_roll": "10"
      +"level": "Two"
      +"sregNumber": "Dash/15/16/A001"
      +"ranked": "1"
    }
    2 => {#407 ▶}
    3 => {#419 ▶}

Please sign in or create an account to participate in this conversation.