Create object with multiple fields from subquery

I have an append only model with which I would like to create objects by copying properties from the last inserted object:

class User(models.Model):
	name = models.TextField()
class Event(models.Model):
    class EventType(models.IntegerChoices):
        CREATE = 1
        UPDATE = 2
        DELETE = 3

    type = models.SmallIntegerField(choices=EventType.choices)

    user = models.ForeignKey(
        User,
        on_delete=models.PROTECT,
    )

	property_1 = models.IntegerField()

	property_2 = models.IntegerField()

	property_3 = models.IntegerField()
def create_event_with_property_1(user_id, new_property_1):
	return Event.objects.create(
		type = Event.EventType.UPDATE,
		user_id= user_id,
		property_1 = new_property_1,
		property_2 = Subquery(
			Event.objects.filter(
                user_id=user_id, 
            ).order_by(
                "-id"
            ).values("property_2")[:1]
		),
		property_3 = Subquery(
			Event.objects.filter(
                user_id=user_id, 
            ).order_by(
                "-id"
            ).values("property_3")[:1]
		)
	)

This works fine, but is there a way to do multiple properties from a single query? As I add more fields, the query becomes unwieldy and slow.

Thanks!

You should find the last record first, then use that object in this function.

How about

def create_event_with_property_1(user_id, new_property_1):
    last_event = Event.objects.filter(user_id=user_id).order_by("-id").first()
    if not last_event:
        return
    event = Event.objects.create(
        type=Event.EventType.UPDATE,
        user_id= user_id,
        property_1=new_property_1,
        property_2=last_event.property_2,
        property_3=last_event.property_3,
    )
    return event

The reason I don’t want to do that is because a new event can be added between those two queries.

I realise I can structure a transaction which then runs a third query to check the previous event was the one I thought it was (failing if not) but this then requires 5 queries (start transaction, get previous event, create event, check second to last event, commit transaction) and potentially can get stuck in a retry loop.

The SQL should be fairly simple I think:

INSERT INTO events (type, user_id, property_1, property_2, property_3)
SELECT 1, $UserId, $NewProperty1, property_2, property_3
FROM events 
WHERE user_id=$UserId
ORDER BY ID DESC
LIMIT 1

Alternatively, it can be easily done with CTE (subqueries).

The short answer is that you must use one subquery by inserted/updated field to achieve this because the ORM doesn’t support CTE or LATERAL joins.

See this SO question which came to the same conclusion.

It seems that django-cte does support update but I’m unsure about create.

Thanks for that, looks interesting but I think even the update still relys on just piling up subqueries from looking at it…

I suspect I may be forced to do it with raw queries for now although if anyone else had any suggestions I’d appreciate it!