Using raw expression in laravel to get latest entry for user

Question

Here is my table

What I want to happen is to get the latest grades in English for students and ignore students who don't have one. It should show like this:

{"date":"2017\/7\/13","student_id":2,"grade":"C"}<br>
{"date":"2017\/7\/1","student_id":3,"grade":"D"}<br>
{"date":"2017\/4\/1","student_id":5,"grade":"E"}

However, in the code that I have, this is what I show

{"id":2,"date":"2017\/5\/1","student_id":1,"grade":"A","subject":"Science","created_at":null,"updated_at":null}<br>
{"id":3,"date":"2017\/7\/13","student_id":2,"grade":"C","subject":"English","created_at":null,"updated_at":null}<br>
{"id":4,"date":"2017\/2\/1","student_id":3,"grade":"A","subject":"English","created_at":null,"updated_at":null}<br>
{"id":5,"date":"2017\/6\/1","student_id":4,"grade":"C","subject":"Science","created_at":null,"updated_at":null}<br>
{"id":6,"date":"2017\/4\/1","student_id":5,"grade":"E","subject":"English","created_at":null,"updated_at":null}<br>
{"id":7,"date":"2017\/2\/1","student_id":1,"grade":"D","subject":"Science","created_at":null,"updated_at":null}<br>
{"id":8,"date":"2017\/3\/1","student_id":2,"grade":"A","subject":"English","created_at":null,"updated_at":null}<br>
{"id":9,"date":"2017\/7\/1","student_id":3,"grade":"D","subject":"English","created_at":null,"updated_at":null}<br>
{"id":10,"date":"2017\/8\/1","student_id":4,"grade":"C","subject":"Science","created_at":null,"updated_at":null}

Here is my controller

$englishgrades = StudentHistory::raw('select p1.student_id
    from student_histories p1
    inner join 
    (
      select student_id, max(date) as mdate
      from student_histories
      group by student_id
    )
    p2 on p1.student_id = p2.student_id and p1.date = p2.mdate 
    where p1.subject = "English"
    ')
    ->get();

home.blade.php

Total of {{ $englishgrades->count() }}
                <br><br>

                @foreach ($englishgrades as $englishgrade )
                    {{ $englishgrade  }}<br>
                @endforeach<br><br>

Please also help me remove the other details that it is getting in the blade view.

Yes, something I can insert as raw expression, like what I did above — Rodney Zanoria, May 19 '17 at 06:27
I can provide you mysql query, then you can write in your controller file, Also can you provide me create table and data, i will work on that — Monty, May 19 '17 at 06:28
I'm good with mysql query because I can use that as raw expression. What do you need again? — Rodney Zanoria, May 19 '17 at 06:35
provide me create table command with data, i will insert that in my mysql and create query for you — Monty, May 19 '17 at 06:38
Hi Monty, just use generic table names so you can recreate in your end. You can use strawberry's response here as guide http://stackoverflow.com/questions/43910090/query-to-retrieve-the-latest-entry-in-a-history-table-in-laravel-5-2 — Rodney Zanoria, May 19 '17 at 07:14
Hi @Strawberry, yeah, I know it should be, but it's giving me all the data, not the ones I intend it to be — Rodney Zanoria, May 19 '17 at 07:46
@Strawberry, can you show me if it runs on your end? I would love see if someone can show me it runs using laravel — Rodney Zanoria, May 20 '17 at 02:35

kunal · Accepted Answer · 2017-05-20T12:27:53.857

Try this once :-

$englishgrades = DB::select(DB::raw('SELECT  a.*
                    FROM    student_historiess a
                    INNER JOIN
                    (
                        SELECT *, MAX(id) max_ID
                        FROM    student_histories
                        GROUP BY grade
                    ) b ON a.grade = b.grade AND
                        a.id = b.max_ID  
                        having a.subject = "english"'));
 echo "<pre>"; print_r($englishgrades); die;

Hope it helps!

Using raw expression in laravel to get latest entry for user

1 Answers1