Can this lookup be performed with a single query using Django ORM?

Using Django Using the ORM
1

I have the following models:

class Article(models.Model):
    manufacturer_sku = models.TextField(db_index = True, unique=True)
    
class ExternalSource(models.Model):
    name = models.CharField(max_length=128, unique=True)

class ExternalConnections(models.Model):
    external_source = models.ForeignKey(ExternalSource, on_delete=models.CASCADE)
    article = models.ForeignKey(Article, on_delete=models.CASCADE)
    value = models.TextField()

I need to get the article referenced by an entry in ExternalConnectons with value = itscope_id and referencing an ExternalSource entry with name = ‘itscope_id’.
Currently i am first filtering for the ExternalConnection and accessing the article via the attribute like this:

external_connection = await ExternalConnections.objects.filter(value = itscope_id, external_source__name = 'itscope_id').afirst()
if external_connection == None:
     raise ArticleNotFound(sku, itscope_id)
            
def sync_externalconnection_to_article():
     self._article = external_connection.article
await sync_to_async(sync_externalconnection_to_article)()

My initial thought was that this could be condensed into one query like this:

article = 
Article.objects.filter(externalconnections__externalsource__name="itscope_id", externalconnections__value=itscope_id).first()

But this results in an error:

Unsupported lookup 'externalsource' for ManyToOneRel or join on the field not permitted.

Can the sql query below be perfomed using Django ORM or do i have to resort to raw SQL?

SELECT articles_article.* FROM articles_article
INNER JOIN articles_externalconnections
ON articles_article.id=articles_externalconnections.article_id 
INNER JOIN articles_externalsource
ON articles_externalsource.id=articles_externalconnections.external_source_id
WHERE articles_externalsource.name='itscope_id' AND articles_externalconnections.value='17814839000'

Edit: Reduced models to relevant fields
Edit2: Typo
Edit3: SQL

2

I think there’s a potential mistake that might just be a simple typo.

The model name is ExternalConnections, but you’re trying to reference a related name external_connection, but you don’t have the related_name attribute with that as the name. So, it seems that you’re trying to reference a related name that isn’t defined.

To clarify, with what you have posted here, the query would be:

article = Article.objects.filter(externalconnections__external_source__name='itscope_id', externalconnections__value=itscope_id).afirst()
4

I had several different typos in my query yours works, thank you.

5

So the real question here is, do you see why this works, and not some other formulation?

6

Yes i think im good. My main problem was that i confused myself by giving the ExternalSource model and the ExternalConnections attribute external_source essentially the same name with an extra underscore while also having the result of the request save in a variable called external_connection.
The error mentioning “join on the field not permitted” instead of something like “cant join on nonexistant field” did not help either.

Thank you for taking the time to help me with this.