I think you could consider using raw sql, https://docs.djangoproject.com/en/2.2/topics/db/sql/#executing-custom-sql-directly
On Fri, Oct 25, 2019 at 7:56 PM Cheda me <dvdce...@gmail.com> wrote: > Hey guys, > > I have worked out the SQL query I would like to implement but am currently > struggling to create the Django query. I have been attempting to use the > .aggregate() & Max() methods but cant seem to get the right result. Any > help is much appropriated. > > SELECT temp.* > FROM item_detail temp > INNER JOIN > (SELECT item, MAX(created_date) AS MaxDateTime, MAX(id) AS MaxID > FROM item_detail > WHERE approved = true > GROUP BY item) grouped_join > ON temp.item = grouped_join.item > AND temp.created_date = grouped_join.MaxDateTime > AND temp.id = grouped_join.MaxID > > > CREATE TABLE item_detail > ( > id INT, > item INT, > created_date TIMESTAMP, > description VARCHAR(10), > approved BOOLEAN > > ); > > INSERT INTO item_detail VALUES(1, 1, '2009-04-03', 'dk', true); > INSERT INTO item_detail VALUES(2, 1, '2009-04-03', 'dk mama', false); > INSERT INTO item_detail VALUES(3, 1, '2009-03-04', 'dk 2', true); > INSERT INTO item_detail VALUES(4, 2, '2009-03-04', 'botw', true); > INSERT INTO item_detail VALUES(5, 2, '2009-04-04', 'botw 2', true); > INSERT INTO item_detail VALUES(6, 3, '2009-03-05', 'cod mw', true); > INSERT INTO item_detail VALUES(7, 4, '2008-12-25', 'bf 4', true); > INSERT INTO item_detail VALUES(8, 4, '2009-01-05', 'bf mw', false); > INSERT INTO item_detail VALUES(9, 2, '2009-04-06', 'botw 4', false); > INSERT INTO item_detail VALUES(10, 3, '2009-04-06', 'cod newy', false); > INSERT INTO item_detail VALUES(12, 1, '2009-04-07', 'dk ea', false); > INSERT INTO item_detail VALUES(13, 1, '2009-05-08', 'dk 3', true); > INSERT INTO item_detail VALUES(14, 1, '2009-05-08', 'dk 3-3', true); > > > Here is the link to the SQL Fiffle: http://www.sqlfiddle.com/#!17/260b62/1 > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to django-users+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-users/88f11149-eb65-4d0b-ad6e-729d9db6c6d2%40googlegroups.com > <https://groups.google.com/d/msgid/django-users/88f11149-eb65-4d0b-ad6e-729d9db6c6d2%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CABexzmgXY0XHjsLNe2%2BTwN1F8cJwX9%3DHYL2EoF56XzzD4M_xew%40mail.gmail.com.
