ORM left join

Using Django Using the ORM
1

I’ve looked through many of the other posts, but I’m not quite finding the answer I need.

I have two models:

class Album_Section_Entry(models.Model):
    album_id = ForeignKey(Album, on_delete=models.CASCADE, null=True, blank=True)
    album_section_id = ForeignKey(Album_Section, on_delete=models.CASCADE, null=True, blank=True)
    label_number = models.CharField(max_length=10, null=False, blank=False, default='0001')
    entry_description = models.CharField(max_length=100, null=False, blank=False, default='Unspecified')
    date_created = models.DateField(auto_now_add=True)
    notes = models.TextField()
    dropbox_shared_link = models.URLField(null=False, blank=True, default='', max_length=500)
    dropbox_filename = models.CharField(max_length=250, null=False, blank=False, default='')
    base_filename = models.CharField(max_length=250, null=False, blank=False, default='')
    sort_position = models.IntegerField(null=False, blank=False, default=100000)
    id = models.UUIDField(default=uuid.uuid4, editable=False,
                          unique=True, primary_key=True)

and

class Album_Section_Entry_Likes(models.Model):
    album_section_entry_id = ForeignKey(Album_Section_Entry, on_delete=models.CASCADE, null=True, blank=True)
    user = models.ForeignKey(User, on_delete=models.CASCADE, null=True, blank=True)
    liked_date = models.DateField(auto_now_add=True)
    id = models.UUIDField(default=uuid.uuid4, editable=False,
                          unique=True, primary_key=True)

What I am trying to do is to produce a list of Album_Section_Entry objects while also showing any related Album_Section_Entry_Likes as a left join. The outer query should return all albums for the specific id and include any related entries from the “likes” table.

I’m pretty sure I could do this with straight SQL, as this query works..

select *
from albums_album_section_entry
LEFT JOIN albums_album_section_entry_likes on album_section_entry_id_id = albums_album_section_entry.id
where albums_album_section_entry.id='abcde12345' and user_id = 1;

however, I’m trying to get this working using the ORM.

Thanks!

2

Side note: You really do not want to name your ForeignKey entries with an _id suffix. It’s misleading and potentially confusing.
When you have an instance of Album_Section_Entry named album_section_entry, then album_section_entry.album_id is not the id. It’s an instance of Album. In this case, the reference to the key itself ends up being album_section_entry.album_id_id. (Notice how your foreign key to User is user, not user_id.)

Now, regarding your query, you have two choices, and in neither case do you need to do anything special.

See the docs at Related objects reference | Django documentation | Django

If you have an instance of Album_Section_Entry named album_section_entry, then the set of all Album_Section_Entry_Likes associated with album_section_entry would be album_section_entry.album_section_entry_likes_set.

This ends up being resolved as two queries.

Or, you can “invert” the query, and base your query on `Album_Section_Entry_Likes.

For example, if you know an album_id for which you want to get the Album_Section_Entry and its related Album_Section_Entry_Likes, you could actually create the query:
Album_Section_Entry_Likes.objects.filter(album_section_entry_id__album_id=my_known_album_object)
This gives you the list of Likes, and every instance of that list has a reference to the same Album_Section_Entry object.

3

Thanks for the reply Ken.

Point taken on the naming of foreign keys. This has caused some confusion, but at least for now, this is a solo project, so I’m getting by.

In terms of your proposed solution, from what I can tell from your first suggestion, in order to get the set of all likes for an album_section_entry, I would first need that item, so for example, I could retrieve all likes for a user for a single album_section_entry. Also, there would only be a single entry from the likes table if the current user liked the entry, or none if they did not.

However, what I’m retrieving in the initial query is all album_section_entry items, so when I create the second query I do not have a specific album_section_entry, but rather all of them.

How do I get that second query to relate to each album_section_entry returned in the first result set?

I’ve been looking through the docs from your link but I don’t see that scenario, but I will read through them again in case I missed it.

Thanks

4

I just realized that the original SQL query was incorrect. It was only working on a single album_section_entry.

Here is what the SQL query that I am trying to generate through the ORM looks like.

select  *
from albums_album_section_entry
LEFT JOIN ursus.albums_album_section_entry_likes aasel on albums_album_section_entry.id = aasel.album_section_entry_id_id
and aasel.user_id = 1
where album_section_id_id = 'd909d6169d5747a1b6d1fbf7a7368ad7'
ORDER BY album_section_id_id;
5

There’s nothing special you need to do. You don’t need to write any query for this.

For every instance of Album_Section_Entry, by any name, then the related objects are referenced as <instance_name>.album_section_entry_likes_set.all() This means that Album_Section_Entry.objects.all() intrinsically gives you access to all the related Album_Section_Entry_Likes, without you adding any additional clauses to the query.

(Now, if you’re retrieving a lot of data, there are some performance enhancements available - but they’re not required for this purpose.)

You want to stop looking at this as writing queries, and start looking at this as accessing objects through their references.