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: django-users@googlegroups.com <django-users@googlegroups.com> On Behalf
Of wd
Sent: Saturday, 26 October 2019 11:43 AM
To: django-users@googlegroups.com
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 <dvdce...@gmail.com
<mailto: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 <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 django-users+unsubscr...@googlegroups.com
<mailto: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
<mailto: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
<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 django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/004f01d58bcd%249e32bee0%24da983ca0%24%40gmail.com.
