Help accessing data from connected models in a single query

Using Django Using the ORM
1

Hello,

I needed some help checking if its possible to get data from multiple tables by joining them in django. I have the following models. I am trying to search field 5 in model E. I need related information from class A and class P. Since class E has a foreign key relation to classA I am able to get the information using select_related. However I am not able to access information from Model P in a single query. How to structure the query so that the object has information from Class A as well as class P. The models are below.

Class A(models.model):
field1 = models.IntegerField(primary_key = True)
field2 = models.IntegerField
field3 …
field4…

Class E(models.model):
field1 = ForeignKey(Class A)
field2
field5.
field6…
field7…

Class P(models.model)
field1 = ForeignKey(Class A)
field2 …
field8
field9
field10

Thanks for your help.

2

Welcome @tlhingor !

First, for clarity:

This is not an accurate statement. Using select_related is never required. It is a performance enhancement facility only. You are always able to follow relationships in either direction without using it.

Additionally, while it is true that the ORM does execute SQL statements “behind the scenes”, that should not be your first perspective when building your queries. Use the ORM to retrieve your objects as needed, and then look to optimize them if and only if there is something to be gained by doing so.

Now, regarding your specific example here, it’s going to be a lot easier if you provide the actual models (or a sufficient subset thereof) and the details of the data you are looking to retrieve. The problem with contrived examples is that they tend to get confusing when trying to relate them back to your real models.

3

Hi Ken,

Here is the list models.
The applications model has applno as the primary key.
The other fields give information related to the application.
For each application there are a number of products.
The products model give information on the product.
Each product has varying number of excipients.
These are listed in the excipient model.

class Applications(models.Model):
    applno = models.IntegerField(primary_key=True)
    appltype = models.CharField(max_length = 100)
    applpublicnotes = models.TextField(blank=True)
    sponsorname = models.TextField(blank=True)

    class Meta:
        managed = False
        db_table = 'applications'
    
    def __str__(self):
        return str(self.applno) 
        
       
class products(models.Model):
    id = models.IntegerField(primary_key=True)
    applno = models.ManyToManyRel(to = [Excipients], field = 'applno')
    productno = models.IntegerField()
    form = models.TextField(blank = True)
    strength = models.TextField(blank = True)
    referencedrug = models.IntegerField()
    drugname = models.TextField(blank = True)
    activeingredient = models.TextField(blank = True)
    referencestandard = models.TextField(blank = True)

class Excipients(models.Model):
    id = models.AutoField(primary_key=True)
    applno = models.ForeignKey(Applications, to_field= 'applno', on_delete=models.PROTECT, db_column='applno')
    productno = models.IntegerField()
    excipient = models.TextField(blank=True)
    excipient_quantity = models.TextField(blank=True)
    excipient_unit = models.TextField(blank=True)

    class Meta:
        managed = False
        db_table = 'excipients'
        
    def __str__(self):
        return str(self.excipient)

I wanted to search the excipient field in the excipients model.
If there is a match then display information from all the three tables.
My current view is able to join the excipients table and the applications table but I am not able to access information on the products table.

def get_queryset(self):
        query = self.request.GET.get('search')
        applno_values = Excipients.objects.filter(excipient__icontains=query).values_list('applno', flat=True)
         
        return Excipients.objects.select_related('applno').filter(applno__in=applno_values).order_by('applno', 'productno')

Thank you for your help.

4

Side Note: When posting code here, enclose the code between lines of three
backtick - ` characters. This means you’ll have a line of ```, then your code,
then another line of ```. This forces the forum software to keep your code
properly formatted. (I have taken the liberty of correcting your original posts.
Please remember to do this in the future.)

The first issue is that your many-to-many relationship between products and Excipients isn’t defined properly. See

Pay particular attention to the examples shown in that second page.

Second, don’t try to “over-manage” Django’s management of models unless you have a real and defined need to do so.

Is there a reason why you have managed = False defined for both the Applications and Excipients models? (This seems odd to me, especially since you don’t have this on the products model.)

In the general case, you don’t really want to use the to_field and db_column settings of the ForeignKey field. It doesn’t make any sense for Excipients, because the applno field in Applications is the primary key, so that’s the field that is going to be used anyway.

Having the ForeignKey field in Excipients named applno is both confusing and misleading. Within the context of the ORM, the applno field is actually a reference to an instance of Applications.

Next, since Excipients is the “many” side of the many-to-one relationship, you can always refer to the related Applications object through the foreign key field.

What this means is that for any instance of Excipients, you can directly access the Applications object through the foreign key field name.
For example, if you have:
an_excipient = Excipients.objects.get(id=1)
then the Applications object related to an_exicipient is access as an_excipient.applno.
(This is where your mis-named field causes confusion. The expression an_excipient.applno does not give you the applno value.)

Also, as mentioned in the ManyToMany docs referenced above, all related objects through that relationship are accessible through the related manager using the related table name with the _set suffix.

In this specific case, again with the example:
an_excipient = Excipients.objects.get(id=1)
then all related products are accessed by
an_excipient.product_set.all()

Finally, you generally want to avoid using the values functions, again unless you have a specific need for it. It becomes a limiting factor within your views and templates.

5

Thanks Ken. For the products model I forgot to copy the meta class. managed = False is also present for the products model.

Removing to_field worked fine. When I remove the db_column setting django points the foreign key to applno_id field in the applications model. So I had to put it back.

Also I updated the applno field in the products model from ManytoMany to ForeignKey as Excipients model since in the applications model applno is the primary key.

When I use Excipients.objects.select_related('applno) I am not able to get the drug name data. Is thee something I am missing.

6

Again, the select_related has nothing to do with this. It is only a performance enhancement, it has nothing to do with the ability to access related data.

Please post your current models.

7

Here are the current models

class Applications(models.Model):
    applno = models.IntegerField(primary_key=True)
    appltype = models.CharField(max_length = 100)
    applpublicnotes = models.TextField(blank=True)
    sponsorname = models.TextField(blank=True)

    class Meta:
        managed = False
        db_table = 'applications'
    
    def __str__(self):
        return str(self.applno) 
        
       
class products(models.Model):
    id = models.IntegerField(primary_key=True)
    applno = models.ForeignKey(Applications, on_delete=models.PROTECT, db_column='applno')
    productno = models.IntegerField()
    form = models.TextField(blank = True)
    strength = models.TextField(blank = True)
    referencedrug = models.IntegerField()
    drugname = models.TextField(blank = True)
    activeingredient = models.TextField(blank = True)
    referencestandard = models.TextField(blank = True)

class Meta:
        managed = False
        db_table = 'products'
        
    def __str__(self):
        return str(self.drugname)

class Excipients(models.Model):
    id = models.AutoField(primary_key=True)
    applno = models.ForeignKey(Applications, on_delete=models.PROTECT, db_column='applno')
    productno = models.IntegerField()
    excipient = models.TextField(blank=True)
    excipient_quantity = models.TextField(blank=True)
    excipient_unit = models.TextField(blank=True)

    class Meta:
        managed = False
        db_table = 'excipients'
        
    def __str__(self):
        return str(self.excipient)
8

And for clarity, what you’re looking to get for an individual instance of Excipients, all of the products instances related through the same Applications?

If so, then if you have an instance of Excipients named an_excipient, the related products would be an_excipient.applno.products_set.all().

9

It gives an attribute error saying manager isn`t accessible via Excipient instances

10

Please show the actual code you are trying.