Django filter on attribute with two values, preferring the result of one

Question

This is kind of a weird question, so sorry in advance. I have a model in Django Rest that looks like this:

class BaseModel(models.Model):
    created_date = models.DateTimeField(auto_now_add=True)
    modified_date = models.DateTimeField(auto_now=True)

   class Meta:
        abstract = True

class Foo(BaseModel):
    barId = models.ForeignKey(Bar, unique=False)
    fizzId = models.ForeignKey(Fizz, unique=False)
    buzzId = models.IntegerField(unique=False)
    value = models.TextField()

And I have a ViewSet that needs to return a list of all the Foos that have a given {request_barId, request_buzzId, lastUpdateDate}. This is fairly straightforward,

foobar = Foo.objects.filter(
buzzId=request_buzzId,
modified_date__gt=request_lastUpdateDate,
barId=request_barId)

Here's the rub. There's a default value for buzzId that is the base list that the specified buzzId needs to overlay, replacing the instances on the base list. That's where I'm getting a little lost. I have a solution, but I feel like it's not particularly elegant and that there's go to be a way to do this cleaner. Here's my code to do the overlay:

base_foobar = Foo.objects.filter(
buzzId=base_buzzId,
modified_date__gt=request_lastUpdateDate,
barId=request_barId).exclude(
        fizzId__in=[o.fizzId for o in foobar])
result = foobar | base_foobar

And this just seems really janky. Is there a way to clean this up?

EDIT: To clarify, let's say that the list for the tuple { 1, 0, '01-01-1970' } represents the base set (buzzId: 0), and returns a list of objects containing fizzIds { 1, 2, 3, 10 }. Let's say that the tuple { 1, 1, '01-01-1970' } represents some buzzId's request for a complete set of strings. If we say that our buzzId of 1 (call it augment) has matching Foos with fizzIds { 2, 10, 15, 20 }, then our result set should look like

{ (base) 1, (augment) 2, (base) 3, (augment) 10, (augment) 15, (augment) 20 }

Does this clear it up?

can you please elaborate on this and update your question: "There's a default value for buzzId that is the base list that the specified buzzId needs to overlay, replacing the instances on the base list." — Dhruv Baveja, Mar 02 '17 at 20:56

score 0 · Accepted Answer · answered Mar 03 '17 at 16:03

0

You could use Q to combine into one statement:

result = (Foo.objects
    .filter(
        Q(
            modified_date__gt=request_lastUpdateDate,
            barId=request_barId
        ) 
        &
        (
            Q(buzzId=request_buzzId) 
            |
            (
                Q(buzzId=base_buzzId)
                & 
                ~Q(fizzId__in=[o.fizzId for o in foobar])
            )
        )
    )
    .order_by( '-buzzId' if request_buzzId < base_buzzId else 'buzzId')
    )[0] # fetch first result only

Also, please format all your code in the future. It doesn't have to be like me, but it would definitely help with reading your question.

answered Mar 03 '17 at 16:03

Ross Rogers

23,523
27
108
164

Do apologise on the formatting; I tried to fix it in the editor and I guess editing screwed it up or something. I'll fix it. Will also try your solution out, thanks! – Duckies Mar 03 '17 at 19:40
No prob, I see you're newbie :-) If the solution helps, up vote it. If it works, up vote it and accept it :-) – Ross Rogers Mar 03 '17 at 21:30
So the solution I have right now does work. I'm wondering if you can explain why this solution would be a better one than what I have listed? Does it optimize the query, or is there an efficiency I'm missing? – Duckies Mar 03 '17 at 22:25
There is probably little difference, but you should look at the generated SQL by printing it out to your console. I do [this](http://stackoverflow.com/q/22134895/20712) in my environment to _always_ print out the SQL in debug/non-production mode. The only "optimization" that I've done is to implement [D.R.Y.](https://en.wikipedia.org/wiki/Don%27t_repeat_yourself) by putting the common filter terms into a single `Q()`. Also, by using one filter, instead of two, complex relationship following filter parameters are executed against one related table instance instead of rejoining. – Ross Rogers Mar 04 '17 at 02:52
Anyways. Spit out the SQL and then measure it in whatever database engine you're using. Then look at the query schedule et c., et c. – Ross Rogers Mar 04 '17 at 02:52

Django filter on attribute with two values, preferring the result of one

1 Answers1