Query to retrieve the latest entry in a history table in Laravel 5.2

Question

Here is the table

table: StudentHistory

id   |  date     | name  | grade  | subject
---- | ------    |------ |--------|------
1    | 5/1/2017  |Mark   |   a    |   science
2    | 7/1/2016  |Earl   |   c    |   english
3    | 2/1/2015  |John   |   a    |   english
4    | 6/1/2016  |Mike   |   c    |   science
5    | 4/1/2016  |Matt   |   e    |   english
6    | 2/1/2017  |Mark   |   d    |   science
7    | 3/1/2016  |Earl   |   a    |   english
8    | 7/1/2015  |John   |   d    |   english
9    | 8/1/2016  |Mike   |   c    |   science

What I want to happen is to populate the latest grades ONLY in English for students who have one. It should show like this

7/1/2016 Earl c
7/1/2015 John d
4/1/2016 Matt e

I got this but it doesn't give the latest based on

$englishgrades = StudentHistory::('date', 'name', 'grade') ->where('subject', 'english') ->groupBy('name') ->get();

Please help

Is that first of May or fifth of January? – Strawberry May 11 '17 at 08:29 — Strawberry, May 11 '17 at 08:29

Anar Bayramov · Accepted Answer · 2017-05-19T03:42:43.010

0

You can do descending order and receive first row which is going to be your last row in database. But the thing is you store date in a wrong way. as mentioned by @Strawberry its not clear date or month. I would suggest you to do use mysql datetime option then you can simply do the code I post below. In this case you might have to parse each date for a proper way

$englishgrades = StudentHistory::select('date', 'name', 'grade')
->where('subject', 'english')
->groupBy('name')
->orderBy('date','desc')
->get();

edited May 19 '17 at 03:42

answered May 11 '17 at 08:25

Anar Bayramov

11,158
5
44
64

shouldn't it be ->orderBy('date','desc')? – Rodney Zanoria May 11 '17 at 08:29
It was so then I changed it because I am kinda confused on what you are trying to do :) you are gonna order by Date only or all this fields ?? – Anar Bayramov May 11 '17 at 08:31
Yeah, just by date. However, when I applied that, all the columns are now printing, from ID to subject. When I commented out first(), it's back to just the three things I selected. Do you know why? – Rodney Zanoria May 11 '17 at 08:34
actually, it printed out all back again – Rodney Zanoria May 11 '17 at 08:35
all columns ? its normal no ? you want to get special column ? you are not using select in the first line `StudentHistory::select('date', 'name', 'grade')` edited my answer – Anar Bayramov May 11 '17 at 08:35
Hi @Rodrane, does first and get work together? I posted another question and the other developer said that you can only use one. You can check that here http://stackoverflow.com/questions/44043255/queryexception-errorexception-and-pdoexception-in-connection-php – Rodney Zanoria May 19 '17 at 02:57
you can use only one either first or get – Anar Bayramov May 19 '17 at 03:35
Oh ok, however, your answer has both. Can you help me by reviewing my question again and how to achieve my needed results? – Rodney Zanoria May 19 '17 at 03:41
I am sorry it was my bad. Just remove first you get all the data you need if you keep first you get first one – Anar Bayramov May 19 '17 at 03:43

score 0 · Answer 2 · answered May 11 '17 at 08:36

0

I don't know what you mean by 'populate' in this context, and I don't know laravel; but here's a query that gets the desired result...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,date  DATE NOT NULL
,name  VARCHAR(12) NOT NULL
,grade  CHAR(1) NOT NULL
,subject VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1,'2017/5/1','Mark','a','science'),
(2,'2016/7/1','Earl','c','english'),
(3,'2015/2/1','John','a','english'),
(4,'2016/6/1','Mike','c','science'),
(5,'2016/4/1','Matt','e','english'),
(6,'2017/2/1','Mark','d','science'),
(7,'2016/3/1','Earl','a','english'),
(8,'2015/7/1','John','d','english'),
(9,'2016/8/1','Mike','c','science');

SELECT a.* 
  FROM my_table a 
  JOIN 
     ( SELECT name
            , MAX(date) date
         FROM my_table 
        WHERE subject = 'English' 
        GROUP 
           BY name
     ) b 
    ON b.name = a.name 
   AND b.date = a.date;
+----+------------+------+-------+---------+
| id | date       | name | grade | subject |
+----+------------+------+-------+---------+
|  2 | 2016-07-01 | Earl | c     | english |
|  5 | 2016-04-01 | Matt | e     | english |
|  8 | 2015-07-01 | John | d     | english |
+----+------------+------+-------+---------+

answered May 11 '17 at 08:36

Strawberry

33,750
13
40
57

can you do this in query builder? – Rodney Zanoria May 11 '17 at 08:37
If you think this is the answer, then this is a duplicate topic, see http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column The only reason I have not voted yet because the question is for laravel and the OPs tend to prefer an answer that uses the query builder and not raw sql. I would use the raw sql :) – Shadow May 11 '17 at 08:40
Thanks for pointing that out shadow. I'm particularly looking for a query builder in Laravel for my controller. I saw the post you referenced, and that's MySQL – Rodney Zanoria May 11 '17 at 08:43
@Rodrane really? I can just use raw and use that code? let me try. – Rodney Zanoria May 11 '17 at 08:44
@Rodrane, I think I'm going to have a migraine. LOL, that's why I prefer the query builder. strawberry if you can explain that to me, interpret in English, then I guess I can try using that – Rodney Zanoria May 11 '17 at 08:46

Query to retrieve the latest entry in a history table in Laravel 5.2

2 Answers2

Linked