Thanks for the recommendation. The raw SQL method worked for me. Would be good
to know how to accomplish this using the Django ORM query set.
Basically I have a list of games, the user may choose to update the name for
example and instead of the object being updated a new object is created with
the status set to PENDING.
The application should only display a single unique game (game_id) where the
latest(created_at) record status is (APPROVED).
Example, I have cut out most of the bulk one to many links, fields etc. so it
is easier to share:
My View:
class ViewGameList(ListView):
model = GameDetails
template_name = 'games/list_all_games.html'
paginate_by = 60 # if pagination is desired
def get(self, request, **kwargs):
self.object_list = GameDetails.objects.raw("""
SELECT temp.*
FROM gamedetails temp
INNER JOIN
(SELECT game_id, MAX(created_at) AS MaxDateTime, MAX(id) AS MaxID
FROM gamedetails
WHERE status = ‘APRO’
GROUP BY game_id) grouped_join
ON temp.game_id = grouped_join. game_id
AND temp.created_at = grouped_join.MaxDateTime
AND temp.id = grouped_join.MaxID
""")
return self.render_to_response(self.get_context_data())
My Model:
class GameDetails(models.Model):
name = models.TextField(blank=False)
game = models.ForeignKey(Game, on_delete=models.CASCADE)
created_at = models.DateTimeField(auto_now_add=True)
APPROVED = 'APRO'
PENDING = 'PEND'
DECLINED = 'DECL'
STATUS_TYPES = (
(APPROVED, 'Approved'),
(PENDING, 'Pending Approval'),
(DECLINED, 'Declined'),
)
status = models.CharField(max_length=25, choices=STATUS_TYPES,
default=PENDING)
From: [email protected] <[email protected]> On Behalf
Of wd
Sent: Saturday, 26 October 2019 11:43 AM
To: [email protected]
Subject: Re: Database ORM query Help
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 <[email protected]
<mailto:[email protected]> > 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 <http://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 [email protected]
<mailto:[email protected]> .
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 [email protected]
<mailto:[email protected]> .
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CABexzmgXY0XHjsLNe2%2BTwN1F8cJwX9%3DHYL2EoF56XzzD4M_xew%40mail.gmail.com
<https://groups.google.com/d/msgid/django-users/CABexzmgXY0XHjsLNe2%2BTwN1F8cJwX9%3DHYL2EoF56XzzD4M_xew%40mail.gmail.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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/004f01d58bcd%249e32bee0%24da983ca0%24%40gmail.com.
