Django, what the JOIN? with Simon Charette

Speaker 1: Welcome Simon. We're really glad to have you join us again as the guest speaker for session five. Amazing introduction for Simon. He has been a longtime contributor to Django. He has been contributing to ORM for more than a decade and is an amazing, amazing community member He has served on the Django 5. x Steering Council and is also part of the security team and the Triage and Review Team. He has a background in software engineering and also works as the principal back-end engineer at Sapier. So that's there's a lot of awesome, awesome stuff that he builds, that he does. And to give a little sneak peek into that, he's here with us today to share

Speaker 1: about the talk, Django, What the Join. Such a cool title. We are super excited to Deep dive into it and learn how the ORM turns relational references within the join. Welcome Simon, over to you.

Speaker 2: Thank you, Priya, and thank you for having me a second time. I'm really happy to be here. So before diving into the subject, bit of a small introduction. I'm based in Montreal and you know for the rest. So if you ever pass by Montreal and you want to discuss Django stuff, let me know. Hit me up on GitHub on our Mail. I'd be happy to discuss it with you. So before diving into the deep of the subject into the subject in terms of how the ORM turns reference to different models into actual SQL joints. It gets worth spending a bit of time understanding what a join is. Um so as you know, the RM

Speaker 2: um allows you to uh map python classes to um tables in relational database systems such as sqlite postgres and so on um so these systems allow you to define entities which have some property represented by columns and each row represent a n instance of your um Entity. And the strength of these systems is that just like in the real world, you're able to relate these relationships together. So that's a way to kind of like model the world, model data around it And in this space, a join is a way to combine data. So if you have data from one side, for example, author and books on the other side. join um instruct

Speaker 2: the relational database and how to project the data, how you'd like to combine the data, do a form of intersection between both. So you can think of a way as having matching records between two tables combined together and there are a few ways to do that. There are several types of joints. The ones are going to be of interest to us and the ones that are the most common. are an inner join. So an inner join is, and we'll go into more details about that with examples, but it is a strict intersection between two data sets. So if you have a data set, a table on side A and another table on side B, you try to combine them, then only data that is present in both will be presented in or join. An outer join is subtly different, so it will include the strict intersection, but for parts that are not in the intersection

Speaker 2: we uh will the database will provide null values for that. Um these joins are uh preceded by a left and alter, uh sorry a left or a right. specifier and the reason for it is that it determines uh which side of the relationship so on the left so or a or b left hand side or right hand side should have the null values if this is confusing to you um now we'll go a bit into more details on that subject. So let's look at this very um simplified um representation of a of a book in a publisher. So in this case we have a uh a book model um which has a relationship to a publisher and and this relationship is is nullable um so um if we are to

Speaker 2: create a publisher and we are to create book. In this case we would be creating one book that is associated with the publisher that we just created. And the other one that is not. So just a way to model the fact that there might be some independent books that are released. In this case, if you were to be using NerdJoin, as I mentioned previously, only matching records between two tables involved in I guarantee publisher and the author would be returned. And if there is no match, then no row would appear. So if we were to do a an inner join between the book table backing the book model and the publisher model, uh publisher table backing the publisher model, and we were to join between both of them.

Speaker 2: Well we know that in the case of one of our books it has no publisher so when we try to intersect it with the um publisher table there cannot be no there cannot be an intersection between both data sets so in this case the rope would be um excluded If we were to do a left auto joint, the difference, as I mentioned previously, would be that we would get this strict intersection. So By intersection, I mean the intersection on the specifier for the joint. So in this case, you can see publisher ID equals book publisher ID. This is what determines what the intersection should be on. A left auto join which will return this re intersection and will also return rows on the left. So in this case, the table that is on the left of the join is the book one

Speaker 2: that as no publisher and for the rows that have no publisher it will set null in there to represent that it's outside of the intersection So if we were to do a left outer join, so very exact same query, but instead of using an inner joint, we use a left outer join. we would get a different data set being returned by the database. We would get a published book, an independent book, and everything, L, the columns that relate to the publisher table that we joined would be null. So a small summary of it, the inner and inner joint is the strict intersection between both tables, and an outer is the street strict intersection plus, and this plus uh uses null

Speaker 2: to represent the lack of intersection between both tables. So uh when does the ORM span join exactly? Um well you might be familiar with the the under the under syntax between fields. So if you use method like filter or exclude um you could be doing something like give me all the books that um have an author uh which name starts with uh Simon for example or something that You could be using Select related as well to refer to relationship. And another case as well, which doesn't include the de-under -de-under syntax, also known as the double underscore syntax. Are reverse relationships. So when you define a foreign key through Dejjango RM, you're able to specify a related name. And when you do that, since the relationship is not defined in the model,

Speaker 2: It will still span join because it needs to kind of like join back to the table where the fill was defined. And so that's when joins are spun. And in order to understand which kind of joint is yours, it is important to understand why it's why one should be picked over the other. As we saw previously, the accuracy of the results is affected by the kind of joints that you're going to be using, right? Just by changing the joint type, we've got a different set of results and you can imagine that as more model get involved or table get involved and the set of results that you would get depending on the type of joint would would greatly differ between both The second one is that there are some cases,

Speaker 2: and we'll look into them into more details, where one could be one or the other journals could be used interchangeab interchangeably. Yeah, I think I got that right. But it's still preferable to pick the uh inner one or the inner over outer. And the reason for it is that When you ask the relational database to evaluate your SQL and turn that into actual code where it fetches data here and there on disk and so on and combine it together. If you tell it about an inner joint, it knows if you use the inner joints of an outer joint, it can make some decision with regards to how much memory is going to be required to combine both data sets. because the intersection is likely going to be stricter than what we have on

Speaker 2: the left side. So it helps the even if some in some cases you could be using an outer joint, the case of another inner joint and get the same results. it is preferable to specify explicitly to the database that you want an inner join because it may it can make some optimization for it. So that you might have, if you're familiar with SQL, you have heard in the past like, oh it's pretty bad if you use like a lift outer join it should be using try to use inner join that's one of the reasons for it. Inner joins are easier to optimize. It's easier for the database to reflect about it and return the results in a faster fashion. Last thing before we dig more into the rules uh into how inner and outer are um picked up. Is the um something we should know about how the uh SQL null behaves and what it means basically.

Speaker 2: So um if you're familiar with uh more Python and you use none, for example, and you use um um other strings, you might be surprised to know that if you compare Python to SQL, if you do something like none equals none in Python, you would you would get true, right? Well in the case of SQL, null is used to represent unknown. So if you do null equals null, you will always get fault. out of it. And that's something that is very surprising to folks that are not familiar with with SQL. But one way that you can think of it is since null is meant to represent something unknown. If you don't know about something and you don't know about something else, then you can't know that they are equal, right? These are two unknowns.

Speaker 2: They could be equal or it could be not. So that's one of the memonic tricks that I use to uh reflect about that and that's important for the following because um when you do specify for joints for example inner join uh publisher um on publisher uh author. publisher ID equals publisher, well you're using the equal operator. And that means that if none cannot equal none, then in some cases you can you can take shortcuts in this regard. So the four rules that we'll look over in terms of like when does Django picks one joint over the other? The first one being that non-nullable relationship are always a good candidate for using Ender. Because if there's

Speaker 2: no null value, then um in this case uh there's no um reason to have special null handling uh if the relationship is not nullable so for example if we knew from the get, if we had defined our data model privacy in a way that each author must have a publisher, there's no reason to even use left author joint because we know that an author can only exist if there's a publisher associated with it. We'll go over all of these rules in more details. The second one is that if you have a nullable relationship, but you're constraining it in a way that removes null from the relationship. So for example, say that you have a an author publisher that can be nullable, but you're only interested in

Speaker 2: an author that have a publisher with a specific attribute then in this case you can ignore nulls and in this case the first rule applies and you it becomes a non-nerable relationship We'll go over all of these rules into details, but this is more like an overview. The third rule is that there is an exception to the second rule, is that there are some functions that behave differently with regard to null. So if you can think of some of them already. uh we will I'll you will have time as well to um uh I will ask uh if anyone knows about them. Uh and lastly uh there's another rules that if you uh are doing multiple joins. So if you have a very nested relationship that you need to up uh with the de-under the under

Speaker 2: syntax through multiple relationship Then the moment you've got an outer join because it's not eligible for an inner join, everything that comes after must be outer as well. So that was a lot. Let's dig into each of these rules one by one So the first one, non-nullable relationships are always candidate to be inner. So that is the case for select related. filter, um but it is not the case for things like reverse relationship, right? Because in these case they are nullable. What I mean by that is that um If you look at again our author and publisher models from the publisher perspective, it since the author um since the reference is coming from author to publisher,

Speaker 2: There's no way from the publisher perspective to know if the data exists on the other side. And it might not exist, and it is considered nullible. Let's look at a particular model set to understand this a bit better. So got a simplified reservation system here where we've got a first room model and we've got a reservation model that has a room uh foreign key and it has a reverse name a related name called reservation which means that if you try to You want to refer to reservation from room, you're also able to do so. If we do something like reservation. object. sy related room, we are asking the ORM to start from reservation and

Speaker 2: also fetch all the data that relates to room. And in this case, we know that we cannot define a reservation without a room, like it's a it's a the feel is not nullable. There's no way to create a row reservation without without a room So in this case, we know that the relationship is not nullable, and we can use an inner join when joining between both. If we do it on the other side though, so from the room perspective, in the data model that we have, it is possible that you create room and no one has reserved them. So from the room perspective, the relationship is nullable. So if you start from room and you go to preservation, like it's the case here, uh

Speaker 2: you need to use a left out rejoin because otherwise you would be excluding room that don't have any um uh any reservation in your data set and might that might not not be something you um you are uh expecting. So that's the first rule. The second rule is that if you have a knowledgeable relationship, so for example, the second one that we looked at that was going from room to reservation, you can have a room without reservation. But You are applying a filter that specifies that you are interested in some reservation, so some reservation between a date, for example, then the moment you do that Because you're only interested in a subset of the relationship, the relationship becomes not nullable

Speaker 2: because if you are interested in only the room with reservation in 2026, for example, well, all the ones that don't have reservation at all, they are not candidate. Hence the joint can be, even if the relationship is nullable, can be turned from a lift outer join to an inner join. And that's because null, as we saw per C, does not equal anything. Null does not even equal itself. And when that's the case, the first rule applies. Let's look into this with an example again to understand it a bit better. Here I've got the similar data model that we had previously where we've got an author That relates to a publisher and the relationship is null. So it's it is it is nullable.

Speaker 2: If we are to do something like author. objects. filter and we are only interested in the author that have a publisher name equals foo. then normally because the relationship is nullable you could um you would have to use a left outer joint but since you're only interested in outer that have a publisher because if they don't have a publisher they can't have a publisher that have a certain name then in this case you can turn the left outer joint to a null. So if the filter that you're applying are constraining nulls, like we see here. It promotes relationship. You can use an inner joint, and it's easier for the database to reflect about it.

Speaker 2: So uh I alluded to that previously, but there's a slight exception to it. It's not all the the filter that you pass to um uh the um uh the filter method that will um restrict a notable relationship. So I'm curious to hear maybe uh Mr. Priya if people can raise their hand or just uh speak but uh if can anyone come up with a an exception um to the rule that uh does special null handling when you call into filter I can

Speaker 3: make a guess. Uh but are you asking about like the case-when expressions? Is that maybe one of them?

Speaker 2: Uh you could have one expression that uh do it, but there's um even kind of like finer grain ones. Um So maybe one more guess if someone like a lookup or um a function that deals with null in a special way

Speaker 4: Is it the in expression?

Speaker 2: In does some specialized endling with it, right? Yes. But uh it's it's not uh uh in this case but yes it does something special because if you have uh in null um you need to specialize special cases but yeah that that's a good that's a good one um All right, I I'm going to to move forward, but that was these were kind of like two good examples. So the one I am referring to is the one like uh the is no lookup, right? Um so if you are partially interested in something that is null, then it's very important that the null are just are not removed from the expression, that you don't go from a left outer joint to an inner joint that's removed null. There are some other functions that the framework provides, such as Coal S. If you're not familiar with Coal S, it's kind of a similar to the MySQL

Speaker 2: if null function. You can pass it multiple arguments and it will return the first one that is not nullable. So it is very important that if you do a filter by that, well, the framework does not make an optimization that changes the semantic of the of the query by removing null from the equation. So let's look at this exception in detail. If we compare to what we had previously, it's the same data model with the author having a nullable relationship to publisher. If we do something like publisher equals name full and publisher or publisher name isn't all, then in this case it's pretty important that we don't do an inner join against publisher because otherwise it would elide or remove um all the author without publisher from the equation and we could not apply the where.

Speaker 2: So the framework is smart enough to see these things even in very nested Q objects and say, okay, no, you know what? I I cannot um Uh I cannot make this absension here. It's important that null are not removed from the equation. There is unfortunately bugs on this front that exist, that we are aware of Um but um not sure if you've played with it, but it is possible nowadays to use um lookups directly. So um Semantically the these two queries should be equivalent. So here you see that we do Q publisher name is not true In this case, we specify it using the lookup instance directly. So if we create a lookup isn't all and we pass it like that. So we refer to the feel and we set it to true.

Speaker 2: Unfortunately, the RM is not aware of that. And the reason for it is that The reason why the first case work is that the isnald string lookup check is hardcoded in the RM. It's not something that is a property of expression that denotes kind of like a flag that tells the RM, you know what, I'm special with regards to with regards to null. But yeah, this is in the process of being fixed. We talked uh with about it uh with Jacob. uh the fellow at uh Django under man um last week. So the last rule now um if you do joins against An outer joint table, even if you are normally admissible with the previous rules for an inner joint, you must stick to iter.

Speaker 2: And this will become apparent why in the example, but it is mandatory to preserve null values. And the reason is that if you have something that has null values, and you need to preserve them, for example, uh author with publisher being potentially null. And you go one step further and you try to do an inner join with null values on your left hand side as we know null does not equal anything so augmenting the joint chain would remove results from the previous joint so let's look at an example to understand that a bit better If we have author publisher on the bull, but now we have another model, um, location. Obviously this thing would have like details and so on about what it is. It's not important for the sake of this example.

Speaker 2: But publisher now has a foreign key to location , which is, I'll point it out, not nullable in this case If we do a publisher. object. select related location, well, we know that from the publisher relationship to location. The relationship is not nullable, right? You cannot create a publisher without a location. It is hard coded in our data model here. We don't set it as null through. Well in this case we are able to use an inner joint. So all fine on this run. Things change so when you start the relationship from the author side. So if you start from author You go through publisher, then you want to go to location. Well, now your publisher relationship has become nullable

Speaker 2: because in that transitively or um it it applies to uh further joins um by the end so what was previously a ninnar join from uh publisher to location now becomes a left out or joint between both because now publisher is a nullable relationship because it's coming uh it's originating from otter where it is defined as nullable So these are the the four rules. There's some optimization around it as well. Yeah, it's it's a lot to kind of like wrap your heads around all these things. Um hopefully if you are curious about how these things are are being put in place, I would suggest you have a look at these two methods on the

Speaker 2: SQL query method, one that relates to setup join and join. The first one being the logic that turns a the string that you're used to that is relationship underscore underscore other relationship underscore underscore field into an actual join data structure and the second one Performs join reuse, which is the last area I'd like to cover. So what join reuse is exactly? As you No, when you build a query set, you're going to be passing it around, right? And in some cases you're going to be calling filter on it, going to get a new query set, you're going to be passing it around in another location. And if Django was creating join every time a new filter call is made or every time the same relationship is reference and reference all over again, well um

Speaker 2: well database wouldn't be happy about that, right? because it would uh need to do unnecessary work um and uh I mean the database but also you as some person potentially that needs to maintain this database and build fast application will also not be happy about it. So there's some logic in the RM to perform join reuse and there are some um pretty there are some rules to determine if a join can be reused between filter calls. The first one is that if you make multiple references to the relationship in the same reset method calls, for example, select related , filter or annotate then the joint will necessarily uh be uh reused. If you are targeting and the other a rule as well is if you are calling multiple methods but they are all targeting

Speaker 2: single valued relationship, then it is legible. So to explain what a single value relationship is, if you're not familiar with the one-to-many or one-to-one or zero to one uh way it's just a way to to say like um how does this model relate to to this one um can this um publisher have many books so it's a multi-valued relationship Or can an author have only a single publisher? Or can a public can a can a publisher have a single location? So it's it determines like is the relationship uh does a relationship as up to one value or up to many value. But we'll look into example as well to explain that a bit more. So back to our

Speaker 2: set of models that we add. Augmented it now with uh two more fields, being the author name and the uh DOB for date of birth. Obviously, this is an oversimplified representation of the world, but it should serve us well for the purpose of this demonstration. So if we are to make a single query set method call in this case, and we are targeting a multivalued relationship, what I mean by that again by multi-relationship is that from the publisher perspective A publisher can have many authors, right? It's the reverse relationship of a one-to-many. So from the publisher perspective, there are many of them. If you make a single filter call and you ask for give me all the authors that add their name that start with Simon and their date of birth is um

Speaker 2: somewhere around that time and then in this case um Django will use a single uh a single join and you will notice here that even if it's nullable Since we are making a filter that restrain or exclude no values, we are using an inner join. But the important part here is that there's a single join If you do to make two distinct filter call, it will result in two um different joins. And the reason is that um For a multi-valued relationship, there is a nuance between what exactly you mean when you target it. So when you target multi-value relationship, you could be interested in two things. For example, in this case. Are you interested in all the publisher that have

Speaker 2: at least one author that both have its name that starts with Simon? and as its date of bait the date of birth year in 1987? Or are you interested in the publisher that have at least one author that start with Simon? and another author that starts with uh that adds their date of year in 1987. So there's a bit of nuance between the both uh between both, right? Are you interested in one member of the relationship that matches other criteria? Or are you interested in um publisher that have at least one author that match one or the other. And the way Django allows you to specify one or the other is through multiple filter code. This is very confusing to

Speaker 2: folks coming to the RRM. It is kind of constraining sometimes as well because you want to pass query set around, but you'd like to reuse a joint that was previously used. Unfortunately, the RM today does not allow you to specify that you'd like some joints to be sticky or you'd like to say like, hey, you know what, I I'd like to be able to reuse the joint if it already exists might come um in the future, but today that's that's how things work. So um for a multivalue relationship, if you make one or two distinct filter calls, it will result in uh potentially multiple joints being issued It is not the case too with single valued relationships. So in our data model, an author can only have a single publisher So even if we make this thing filter call, since they're all intersection

Speaker 2: over the other and they can refer to only one element relating to the author, DRM is going to be using a single join and systematically reusing it across a notate filter. If you were to add 20 more of them, uh it would still do the uh the same. So um yeah so the rules are basically if you have a single value relationship then you the joint will be reused if uh it is a multivalue relationship then depending on if you um refer to it multiple times in the same method query set method call or if you do it in um uh in in multiple um then uh you will end up with a different behavior. So um that was all that hopefully that was useful. Um I I will go through like a small summary of what We just

Speaker 2: saw. So first thing we looked at what a joint is. The second one was a small explanation between what an inner and an outer joint is. The second one was explaining the semantic around null, the kind of like weird behavior with the fact that it does not equal anything. and thus its use to represent unknown. And if you don't know about two unknowns, then in this case, well, you cannot take a decision with regards to are they equal or not. Third one was around the inner versus outer rules. Relationship are nullable. Is the nullable relationship constrained the null function, null endling function such as is null and coalesced? And the second one being the transitive property of alter

Speaker 2: joins. And last one, the OR RAM join reused for single value relationships. Hopefully these provides you tools, make the RM a bit less intimidating. Obviously there was a lot there squeezed in a very small amount of time. So yeah, I'm happy to answer further questions that you might have.

Speaker 1: Thanks Simon. That was such an insightful and well-curated in-depth content. Thanks a lot. So, yes. We are open for questions. I can see the first question from Eddie regarding how to ignore none during compilation phase. Eddie, would you like to go first and ask the question? Or should I defeat it? Okay, I think that I was okay. I'll I'll go over to the question in the chat. Okay, that's totally fine. I would like to know more about ignoring none during compilation phase

Speaker 1: in qs. exclude. Foo underscore underscore in is equal to none case, I still get none values in the objects retrieved from the DB. Is ignoring none still a relevant approach to deal with the conversation into SQL, or is there a better suggestion about it?

Speaker 2: Yeah, so um I know there's a ticket are open about it. I'd like to kind of like refresh my uh my context around it My understanding is that um SQL because of how weird SQL is with regards to null values. Um And the best effort that the RM makes in trying to make it less weird when you pass or almost transparent when you pass none in a any specifier. So for example, when you do exact uh none it will turn it into isn't all uh without you having to do anything. Um there is some um if I remember correctly the moment that you have a non-value in a NIN lookup, it will always be uh unknown um

Speaker 2: because uh it it cannot be cannot be certain and so on So in terms of is it the right way to fix it, I would have to look more about the ticket. I'm not sure that ignoring none is the best way to do it. I feel like in some cases one thing we could do is if we see that we are passing a literal list that contains non value, something we could do is turn it into a remove the null the non-null values and add a or field is null or something like that try to uh add it further um But yeah, I I would I would have to do to look a bit into it. I know that there's been a discussion as well where there's a NSQL function called any

Speaker 2: , which behaves very similarly to the in lookup but treats none slightly differently. So maybe that could be an avenue as well. But yeah, you yeah, you might be using exist as well if you're passing subquery because all the best effort that Django will do with regards to passing in a particular list, it won't be able to do if you pass in subquery. Because when that happens, well, it's not a literal set of values. So there's Django has no control over what if whether or not the subquery can be itself returning none. and throwing uh the result away. So hopefully that was that was useful. But yeah.

Speaker 1: Thanks. Next question we have is from Tim. Tim would you like to go?

Speaker 3: Yeah, I think it's gonna be similar uh related to Emma's question. Following this as well. But um it's around use the term sticky for filters when you have a many to many field and you do multiple filter calls. I know you said we don't support that. Have you are you aware of any discussions from the past about trying to implement support for it?

Speaker 2: Yeah, uh so there are a few tickets about it. Uh the fact is the the ORM itself makes a lot of use to it internally. The reason is that If you do have a related manager, so for example, so that you have um a publisher instance to go back to our um uh to our model and and you do something like uh publisher. books dot all uh well the query that is specified there um is going to have a filter of the form uh well give me all the the book like looking back the relations reverse relationship and um What Django has done over time is that the first filter call that you make, so if you were to do like publisher that books. filter, will be sticky to the internal filter that was applied internally, so to allow you to

Speaker 2: uh took into that and avoid doing multiple joins. So yeah, the ORM use it internally. I believe that the best way forward there is to allow for relationships to be explicitly marked as What I mean by that is that today relationship between models can only be defined declaratively, so it need to be on the model itself. I believe that there's a way to expand on what we have with filtered relation and have something that is more similar to what we get in, I think it's called Django. relativity or Django relative. It basically allows you to define relations. And I think that the best way to go for it with that would be to um allow these relations to be defined through the alias

Speaker 2: method. So you'd be basically able to say I want to so from the publisher perspective you could do something like publisher. objects dot alias um author sticky or author reused and you would just uh pass a relation that is authors and sticky true or reuse true or something like that um because I don't think we can reasonably change backward competitivity on this front.

Speaker 3: So I just want to follow up on that. I had a colleague actually implement a second um filter method on the query set model that was called S filter, which just hacked in like setting sticky equals true and allowed us to to basically um squash all those filter calls together. Uh so they yeah. I don't just pointing that out. Uh in case anybody else finds it helpful.

Speaker 2: It it is technically doable. Um like it's the the the TLDRs, if you look into like these setup join and join meta, they all take a a quar called reuse and you can pass any table alias that the query knows about in there and it will use them. It's just that yeah in this case of distinct filter call we we don't do so I think there's even a choir nowadays which is reuse all or something like that. So it might make your S-filter things even easier to implement.

Speaker 3: Cool. Thank you.

Speaker 1: Thank you. The next up we have from Emma. Emma asks I have some memory of yeah please Emma, go ahead.

Speaker 5: Yeah, I have some memory of uh many to many uh relationships. Or there was one filter on the right hand side of the of the many to many that despite being in the same filter call uh with multiple filters in there resulted in um basically an or statement so uh filtering multiple times on default multiple joints Uh is that still a thing? Uh or maybe I'm just misremembering and there was just multiple dot filter calls. But I I feel like i it was all in one and the result was quite unexpected. Um

Speaker 5: is there something to know about many to many in regards to just uh simple uh joins between two tables goes many to many or joins between three tables uh instead of two.

Speaker 2: Yeah. Um I have two leads maybe that relate to that. The first one being that if you do a negation against a multivalue relationship, the RM would perform what it calls a subquery pushdown. So instead of trying to get the right semantic with regards to null and ling and so on, it will basically Take the what should be a join, turn it into a subquery, turn it into an exist over it, and do the specialized null endling on top of it Because otherwise it it becomes very hard to reason about null endling because you're negating something and then the the other joints wouldn't need to take uh care of that.

Speaker 2: So that might be one thing. So even if you have in the same protocol, and if we take our previous example where we're doing publisher. object. filter. Or I think it was author. Yeah, author. filter. publisher names equals something or publisher other property. If you were to negate one of the components on the OR, then a subquery pushdown would happen in this case. So maybe that's what you're referring to with multiple joints. It is surprising to a few people that if you you use like a negated multivalue relationship it turns uh into a a subquery pushdown and a join if you are referring the relationship in in another mean uh so that might be it

Speaker 2: um The second one is that today the RM, there's one observation that it does not do. Even if you have database constraints that are put in place. um from a uh that you have a menu relationship between um author and books so books can have multiple authors and uh authors can have multiple books, uh then the intermediary table is always going to be joined against. Well in some cases for some queries you you can basically uh pop through it, right? You don't always need to have it. Um so there are some cases where uh because um of the way the relationship is defined uh we do opt through joins that we could potentially um remove. So we could avoid joining to a table and

Speaker 2: use avoiding using the intermediary table. So that might be another thing where there's a an extra join. But yeah, it's it's It's hard for me to tell which which case uh you're pointing to. So hopefully that that relates to it slightly.

Speaker 5: Yes, it was something like so an author can have many books and a book can have many author and We 're filtering on uh books uh with author uh name like Simon and uh author name like uh charit and uh it was joining doing the join twice and so returning both uh users with Simon and Charet and I'm I'm sh pretty sure it was in the same dot filter, but maybe I'm misremembering.

Speaker 2: Okay. Yeah, it would I know for sure that in the case of a two-filter call it will add this behavior. Um maybe there's some cases in a single one, but I'm I'm not I'm not aware of them

Speaker 1: Okay, thank you. Do we have any more questions? Anyone? Okay, then I think we're all Clear and happy with today's session. Thank you so much, Simon, for joining us today. It means a lot to have you with us. Thank you. Thank you everyone for your valuable time as well.

Speaker 2: Thank you, everyone.