Challenges Encountered in Implementing Conditional WHERE Feature in `bulk_create` for Django Ticket #34277

Django Internals ORM
1

Hello everyone,

We are currently working on adding a feature for Django ticket #34277. The goal is to introduce a conditional WHERE clause in the bulk_create method to enable conditional updates when using the bulk_create function with update_conflicts=True.

What we have accomplished so far:

  1. Modifying bulk_create: We added additional parameters to bulk_create, including update_conflicts, update_fields, unique_fields, and where_clause.
  2. Passing Parameters: We have successfully passed these parameters through various functions, including _insert, _batched_insert, and others relevant to the process.
  3. Handling SQL: We started working on converting the WHERE clause (represented by a Q object) into valid SQL, using a SQL compiler obtained via get_compiler().

Encountered Issue:
When attempting to compile the Q object (representing the WHERE clause) into SQL, we are facing an error: 'Q' object has no attribute 'as_sql'. We tried to bypass this issue by using compiler.compile(where_clause), but this did not lead to a viable solution.

Questions and Need for Clarifications:

  1. Converting Q to WhereNode: How can we properly convert a Q object into a WhereNode object so it can be compiled into SQL? Is there a standard approach for this within Django’s framework, or should we consider a custom method?
  2. Best Practices: Are there any best practices or existing examples in Django’s codebase that we could refer to for this task?
  3. Handling Specific Backends: How can we effectively manage differences between SQL backends (like SQLite, PostgreSQL, etc.) when generating SQL for the conditional WHERE clause?
  4. General Advice: Are there any tips or cautions you could give us to successfully complete this task, considering its complexity and impact on a widely used feature like bulk_create?

We would greatly appreciate any feedback, advice, or code examples that could help us progress in this complex task. We aim to make a significant contribution to Django while ensuring the robustness and compatibility of this new feature.

Thank you in advance for your time and expertise.

Best regards,
Barhamou

# myapp/views.py
from django.http import HttpResponse
from .models import Item
from django.utils import timezone
from datetime import timedelta
from django.db.models import F, Q

def test_bulk_create_view(request):
    new_items = [
        Item(id=1, name="Item 1 encore une modification", last_updated=timezone.now() - timedelta(days=1)),
        Item(id=2, name="Item 2 Updated", last_updated=timezone.now() - timedelta(days=1)),
        Item(id=3, name="Item 3 New")
    ]

    Item.objects.bulk_create(
        new_items,
        update_conflicts=True,
        update_fields=['name', 'last_updated'],  # Champs à mettre à jour
        unique_fields=['id'],  # Champ unique susceptible de déclencher l'upsert
        where_clause=Q(last_updated__lt=F('EXCLUDED__last_updated'))
    )


    #Item.objects.bulk_create(new_items, where_clause='Une clause where')
    #Item.objects.bulk_create(new_items, update_conflicts=True)
    return HttpResponse("bulk_create testé avec succès")