I have a table that contains the pricing data for the cards and I am trying to get the pricing data for distinct cards by card_id but this seems to select the row at random. I would like to get the latest datetime pricing data for each card card_id.
Table:
| id | nonfoil | foil | datetime | card_id |
|---|---|---|---|---|
| "fb7fbcdc" | 0.20 | 0.49 | "2021-10-11 10:03:51.943603 01" | "00302342" |
| "d0d6f491" | 0.10 | 0.49 | "2021-10-11 10:01:09.916438 01" | "00302342" |
| "bfdca73b" | 0.03 | 0.04 | "2021-10-11 10:03:51.907601 01" | "012e0b83" |
| "33c7aeae" | 0.10 | 0.04 | "2021-10-11 10:01:09.875894 01" | "012e0b83" |
| "94ca3324" | 0.10 | 0.04 | "2021-10-11 10:01:09.961261 01" | "0307f37b" |
| "2e992a8d" | 0.03 | 0.04 | "2021-10-11 10:03:51.988602 01" | "0307f37b" |
I currently am getting the pricing data using the following code:
pricing_cards.objects.filter(card_id__rarity='mythic').values_list('nonfoil', flat=True).distinct('card_id'),
For example this is returning:
| id | nonfoil | foil | datetime | card_id |
|---|---|---|---|---|
| "d0d6f491" | 0.10 | 0.49 | "2021-10-11 10:01:09.916438 01" | "00302342" |
| "bfdca73b" | 0.03 | 0.04 | "2021-10-11 10:03:51.907601 01" | "012e0b83" |
| "94ca3324" | 0.10 | 0.04 | "2021-10-11 10:01:09.961261 01" | "0307f37b" |
But I would like it to return:
| id | nonfoil | foil | datetime | card_id |
|---|---|---|---|---|
| "fb7fbcdc" | 0.20 | 0.49 | "2021-10-11 10:03:51.943603 01" | "00302342" |
| "bfdca73b" | 0.03 | 0.04 | "2021-10-11 10:03:51.907601 01" | "012e0b83" |
| "94ca3324" | 0.10 | 0.04 | "2021-10-11 10:01:09.961261 01" | "0307f37b" |
CodePudding user response：
This should do it:
pricing_cards.objects.filter(card_id__rarity='mythic').order_by('card_id', '-datetime').distinct('card_id').values_list('nonfoil', flat=True)