Database ORM query Help

[Cheda me]

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

[Samiul Sk]

Please post the code of Model and explain what do you want as Queryset?

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

[wd]

I think you could consider using raw sql, https://docs.djangoproject.com/en/2.2/topics/db/sql/#executing-custom-sql-directly

--

[dvdc...@gmail.com]

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)

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CABexzmgXY0XHjsLNe2%2BTwN1F8cJwX9%3DHYL2EoF56XzzD4M_xew%40mail.gmail.com.