How to get the last entered record field in rails with join and group by?

Question

I am using rails 5.1 with ruby 2.4 and I am stuck in rails Active record query, which I have already made it in SQL but not able to replicate it in the rails way. Please suggest the right syntax, thanks in advance. The Sql query is:

select jobs.id, users.first_name, users.last_name, users.email, MAX(comments.created_at) from comments left join jobs on jobs.id = comments.job_id left join users on users.id = jobs.user_id group by jobs.id;

The corresponding query made in rails is:

Comment.joins("left join jobs on jobs.id = comments.job_id").joins("left join users on users.id = jobs.user_id").select("jobs.id, users.first_name, users.last_name, users.email, max(comments.created_at)").group_by("jobs.id")

which is giving the following error:

Completed 500 Internal Server Error in 151ms (ActiveRecord: 6.1ms)

ArgumentError (wrong number of arguments (given 1, expected 0)):

The output should be as:

+-------+------------+-----------+------------------------+--------------------------+
| id    | first_name | last_name | email                  | MAX(comments.created_at) |
+-------+------------+-----------+------------------------+--------------------------+
| 63606 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-18 05:19:30      |
| 63608 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-26 09:22:21      |
| 63610 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 12:27:24      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 12:15:36      |
| 63684 | vidur      | punj      | vidur.punj@hotmail.com | 2019-10-15 11:46:45      |
| 63690 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-12 05:29:04      |
| 63694 | vidur      | punj      | vidur.punj@hotmail.com | 2019-05-23 04:03:01      |
| 63700 | vidur      | punj      | vidur.punj@hotmail.com | 2019-05-20 10:55:31      |
| 63701 | vidur      | punj      | vidur.punj@hotmail.com | 2019-10-19 10:07:42      |
| 63704 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-13 09:52:50      |
| 63705 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-11 11:16:35      |
| 63706 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-11 11:15:21      |
| 63708 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-13 12:44:19      |
| 63709 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-11 11:13:48      |
| 63717 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-17 09:42:35      |
| 63719 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-17 10:38:51      |
| 63726 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-20 10:55:44      |
| 63727 | vidur      | punj      | vidur.punj@hotmail.com | 2019-06-25 04:57:45      |
| 63741 | vidur      | punj      | vidur.punj@hotmail.com | 2019-07-22 06:51:53      |
| 63778 | vidur      | punj      | vidur.punj@hotmail.com | 2019-09-13 11:30:56      |
| 63801 | vidur      | punj      | vidur.punj@hotmail.com | 2019-09-16 06:55:08      |
| 63802 | vidur      | punj      | vidur.punj@hotmail.com | 2019-09-17 03:56:27      |
| 63815 | vidur      | punj      | vidur.punj@hotmail.com | 2019-10-30 05:43:07      |
| 63821 | vidur      | punj      | vidur.punj@hotmail.com | 2019-10-15 10:40:54      |
| 63839 | vidur      | punj      | vidur.punj@hotmail.com | 2019-09-23 11:07:30      |
| 63898 | vidur      | punj      | vidur.punj@hotmail.com | 2019-10-29 07:12:15      |
| 63916 | vidur      | punj      | vidur.punj@hotmail.com | 2019-10-15 12:51:06      |
+-------+------------+-----------+------------------------+--------------------------+

instead of:

+-------+------------+-----------+------------------------+--------------------------+
| id    | first_name | last_name | email                  | max(comments.created_at) |
+-------+------------+-----------+------------------------+--------------------------+
| 63606 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-18 05:18:27      |
| 63606 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-18 05:19:30      |
| 63608 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-18 11:06:48      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 05:33:57      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:04:16      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:49:08      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:49:49      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:50:41      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:51:27      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:52:17      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:52:48      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:53:31      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:53:46      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:54:07      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:54:27      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:54:45      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:55:28      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 06:58:00      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 08:41:57      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 09:08:07      |
| 63611 | vidur      | punj      | vidur.punj@hotmail.com | 2019-04-22 09:08:34      |

score 3 · Accepted Answer · answered Oct 30 '19 at 08:30

3

I think it's your group_by. That's a vanilla ruby enum. Have you tried .group('jobs.id')? group is an activerecord query helper that will run the sql group by clause

answered Oct 30 '19 at 08:30

Int'l Man Of Coding Mystery

1,632
2
14
29

yes, it gives error as: ActiveRecord::StatementInvalid: Mysql2::Error: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'TukaWeb_development.comments.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT jobs.id, users.first_name, users.last_name, users.email, max(comments.created_at) FROM `comments` left join jobs on jobs.id = comments.job_id left join users on users.id = jobs.user_id GROUP BY jobs.id ORDER BY `comments`.`id` DESC LIMIT 11 – vidur punj Oct 30 '19 at 08:32
I think that error message is saying to include `comments.id` in the group. I sometimes have to play with the ordering of things in the group clause `.group('jobs.id, comments.id')` – Int'l Man Of Coding Mystery Oct 30 '19 at 08:34
but the output is not correct, it is displaying all the comments on jobs, but the required is the latest comment on the jobs list. – vidur punj Oct 30 '19 at 08:49
I think you could take the `max(comments.created_at)` out of the select and add `maximum(comments.created_at)` to the query – Int'l Man Of Coding Mystery Oct 30 '19 at 08:51
It gives follwing error: ActiveRecord::StatementInvalid: Mysql2::Error: FUNCTION TukaWeb_development.maximum does not exist: SELECT jobs.id, users.first_name, users.last_name, users.email, maximum(comments.created_at) FROM `comments` left join jobs on jobs.id = comments.job_id left join users on users.id = jobs.user_id GROUP BY jobs.id, comments.id ORDER BY `comments`.`id` DESC – vidur punj Oct 30 '19 at 08:57
Sorry, I meant to take it out of the select query all together. ActiveRecord has a `maximum` query helper. So it might look like `Comment.joins(...).joins(...).select(jobs.id, users.first_name, users.last_name, users.email).maximum('comments.created_at').group('jobs.id, comments.id')` – Int'l Man Of Coding Mystery Oct 30 '19 at 10:06

score 1 · Answer 2 · answered Oct 30 '19 at 08:38

1

Comment.joins("left join jobs on jobs.id = comments.job_id").joins("left join users on users.id = jobs.user_id").select("jobs.id, users.first_name, users.last_name, users.email, max(comments.created_at)").group("jobs.id, comments.id")

answered Oct 30 '19 at 08:38

vidur punj

5,019
4
46
65

How to get the last entered record field in rails with join and group by?

2 Answers2