CODEWITHSUNDEEP
djangoleft-join
Corey O.
Corey O.

Reputation: 476

Django: Accessing RIght-Hand Info From Left Joins

I'm doing a complex LEFT JOIN queries, but I'm not sure how to extract information from the joined tables. I see scads of examples that use LEFT JOIN to filter results, but I don't see anyone accessing any of the row values from the LEFT JOIN. First, let's start with a few simplified sample models as an example:

class Bar(models.Model):
    name = models.CharField(max_length=255)
    status = models.IntegerField(default=0)
    data = models.TextField(default=None)

    class Meta:
        db_table = 'bar'

class FooBar(models.Model):
    bar = models.ForeignKey(Bar, on_delete=models.CASCADE, related_name='foobars')
    status = models.IntegerField(default=0)
    data = models.TextField(default=None)

    class Meta:
        db_table = 'foobar'

class FuBar(models.Model):
    bar = models.ForeignKey(Bar, on_delete=models.CASCADE, related_name='fubars')
    status = models.IntegerField(default=0)
    data = models.TextField(default=None)

    class Meta:
        db_table = 'fubar'

The Bar table has a one-to-many relationship to both the FooBar and FuBar tables. This also means that the FooBar and FuBar have an implicit many-to-many relationship.

Here's the RAW equivalent of what I'm trying to do:

SELECT
    foobar.id,
    foobar.status,
    foobar.data,
    bar.id,
    bar.status,
    bar.data,
    fubar.id,
    fubar.status,
    fubar.data
FROM foobar
JOIN bar ON bar.id = foobar.bar_id
LEFT JOIN fubar ON fubar.bar_id = bar.id
WHERE
    foobar.status = 1
    AND bar.status = 1
    AND fubar.status = 1;

As I look at each entry in the tabular result set, I have all of the information to identify the singular FooBar, Bar, and Fubar records associated with each row and all of their column values. Let's assume that I need to be able to access every single one of these columns in the result-set once the query is completed.

I can reproduce the same joins in Django:

q = (FooBar.objects
     .filter(status=1,
             bar__status=1,
             bar__fubars__status=1)
     .select_related('bar'))

If I iterate through the result set, I can access the FooBar, and Bar objects without needing to perform any additional queries implicitly or explicitly. The following code snippet illustrates how I can access their information.

for fb in q:
    foobar = fb
    foobar_id = fb.id
    foobar_status = fb.status
    foobar_data = fb.data
    bar = foobar.bar
    bar_id = fb.bar.id
    bar_status = fb.bar.status
    bar_data = fb.bar.data

    fubar = ???
    fubar_id = ???
    fubar_status = ???
    fubar_data = ???

How can I access the individual FuBar object associated with each iteration? Ideally I'd like to be able to get it without performing any additional queries in the background if possible.

Thanks in advance.

--- edit ---

I have updated the description of the problem above to be a little more clear about the exact nature of the problem. I am looking specifically for the single, specific FuBar record associated with each row of the result set. Notice that the following:

foobar.bar.fubars

Is not what I want. In short, this returns a set of FuBar records related to the Bar record in the current result-set's row. It does not tell me which specific record was LEFT JOIN'd to the current row in the result-set.

Upvotes: 1

Views: 393

Answers (1)

Shane
Shane

Reputation: 653

This isn't getting it in without extra queries, but I believe is at least getting you the expected results. As I'm dipping in and out while working on work projects, my brain may not be properly focused. But the idea was to annotate in the FuBar id from the matching Bar of the FooBar row, then do a get on it while iterating (which of course is where the extra queries are from).

I can keep plugging away to see if anything else comes to mind.

foobars = FooBar.objects.filter(status=1, bar__status=1, bar__fubars__status=1)
fubar = FuBar.objects.filter(bar=OuterRef('bar')).values('id')
foobars = foobars.annotate(fubar_id=Subquery(fubar[:1]))
foobars = foobars.select_related('bar')

for foobar in foobars:
    bar = foobar.bar

    fubar = bar.fubars.filter(id=foobar.fubar_id).get()

Upvotes: 1

Related Questions