0

My models :

I have 2 (simplified) models:

class Message(models.Model):
    mmsi = models.IntegerField()
    time = models.DateTimeField()

class LastMessage(models.Model):
    mmsi = models.IntegerField()
    time = models.DateTimeField()

From the most recent Message, I want to check if there are Message not in LastMessage.

I can get the most recent Message like that :

recent_messages = (Message.objects.distinct('mmsi')
                  .order_by('mmsi', '-time'))

From there, I have no idea how to extract the informations I want from recent_message and LastMessage.

I'd typically like to have the information displayed like this :

MMSI | Message_Time | LastMessage_Time

I can do that with SQL like this for example :

SELECT r.mmsi, r.time as Message_Time, l.time as LastMessage_Time 
FROM recent_message as r 
INNER JOIN core_lastmessage as l on r.mmsi = l.mmsi 
WHERE r.time <> l.time LIMIT 10;

┌─────────┬────────────────────────┬────────────────────────┐
│  mmsi   │      message_time      │    lastmessage_time    │
├─────────┼────────────────────────┼────────────────────────┤
│ 2000000 │ 2019-09-10 10:42:03+02 │ 2019-09-10 10:26:26+02 │
│ 2278000 │ 2019-09-10 10:42:24+02 │ 2019-09-10 10:40:18+02 │
│ 2339002 │ 2019-09-10 10:42:06+02 │ 2019-09-10 10:33:02+02 │
│ 2339004 │ 2019-09-10 10:42:06+02 │ 2019-09-10 10:30:07+02 │
│ 2417806 │ 2019-09-10 10:39:19+02 │ 2019-09-10 10:37:02+02 │
│ 2417807 │ 2019-09-10 10:41:18+02 │ 2019-09-10 10:36:55+02 │
│ 2417808 │ 2019-09-10 10:42:23+02 │ 2019-09-10 10:30:39+02 │
│ 2470087 │ 2019-09-10 10:42:23+02 │ 2019-09-10 10:39:13+02 │
│ 3160184 │ 2019-09-10 10:42:03+02 │ 2019-09-10 10:28:30+02 │
│ 3604482 │ 2019-09-10 10:42:10+02 │ 2019-09-10 10:35:29+02 │
└─────────┴────────────────────────┴────────────────────────┘

(here recent_message is just a temporary table for conveniance)

How could I do that in Django ?

Thanks !

ogr
  • 610
  • 7
  • 23
  • Is there a reason that Message and LastMessage are different models? – Nico Griffioen Sep 11 '19 at 14:06
  • Message is potentially a huge table with millions of rows, and I want a fast access to the last messages for every distinct MMSI number, that's why I have a different table. I doubt I can get that information as fast with only one table Message. – ogr Sep 11 '19 at 14:18
  • 1
    You should not split this data over multiple tables. It's bad design, and potentially creates data synchronisation issues. Millions of rows is no issue for SQLite or Postgres. You can simply create an index to speed up your query. If you want I can write out an answer on how to do this. – Nico Griffioen Sep 11 '19 at 14:54
  • @NicoGriffioen Thx, I posted another question for that. If I can have only one table and keep the same level of performance, I'd be glad to be wrong. Here is the other question : https://stackoverflow.com/questions/57893251/proper-way-to-access-latest-row-for-each-individual-identifier/57893282#57893282 . On the other hand, I'd still be curious to know how to do what I asked in Django, it's still a blur to me. – ogr Sep 11 '19 at 16:26
  • 1
    If you want to keep this design I would suggest making a `OneToOne` field from `LastMessage` to `Message` instead of relying on an implicit shared IntegerField. This will ensure that no more than one `LastMessage` can point at a single `Message` which will help with consistency but you could still end up with a situation where a Message has no associated LastMessage. Then you would do `Message.objects.all().values("mmsi", "time", "lastmessage__time")` – azundo Sep 11 '19 at 18:55
  • @azundo I will probably change my model indeed, and drop my `LastMessage` table as NicoGriffioen suggested. However your solution is probably the kind of answer I was looking for in the first place. Joining table with underlying foreign key looks pretty easy. I would still be curious to know if there is a way to join tables with arbitrary data though. – ogr Sep 11 '19 at 19:17

0 Answers0