kosky2005
125
14
Laravel

Get Position subjects in exams by Students using Laravel

Posted 9 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();

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

Laracasts Mascot

Hi, Have We Met Yet?

Did you know that, in addition to the forum, Laracasts includes well over 1000 lessons on modern web development? All for the price of one lunch out per month.

Sign Me Up

Channels

Reply to

Use Markdown with GitHub-flavored code blocks.