Get Position subjects in exams by Students using Laravel

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?

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();
    });
});

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?

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.

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);

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).

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);

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);

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.

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.

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

Sorry that was typo error, I wanted to say position

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.

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 ▶}