Friday, October 25, 2019

Database ORM query Help

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+unsubscribe@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.

No comments:

Post a Comment