Finding remote relatives

I have a set of models that link, in various ways, across (at least) six or seven tables. I am having trouble finding the more remote of these tables in a Django queryset. As an SQL query I can do this:

select  *  from profile
    join directorylink_profiles on profile.id = directorylink_profiles.profile_id
    join directorylink_3 on directorylink_3.id = directorylink_profiles.directorylink_id
    join directorylink_2 on directorylink_2.catid = directorylink_3.uplink_id
    join directorylink_1 on directorylink_1.catid = directorylink_2.uplink_id
    join directorylink_0 on directorylink_0.catid = directorylink_1.uplink_id
    
    where profile.directory_optin = 1
    group by directorylink_0.id
 ;

My models all link to each other via ManyToMany or ForeignKey, as marked-up:

profile -> (manytomany via directorylink_profiles) -> directorylink_3
directorylink_3 -> foreignkey -> directorylink_2
directorylink_2 -> foreignkey -> directorylink_1
directorylink_1 -> foreignkey -> directorylink_0

However, with the queryset:

Profile.objects.filter(directory_optin=True)

As far as I can get is the first line of the extended relationship, ie:

profile -> (manytomany via directorylink_profiles) -> directorylink_3

Any help or pointers gratefully received.

Assuming your FK relationships are all defined correctly, you can chain double-underscore relationships together using the field names in the relationships.
See Lookups that span relationships.

Ok, I got there in the end:

from django.db.models import Count

DirectoryLink0.objects.filter(directorylink1__directorylink2__directorylink3__profile__directory_optin=True).annotate(total=Count('id'))

Did not really need the total but this appears to be the only way to group by.

Thanks

As I am understanding it, if you have a queryset, across models, that works, then the reverse of that should also work, ie:

DirectoryLink0.objects.filter(id=(self.kwargs['pk']), directorylink1__directorylink2__directorylink3__profile__directory_optin=True).annotate(total=Count('id'))

and

return Profile.objects.filter(DirectoryLink3__DirectoryLink2__DirectoryLink1__DirectoryLink0__id=(self.kwargs['pk']), directory_optin=True).annotate(total=Count('id'))

should yield the same results. But, the second query throws an error Cannot resolve keyword 'DirectoryLink3' into field…

FWIW I have tried it all lowercase, but that makes no difference.

The other issue perplexing me is how to access the field values in these referenced models on a template!?

At this point, I’d need to see the actual models involved to properly identify the fields being used and to understand the relationships between them.

Referencing the field values is going to depend upon whether you’re following the FK relationship “forward” or “backward” (whether you’re following the chain through the “one” side or the “many” side of the Many-To-One relationship.)

Well, I’d like to know how to be able to do it both ways.

Here are my models, I’ve cut out the non-essential items for brevity:

class Profile(models.Model):
    …
    user = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    …
    directory_optin = models.BooleanField(_('Directory opt-in'), default=False, db_index=True)
    …
    directory_classes = models.ManyToManyField('DirectoryLink3', through='DirectoryLink_profiles')

…

class DirectoryLink3(models.Model):
    id = models.BigAutoField(primary_key=True)
    uplink = models.ForeignKey('DirectoryLink2', to_field='catid', max_length=16, blank=True, null=True, db_index=True, on_delete=models.SET_NULL)
    catid = models.CharField(max_length=16, blank=True, null=True, db_index=True)
    description = models.CharField(max_length=128, blank=True, null=True, db_index=True)
    createdate = models.DateTimeField(auto_now_add=True)
    moddate = models.DateTimeField(auto_now=True)
        
class DirectoryLink2(models.Model):
    id = models.BigAutoField(primary_key=True)
    uplink = models.ForeignKey('DirectoryLink1', to_field='catid', max_length=16, blank=True, null=True, db_index=True, on_delete=models.SET_NULL)
    catid = models.CharField(unique=True, max_length=16, blank=True, null=True, db_index=True)
    description = models.CharField(max_length=128, blank=True, null=True, db_index=True)
    createdate = models.DateTimeField(auto_now_add=True)
    moddate = models.DateTimeField(auto_now=True)

class DirectoryLink1(models.Model):
    id = models.BigAutoField(primary_key=True)
    uplink = models.ForeignKey('DirectoryLink0', to_field='catid', max_length=16, blank=True, null=True, db_index=True, on_delete=models.SET_NULL)
    catid = models.CharField(unique=True, max_length=16, blank=True, null=True, db_index=True)
    description = models.CharField(max_length=128, blank=True, null=True, db_index=True)
    createdate = models.DateTimeField(auto_now_add=True)
    moddate = models.DateTimeField(auto_now=True
        
class DirectoryLink0(models.Model):
    id = models.BigAutoField(primary_key=True)
    uplink = models.CharField(max_length=16, blank=True, null=True, db_index=True)
    catid = models.CharField(unique=True, max_length=16, blank=True, null=True, db_index=True)
    description = models.CharField(max_length=128, blank=True, null=True, db_index=True)
    createdate = models.DateTimeField(auto_now_add=True)
    moddate = models.DateTimeField(auto_now=True)

class DirectoryLink_profiles(models.Model):
	raw_id_fields = ('directorylink',)
	id = models.BigAutoField(primary_key=True)
	catpath = models.CharField(max_length=512, blank=True, null=True, db_index=True)
	directorylink = models.ForeignKey(DirectoryLink3, null=True, on_delete=models.CASCADE)
	profile = models.ForeignKey(Profile, verbose_name=_('profile'), null=True, on_delete=models.CASCADE)

The DirectoryLink0.uplink field is redundant, therre would be nothing to link to.

Try:
Profile.objects.filter(directory_classes__uplink__uplink__uplink__id=...)
(I think I’ve got the right number of uplink references)

Since you’re following the links forward, you use the field names instead of the table names.

I misunderstood the documentation on that issue, I thought it was saying upper and lowercase model names going forward, and lowercase in reverse. Got it now, thanks.

I think I have managed to sort out all the field display on the template, except one, how to get description from the final uplink where:

queryset = Profile.object.filter(directory_classes__uplink__uplink__uplink__id=(self.kwargs['pk']), directory_optin=True)

From that I can get the description(s) like this:

{% for object in object_list %}
     {% for category in object.directory_classes.all %}
         {{ category.uplink.uplink.uplink.description }}
    {% endfor %}
{% endfor %}

However, there could be ten or more in that list, where I just want the one referenced in the queryset: …uplink__id=(self.kwargs['pk'])….

Thanks

I’m not sure I’m following what you’re trying to do here. In that template snippet you provided, what is object? (Is it the list of Profile objects from the query above?)
Or, looking at this from a different angle, what are you actually trying to generate in this view, and what information is being supplied to the view?
(The reason I’m asking is that if you’re trying to get some list of DirectoryLink0 entities, that should be the “base” for your query, not the Profile object.)

object_list is the list of profile objects, and object is each iteration.

Yes, I figured-out that making DirectoryLink0 the root of the query would give me easy acccess to the fields at that end of the query, but the problem with that is that I’d then have the same issue getting the Profile fields as I am now having getting the DirectoryLink0 fields.

So, from the Profile end, how do I get the field DirectoryLink0.description. To compound the problem, this value will be used several times on the page.

Thanks

At that point then, I’d do the work in the view, passing along the appropriate data structures to the template in the context. It’s probably going to be easier (and more efficient) to organize your data there. Can you post the view rendering this particular template? (And probably the complete template as well so that I can understand what you’re trying to render.)

The issue with this query:

queryset = Profile.object.filter(directory_classes__uplink__uplink__uplink__id=(self.kwargs[‘pk’]), directory_optin=True)

is that it’s going to give you a Profile object (that’s what I call the “base” of the query) that refers to the final element in the chain. It does not return all those intermediate elements - they’re actually not relevant to the resolution of that query.

I thnk part of the problem here is that I would usually access this data via an SQL query, and expect to control exactly what is returned by that query. Here I am kind of fumbling around in the dark, and getting stuff I probably don’t want and not getting stuff I do want.

Anyway, doing the Django Tango, I can get the uplink.description/DirectoryLink0.description I want like this:

	def get_context_data(self, **kwargs):
	
		context = super(DirectoryEntryView, self).get_context_data(**kwargs)
		
		parent_section = DirectoryLink0.objects.get(id=(self.kwargs['secid']))
		context['parent_section'] = parent_section
		
		return context

I feel very wary about overriding methods. Is there somewhere I can read some information about this technique?

The whole reason behind using class-based views is that they provide you with methods intended to be overridden. There’s no reason to be wary about that - that’s how they’re designed to be used.

So, is that the right way to override get_context_data?

I’ll do some more research on overriding methods.