Beyond faceted search

Speaker 1: Thank you very much. Um yes. Uh I think Mark stole my joke about numbers being a bit depleted this morning, but um the crowd has filled out a little bit, so a little bit maybe hoping there wouldn't be too many people here to see me embarrass myself on stage. Here we are anyway. Um Beyond Fested Search, yes, is the title of the talk. Um interestingly, I think Danieli set me up quite nicely for this one yesterday, because you could otherwise call this beyond Boolean search, I think. And so perhaps I have uh have a solution over the next half hour to his uh really sharpened knife that he was looking for yesterday. But let me jump into the intro, because there's quite a lot to get through. I hope I was paying attention during the speedrunning talk yesterday as well, because I think I'll need a bit of that. I'll reset my timer and I'm ready to go.

Speaker 1: About me. Yep, my name's Alex. I'm the head of product engineering at Bohurst. I've been building kind of both internal tools at the company and the platform that we actually kind of sell to our subscribers since 2014. Uh and Django is really at the core of absolutely everything that we do. So all of our platform is kind of based around Django. All of the work we do is in Python, so it's a real, like big part of everything that we that we build. Uh and this is actually my sixth time attending DjangoCon Europe, so my other bit was stolen a moment ago by Ian as well. Um yes, I unfortunately missed the uh virtual ones, but I think I've been to every in-person one. Since 2016, I think. Looking back here.

Speaker 1: And most of my camera roll on my uh on my phone has pictures that look a bit like this of Beautifully poured pints of Guinness. This will feature somewhat during the talk, because I'm not going to talk about Bohurst very much itself. But a quick intro to Bohurst first anyway. For those of us who are not aware of us, we're a data platform. uh that provides various data and information on high-growth companies uh uh and their ecosystem. Uh we've got a pretty complex interconnected data model, so we have hundreds of different models connected in different ways. Companies can do all sorts of different transactions and events can happen to them. So we have to have different ways of modeling that. Um and I describe what we have as kind of medium-sized data. It's definitely not what you would call big data traditionally. Um so there's around 12 million million registered companies in the UK with about 30 million directors.

Speaker 1: Those are two of our kind of like biggest tables in our database. That's the kind of like scale of data that we're having to build tools around. Uh that's just about the last time you'll hear about Bohurst. Because let me introduce you to a different company Brewhurst. Brewhurst is also a data platform founded a few weeks ago when I was writing this talk. Uh it provides various data information on Hi -Hop beers and their ecosystem. Um it's not such a complex uh but still internected data model. Um put a handful of models together which represent beers, breweries, stock levels of those beers at different breweries, that sort of thing. Apologies if anyone's getting flashbacks to last night uh from all the pictures of beer that will be appearing on screen throughout the uh throughout the talk.

Speaker 1: Pretty small size data. It's about as many companies as I can bring myself to add manually with the Django admin. Um and what do we want to actually do at Brewhurst? What do we want our users to do? Uh we want our users to allow to be able to search over the data that we hold on these beers, their breweries, every kind of part of that ecosystem. And our users have like a very diverse set of interests. They're not all the same. They're not all kind of working in the same industry. They have very different questions they want to ask of the data. And we don't really know exactly what they're after. So, you know, as good a job as say our product team can do of uh trying to work out what the specific use cases are for them, we want to build a search tool which allows you to do just about anything that you might want to do. And so you need to allow them to

Speaker 1: construct a really custom nested query of the data as well. And by that I mean um Suppose that we're interested in beers, but only interested in beers brewed in a particular style, made by a brewery in a particularly cut particular country. That's maybe just like one level of nesting deep But maybe we want to go even further. Maybe that brewery also needs to make a beer of another style as well, and that's interesting to us. So we want to be able to like nest in another layer back to the beers again. Is there an obvious way to kind of build a tool that allows you to do just about any search you might imagine of that kind? What are the first things you might come to? Item number one is the fasted search Boolean search thing that we were kind of talking about before. Um

Speaker 1: it allows us to apply those various categorizations, might allow us to like search over the text as well as like looking in a few categories, but it's usually quite limited in its ability to do that nested stuff. So you might be able to go one layer deep from brewery to uh from beer to brewery, but then how would you go yet another layer on top of that? And how would you say the way in which you want to go another layer deeper? Often these tools are not uh kind of designed for for doing that kind of deeper stuff. Um so maybe fastered search isn't quite right for what we want to do. Um maybe we could just do some kind of full-text search thing. Uh so maybe we shove all the uh information that we know about a particular uh entity in our ecosystem. into a text document and then allow you to do full text search of some kind over that, perhaps, because I think it's important that uh every talk over the next few days mentions large language models.

Speaker 1: Perhaps what we do is we take that document and then we um use a large language model to build a vectorization of that, and then we can allow users to just do like a text search over those things and they can write whatever words they want and maybe they can search over them that way. That's maybe one way of solving it. But when we do that we're actually kind of losing a lot of the structure that we already have in that data. by doing that. And so we're just like trusting to those large language models that again, as Danieli said yesterday, maybe maybe maybe that's not actually the the right thing that we want to be doing. Part number three, this was just a sort of little question that I I asked myself as I was thinking back to this tool I'm going to talk about, something we actually built. Probably seven or eight years ago and maybe other tools in the ecosystem have come along a long way to do this.

Speaker 1: So maybe GraphQL is kind of has some tools to be able to solve this problem in a similar way. Um I don't know enough about that to talk about that too much, but I'd be sort of interested to see if if anyone knows. knows more about that, uh do come and find me around the conference later on. Suspect I won't have time for for questions at the end of this. But for the approach I'm describing What we're going to do is we're going to kind of create a custom JSON representation of a query. And then we're going to build a tool in our back end in order to take that JSON representation of a query and convert it into some SQL. That's ultimately what we're building. Let's um have a look at a few models. No need to look at the code too deeply here. I'm just sort of like broadly scoping out what we've got. So we've got uh representation of countries and breweries in the system, and a brewery has a name, a description, a date,

Speaker 1: country. Um our models again for the Bohas platform often have way more fields than this. There's a whole load more stuff that you'd usually have accessible. We want to represent a beer which has a foreign key to a brewery. So those two models are connected in that way. And again, beers have names and a description and a date and that kind of thing. Um they also produce merchandise as well. I sort of use this as a way of making the model seem a little bit more complicated than they um they might be otherwise. So um Merchandise also connects the breweries, and again, type description data was released, that sort of thing. And then we're tracking the stock levels as well. I don't know exactly how we'd get this data, but let's assume somehow we have some inside track of all the breweries and how much stock they've got of everything.

Speaker 1: Apologies if you got the ASMR of my uh straw or my water bottle there. What might we want to know? So here's a few questions that maybe one of the users of our platforms might want to ask of our data. What are the breweries where any of the beers they have in stock are new England IPAs and where they have more than 100 t-shirts in stock concurr currently? That feels like a slightly contrived example. Again, the data that we actually use at Bohurst. Uh I said I wasn't going to mention us too much, but I have a few times now. These examples often seem a bit less contrived, but Bear with me for a moment and suppose that someone might want to know the answer to that question. Or what are all the beers produced by breweries based in the US where the brewery also sells hoodies? Or what are all the beers that were released in 2014, where the brewery was also founded in that same year, and where that brewery has also released another beer in 2023?

Speaker 1: And I think as you So I've seen some of these examples, you can kind of see how we have this kind of interconnected series of models that we need to chain through and query in order to kind of generate a query set at the end of it. Um so we need to build a representation for this for this query that we want to build. Uh I feel like the obvious example for that is maybe As I've kind of alluded, we're going to end up with some SQL at the end of this, so why don't we just start off with SQL? Maybe that's the language that we should be using, allowing our users to To build queries with. But maybe they're not very technical, and maybe also we don't want to just give them SQL access to our database because uh we might want to be a little bit careful about exactly how we do that. Um And even suppose you know we had all the security concerns and we decided to build our front

Speaker 1: end around kind of building SQL queries. Then we're kind of just like offloading the work into the front end interface. How does the front-end interface then generate that SQL that we're gonna send to the back end anyway? So maybe let's have an intermediary kind of representation of a query. So what are some properties of what we'd like this query to look like? We need to have a notion of what the base starting model is. So um in some of those questions, some of the time the thing we were looking for was beers, some of the time it was breweries, uh it could be many other different things, so we need to define what the starting model that we want to to begin at is Uh we want to be able to apply multiple child conditions. So in some of those examples again there were multiple things we wanted to know, so that it was a certain date and it had a certain name or a certain style

Speaker 1: or something. Um need to define how we then nest into related models. So how do we get from brewery to beer? Like what is what is that connection? Uh and we want to be able to do this to some arbitrary level of depth. So um we want potentially for our users to be able to go five layers deep, six layers deep. Um and then for each field that we're applying condition to, we need to kind of define exactly what that condition is. So uh that the style is this particular kind or that the year was this particular date, that sort of thing. So we need a representation for that bit of it. As an interface, maybe this is what this would actually uh look like. Uh again, in fact, this is just stolen from the Bohas

Speaker 1: platform, but I've uh tweaked some words a little bit so it uh fits our uh fits our model. And so this represents I think the second query on the previous slide. So this is the the user interface that we might build for this tool. Um this is just about the last time I'm actually going to talk about the front end side of things, because that is a whole other talk uh that maybe I should give it uh ViewCon or React or something like that. Uh we'll skip past this little bit, uh leave that as a very large exercise for the reader Um this is the bit in the pictures where I got a bit distracted by dogs. So dogs are now gonna continue to feature a little bit as well. Maybe that's slightly more inspiring this morning. Um so yes, we start off with a representation of the base model, uh which is what that base parameter at the top is

Speaker 1: there. Uh we're going to find a way to combine the conditions, because maybe we want our conditions all to be true, or maybe we only want any one of them to be true. So we uh define this kind of combined thing, which allows us to decide which way they're combined. There's a few things in here which I'm going to skip over a little bit because they're um again probably a bit too much for me to get through in half an hour. Uh and then we define some children on that, um, which can be either of uh one of two types. They can either be a branch or a leaf, uh, which is kind of the language we use internally to talk about this. So a branch is where we go from one model to another one, and a leaf is where it's just a condition on the current model essentially. Um so here we've got a branch to the brewery um brewery model.

Speaker 1: And again we define how we would then combine the children on that one. So here you can see this kind of like nesting effect that I was talking about. Within those children there, you can then have more children, which could be branches, and those could have children. And so that's how we can kind of go as deep as we might want to go. I think this is either the cutest or the second cutest picture of a dog in this uh uh in this presentation. Um here's a representation of what one of those leaves which sits inside those child conditions might might look like So again we say that the type here is leaf rather than branch. We have the field um which in this case might be country and then we have uh the R value there which is kind of representing what is the actual way that we are filtering on that field.

Speaker 1: Uh and I'm putting it all together. I think maybe this is the best one. I I don't know what's going on here. I think this was a uh lockdown thing where there was Uh they were getting dogs to deliver beers to people, uh which I didn't hear about. I wish that was happening near me. Um and so this is what all of it looks like when you bring it together. This is actually quite a simple representation of a query. Uh often they'll be much bigger than this. Um but yeah, this is kind of how we're gonna represent the queries. Uh and somehow in the front end we're gonna generate one of these JSON representations of uh the thing that we want to know. Um the next thing we want to do is um we're gonna have this mix-in that we're gonna add to all our models. Uh and it's gonna do three different things for us. Um the first thing is that um

Speaker 1: we might have a load of models in the database that we don't want to be searchable at all. So say for example the user model. We don't really necessarily want all users of the platform to be able to search over users' passwords and that kind of thing. So we maybe want to explicitly say these are the models we want to make available to be searched. So any model which has this mix in uh you'll be able to to search over. And so yeah, just having whether it's got that class or not, we'll write some logic later on to check whether it should be searchable. And then we define one of the branches that we can search over and the leaves that we can search over. And I'll go through those in a little bit more detail now. Oh yeah, so that's this is then how we use that in one of those models. So here it is in the brewery model.

Speaker 1: Again, there's some ellipses here for the logic that we haven't really defined yet. We're going to have a kind of dictionary representation for the branches and the leaves saying these are the branch the ways that we want to branch and these are the uh fields that we want to be able to search over. Um note as well with the branches that um here we're kind of going through reverse relations as well. So uh beer and merchandise aren't defined as like foreign keys on the brewery model, but they have uh foreign keys pointing to brewery. Uh and so the name that we use on the left hand side is kind of how we will chain in our kind of ORM queries that we're gonna uh generate a little bit later on. Um I think that's all I've got to say on this one. Um what does our branch logic look like?

Speaker 1: Um so Here's where we start making some massive simplifications. We don't make all these simplifications in product in production at Bohurst. But um here one of the big simplifications we're making, which you can kind of see in the actual like return logic, is that we're basically just gonna do a subquery every time we branch. So that's almost certainly not optimal. As I say, it's not really what we do. We have a a system in order to kind of like check whether it's possible just to do a join rather than doing a subquery. Because often the join is going to be more performant than a subquery is. But for the purposes of this, where we've got this quite small data set, maybe just doing a subquery is enough. Um couple more uh simplifications. Um

Speaker 1: for now as well, we're gonna assume that any of these like child conditions that we have on a particular model, we just want to combine all of them together. So we're just interested in all of them being true. Um so if you have a look in the uh kind of model. objects. filter bit, um there we're just passing those multiple arguments, uh multiple positional arguments to the filter, uh and so that means that they'll all just be combined together. It would be a fairly simple extension, I think, to think about how you might turn that into uh uh any query. You then just want to take those child conditions and all them together rather than Adding them together and that's relatively simple with with Q objects. We're gonna be passing a lot of Q objects around here Uh and then actually

Speaker 1: kind of within those filter we have this like Q from search. json function, which I'm going to show you a little bit later on. And the main thing that's going to do is it's going to uh provide this switch between whether a child condition is a branch or a leaf and it's gonna handle pass the handling of the logic over to the the branch or the leaf in order to to handle that stuff. And that's enough to represent the logic for a branch. Uh it might seem over complicated to have a class representation of this thing. You could just write this as a simple function. Um again as you build the complexity of the tool that you're building, there's often some extended logic that you might want to apply. You might want to kind of clean up some of the input values, that kind of thing. So we found that generally uh it's helpful to have kind of extra methods within this class in order to kind of uh to break the logic up a little bit

Speaker 1: And then let's uh imagine an example of a simple uh leaf as well. So here we're searching over the uh country field that we had. Uh and so we just define this kind of Q from search JSON logic, which is gonna um generate a Q object representation of a particular leaf. We have this field name and we don't need to worry about whether it should be, for example, just country or brewery underscore underscore country. Because we're doing that subquery thing in order to kind of simplify some of that away for us. So we can always just refer to it within its um kind of current model essentially. In this case, we're actually just doing another subquery here because country is actually uh yet another model. But um

Speaker 1: can have a look at a more generalized example as well. Um so this might be a leaf which you so country you'd only use that for any country fields that you happen to have uh in your model, whereas this you could use for any Um any field which is kind of some kind of number representation, could be an integer, could be a float, that sort of thing. And so here we can have some more generalized logic for um here we have a uh the R-value representation is gonna be an uh uh array of two items um a lower bound and an upper bound, and then we'll say if one of those is uh null, then uh that's a less than or equal to or greater than or equal to. representation and so here we're just doing uh matching on all of those different uh possibilities and then generating a slightly different Q object for each. And so the logic that you can kind of put in one of these leave leaves could be anything that you want.

Speaker 1: And so you can do some really custom stuff You might be able to like annotate another model and do some other stuff. You can do a load of logic in Python as well if you want to. Sort of no limit to the stuff that you can kind of put in these leaves, so long as what you get out of the end of it is a Q object. Uh and then what those models actually look like now, if we fill in those gaps from earlier. Um for the branches, um Again, as a simplification, we just said all branches should be kind of um handled in the same way. Um again in production you might have like multiple different types of branch because maybe you want to have you want to branch just to like the latest of a the most recent of a particular model or something like that. So you might have a different type of branch kind of representing that kind of thing.

Speaker 1: Uh and then you define all the leaves as well. There's a few leaves which I haven't talked about here, like the text leaves. Um again you might want to handle handle different types of text differently. So a name, for example, you maybe just want to do like a simple substring matching A description, maybe you want to do some like more complicated full text search stuff. Um so again, by allowing ourselves to kind of like define each of these as custom, we can uh pick exactly how we want each field to be handled. This is how we kind of get the starting model from uh that uh base in the uh if you remember the JSON from earlier, we have that base model starting thing. And so base there is just going to be a text string representation of that model.

Speaker 1: And here we're doing that check. Check that it's got that mix in, check that it has the same name. If it does, then we can return that model. This is the way that we make sure that users can't have search over data that we don't want them to have access to. And then this is ultimately how we actually kind of generate a query set. So is this Q from search JSON thing that I talked about earlier? It's checking whether it's a leaf or a branch. And then offloading the logic to the branches that you've defined on your models or the leaves that you've defined on your models. Uh and then calling that queue from search. json method on those particular branches or leaves in order to generate those queue objects. Um and kind of if you think about the branch case, um

Speaker 1: Here, this QFromSearch. json object is then calling this Q from search. json method on the branch. I probably should have named these things differently to make it a bit clearer. Within that method, we're then calling on a branch we then end up calling this Q from search JSON uh function again. So that's kind of how the like nesting stuff is happening. That's the kind of iterative process for building this kind of nested um set of Q objects which have a load of subqueries within them in order to kind of go through your various models. Um then I do a very dirty hack in query set from search JSON, so we won't talk about that bit too much. Um Because we kind of throw yet another extra subquery in there just for a bit of fun. Um we're kind of offloading this initial

Speaker 1: um um the initial generating of a query set to the branch logic. So here we kind of um create this kind of fake branch to using PK and that allows us to just do a subquery on the same model rather than a new model. Uh and so we just kind of get this extra subquery which wraps everything but allows us not to kind of duplicate the logic um for how we kind of traverse a branch. Um And that at the end of it generates a query set which will have a load of nested Q objects within it essentially. Uh there's no brand reveal of me pressing a button and showing this working, but so you'll all just have to have faith in me that if you put all these pieces together, um at the end of this you've generated a a query set um

Speaker 1: of objects um connecting through various relations on those different models and using Q objects within it in order to kind of combine those into just about anything that you might imagine in the front end. Um various thing as exercises for the reader. I forgot about the beer on this one. This is just a picture of a cute dog. We as I sort of talked about earlier, we haven't handled any other types of combining children other than all of these apply. Um so you'll want to build the logic in order to see any or uh any other cases that you might be able to think of Um I scanned a similar thing as well, uh again, which I sort of addressed briefly earlier, is um We haven't handled other types of branching, so uh rather than any of the beers from this brewery, you might be interested in all of the beers need to have this property, or it might be the most recent uh beer released has this property, so you might want to handle different types of branching like that as well.

Speaker 1: And actually, maybe for a lot of cases, I defined that number leaf earlier, maybe you want to just assume that any number field uh which you want to make available to search should be handled by that that leaf. So maybe you want to create some logic which will, by default, handle things a certain way and then you can override it for particular fields that you want to be handled differently. And there's probably some stuff about data validation and data cleaning and all that kind of stuff, but we don't want to spend too much time on that. Um what are the limitations of this approach? Um don't do the subqueries everywhere thing, I suspect. Um even at medium-sized data, this probably isn't gonna scale. Um the JSON representation we had at the start is pretty comprehensive, but it's still not everything.

Speaker 1: Um so one thing that uh Users at FOH are often asking for is they want to have this kind of full Boolean logic where they want to be able to say, uh, I want the year this uh beer was released to be this and the name of the beer was this or I want some other condition to be true and there's not really currently a way that we can combine those things because we just had a list of children We can't say we want two of those children to be both true and then one to be true alternatively. It's like too complex for the representation that we've defined. So maybe you can think of a different structure which is actually kind of better for handling those particular cases. Um and we have to be quite careful about exactly what we're giving our users access to because um

Speaker 1: It's kind of wild how complex a query that you might be able to build up with this. Uh we want to make sure we put some limits in place so that uh you know our users can't just like take our database down by building a really big complicated query Um some kind of answers to some of those things. Don't really need the subquery stuff everywhere. And that might be quite a slow, slow running query. Let's cache those results when they run it, just in case they kind of come back to that search later. Again, because of the flexibility of this, the caching isn't really for other users of the platform, because it's really quite hard for one user to generate the same thing that another one's generated.

Speaker 1: We have very different kind of as I say use cases for our platform. As I said, make sure that like slow performance of our search feature doesn't impact the rest of the platform. So there's some stuff that we put a lot of effort into uh ensuring this doesn't affect other things. So We run a read replica of the database which all these queries are run on, and only these types of queries are run on so that uh if this thing starts getting slow, then it doesn't affect everyone else. Uh we also use PG Bouncer to like queue up those queries as well. Um again so that uh the database isn't coming under too much load. Um we set a statement timeout on every query, which is a very long statement timeout. It's about 90 seconds.

Speaker 1: uh let users sit there and wait but any more than about 90 seconds and I think they're gonna get bored and close the tab. But again, because of the complexity and medium-sized data often it can be can be going for a very long time. There's lots of things that we'd like to make better here. If solving these kinds of interesting problems are interesting to you, then do come and have a chat with me around the conference. Um I think that's all I've got to say. Thank you very much. Any questions, please come and find me.

Speaker 2: Thank you so much, Alex. Alex, could we take one or two questions maybe? Uh which we're still good, I think. So um if you'd like to use the opportunity to ask a question, please. There's mics on the sides and as well as in the auditorium.

Speaker 3: Hello, Jacob. Hello, thanks. I'm not doing a code review. I'm just wondering why you went for all caps with your method. S signature. I've never seen a effort.

Speaker 1: That's a fantastic question. That decision was made before me.

Speaker 2: Jacob?

Speaker 4: Yes, I I just wanted uh if you have the query queries already in JSON's Um so the logical um uh database to do stuff like that would be Elasticsearch. So why did you W what was the um reason you didn't uh consider Elasticsearch for it?

Speaker 1: Yeah, that's a good question actually. Um yes, haven't looked into um exactly how much of what we make available would be possible with Elasticsearch, it's very possible that yeah, you could build a very similar kind of tool based on Elasticsearch, but we've been kind of using using Postgres for for years and that's um kind of a fairly core architectural decision for us, so kind of making that transition so that this feature can kind of interface with all the other stuff we have on the platform would probably be hard. Yeah, it would be interesting to see how you might build the same kind of thing on top of Elasticsearch instead. And actually, yeah, as you say, maybe you can kind of build a custom interface for any different type of database that you might want, because this kind of JSON representation is a bit more generalized than uh sort of specific to SQL necessarily. Yeah , interesting.

Speaker 2: And that's all the time we have for questions, so let's give it up for Alex.