Building a XenForo 2 Add-on - Part 4: Working with the database - entities and finders
now that we've seen how to insert php variables into our templates it's time to work with some more useful data so let's take a trip to the database while php and XenForo itself for that matter allow you to run raw sql queries to interface with the database there is a more robust way to deal with the database in an application like XenForo our system is called entities and an entity represents the data for one particular item so maybe a single user together with all of the relations for that particular data so maybe all the posts that a particular user has made and any properties and useful or helper methods that go along with the data so for example the user entity has the ability to authenticate itself without going off and talking to some other system elsewhere additionally entities are self-aware in that they can describe themselves they have a record of how they're built up so you could take something like the email field here and pass it to the user entity as either the value for a new entity or an updated value of an existing entity and it will look at its structure here and say that okay so email is a string and it has a max length of 120 and it will automatically make sure that the data is valid in that regard but it also knows that it has to be a valid email address and so we'll look at a function like this verify email method here and go through and make sure that it is in fact a valid email not a banned email etc etc and if it fails the validation checks for whatever reason it will give you an intelligent and intelligible error message rather than an abstract database failure together with the entity system is the finder system which is essentially a query builder to build up a select query together with all of the conditions and limits and orders and other things that you'd expect when you're trying to fetch data from the database once you've built your query which is done entirely programmatically rather than resorting to actually writing sql as far as the engineer is concerned you then have the ability to fetch the data as a collection of entities rather than flat arrays as you would find with the regular mysql functions this programmatic approach means that it's much easier for add-ons and other code to integrate with the finder system because you can override functions and methods and generally it's much easier to work with that than trying to do some sort of bizarre text manipulation on the raw sql so let's take a look at entities and finders in a real world context so here we are back in my test action for the note controller and i've removed nearly all code from the previous step because it's superfluous to our needs at the moment what we want to do here is we're going to create a user finder so let's just get started and do that user finder equals this finder which is going to call a factory that will start up finder and then the argument that we need for the finder is the short name of the entity that we're trying to find and in this particular case it's XF:User we can actually see that in the user entity just here structure short name so back to the note controller here now if i were to just let that run and let's actually give it an output so users equals user finder fetch you probably saw all sorts of other stuff being suggested then we'll go back and take a look at that in a moment but what we're going to see here is because we've attached no conditions or limits to this finder whatsoever the result of user finder fetch will in fact be the entirety of the user table returned as a collection of entities so let's let's just stick a break point in there and turn on the debugger and actually see what that looks like so here we are we've skipped to here the user's variable has been initialized as the user finder fetch so let's take a look at it you can see it's an instance of array collection and there are four entities and there they are you can see they're keyed with the primary key i'm not going to dig into what the actual entity content looks like at the moment because that'll come shortly but we can see that we do have four user entities as a result of this user finder so now let's let's stop that and add some additional stuff so let's just move this over here so that we can see what sort of stuff we would want let's add a condition here so sure enough just as the sql syntax would require a wear we're going to use wear here too so let's say where and then we need to say where which field let's say where user id and then we need an operator unless it's equals in which case you can just omit it where user id let's say where user id is less than four and we'll leave it like that notice that i'm just chaining onto the end of this user finder here and we'll carry on chaining more stuff so the output of where we'll go into the next condition etc you'll see that in a moment let's run that again and see what we get so we'll hit that and we're back in the debugger we've stopped here again on line 21 and in the users we've got three entities and sure enough we've got entities with primary primary keys one two and three because we didn't want anything that was four or above so let's add another condition let's chain another one on the end there i'm just adding them on to new lines there but if you really wanted to do them all on one line then you might you could now what should we do where what other stuff can we pick up let's say where the username contains an m so username is like and what are we gonna use percent m percent should get us what we want so i'm expecting that that will oh that's there's an m in admin isn't that what should we let's say where it begins with m then so that should bring us down to two results if this worked properly so run again and the users there are now two entities so that worked as well now how about if we wanted to change the order they're returned in you can see that the order is 2 3 so that's in user id ascending order let's change that round so let's go to to the next line here and say order which field should we order by let's order by well let's just do something obvious and reorder them in the opposite direction so order by user id in descending order refresh and now we should see three two so you can see that the ordering worked now having said that you can chain these where commands that's absolutely true and what we're doing here is we're saying where this and where that but what about if you want to say where this or that well as it happens that's actually quite straightforward we're going to use a method called where or and it looks like this where or and now into the conditions here we're going to take the arguments that would normally be put into a where clause and instead wrap them up in an array like that and then that's the first condition now if you want another condition we'll just add another array and put the conditions from this one into there now you can see that i'm passing each one of these arrays as a parameter and you can carry on forever if you were trying to call this as part of a callback and you didn't know how many parameters you were actually going to be passing the where all function can also wrap the whole lot up in one array like that so that now it's just one parameter at each parameter and each element of that first parameter is an array of conditions that works in exactly the same way so let's go back to the initial way of doing it so what we're looking at here is a query that says find me all the users where the user id is less than four or the username starts with m so i'm expecting that that will actually give me three results this time because the every user whose name contains an m has the id of less than four there we go so that's that's a quick intro to finders let's start to get a little bit more advanced with them now right now we are just fetching the user data and we're passing it into this fetch thing here but sometimes you need to generate to find out how many items are going to be returned before you actually fetch them and in normal code that would mean running some sort of count query and again usually this would mean writing another query that has all of the same same conditions as your main fetching query and but instead of fetching the various fields that you want to collect the actual data from you'd run a count function the finder system makes this really easy because it can take all of the information you've passed to it here in your query builder and it can formulate a count query rather than a select data query just with a simple method so we can do that by just saying total equals user finder total and now that will create for me let's run this stop and run so we've created our finder and now we're going to get the total and it says that the total is three and and based on that result you could then do pagination or you could detect a result that's going to have no items returned at all and then just not bother to run the fetch query and you can make all sorts of decisions based on the number of results that you know you're going to get before you actually go to fetch them having seen how to fetch a collection of entities from the database there are a few special cases where we want to fetch just one so we could change this back into a where query and let's get something that's going to fetch just a single record let's let's use that username where username equals now remember i said just know that that parameter is optional i'll show you that in a second let's say where username equals admin at this point the order is irrelevant because there's only going to be one item but what this is going to fetch is a collection of one entity and that's not specific not particularly useful because that would when to use it would have to loop through it so we can change that to a different fetching method called fetch1 and what fetch1 will do is essentially apply a limit to the query so that it knows that it's only ever going to fetch the first result returned and hopefully that would be the only one that's of any importance and it will return it as an entity not a collection of entities so let's just change that to user and go and see that in use so debuggers on and refresh we'll step over that and now the user you can see is an entity it's not an array collection and we can use the we can use that user directly having said all that this is a slightly long-winded way of doing that there are various shortcuts for doing things like fetching an item with its primary id like for example if i want to fetch user id for then i can do that very straightforwardly by just saying where id four the entity system is clever enough to know what the primary key is so this is all you need to fetch the user with the primary key of four and again this would return that user without any additional fuss and of course at this point the total is irrelevant because we know that we're only going to be fetching one so i've taken my code back to the point where it's fetching a single entity in this case it's user id with id 2 mr user and then we're going to fetch it into a single user entity and i've done that so that we can have a look at different ways that we can actually work with the entity itself seems to me that a good demo to start with would be changing the email field like i mentioned earlier on in the video so let's do that while you're working with entities if you're using something like PhpStorm or visual studio code that understands php doc php dot can be a really useful thing to employ to help your code so i'm actually going to annotate the code at this point to tell my IDE that this user which has come out of this XF:User finder is in fact a user entity and i'm just going to do it like this it's an \XF\Entity\User and as soon as i do that i get additional help while i'm typing code and you'll see that when i start doing this let's set the email user e m hey there we are it knows that we have an email field so we'll put that in and use email equals i'm going to make it through an error to begin with so i'm trying to set the value of user email to invalid stuff and then i'm going to save that and at this point i'm expecting that to fall over so let's just turn off the debugger and see what actually happens with this i'm expecting to get a relatively straightforward error message that tells me that the email is not valid please enter a valid email seems pretty reasonable so let's change that to a valid email let's save that out and so we're starting off with mr user example.com run that so no errors and if i refresh the data in the database a valid email.example.com it is that straightforward to work with the entity system how about if i wanted to update multiple fields let's let's change i'm not going to recommend that you actually do this but just for the point of the example let's say user username equals mrs user and we'll change the email as well that'll do so we we're now setting the email and the username and then we're gonna hit save and what we should find is that sure enough that's all updated as we'd expect it to so now let's change this back so that it doesn't change all my denormalized data in the database mister user and mr user i think it was mis wasn't it mr user at example.com let's save that out so doing
updates to users is that straightforward you could also use a thing called bulk set which essentially allows you to pass an array of name value pairs into a function to have them all set on the entity and it looks something like this you just say user bulk set and then you'd have your array of email we'll go to whatever that is and username would be that and essentially you just set up your name value pairs like that in order to have them all go with a single command which is great if you've had them all come through an input filter or something like that and you can have any number of those in that call to make that work in exactly the same way you probably also saw that there was a bulk set ignore option there which will bypass some of the validation checks so only use that if you're absolutely certain that the input that you're providing is valid so that's updating existing entities in a nutshell but what about creating new ones well as it happens it's really quite similar so let's just change this code around so that we're not working with an existing one but we're creating a new one i'll get rid of that line about the user finder and then this part where i fetch the results of the finder i also want to get rid of and instead i'm going to leave the php doc in place because we are still going to be working with a user entity but this time i'm going to say $user = $this->em() which is Entity Manager ->create() and then i need to pass it the short name of the entity type that i'm trying to create so again it's a XF:User and the item that that returns will be essentially an empty entity which contains all of the default information that is needed but doesn't have the stuff that it can't work out by default so things like the email and username so having created that we then move into our bulk set or a number of individual set operations to pass the data that is necessary and then we hit save and that's that's all there is to it the difference here being that the entity manager knows enough about the entity to know whether or not it's already represented in the database and if it is it'll run an update and if not it'll run an insert with exactly the same validation and error checking that you would expect from the update facility so i won't actually run this now because that's not kind of the official way to create a user and there's a user creation and registration service that actually does that job much better but just as an example of how to create a new entity this would essentially be it so i'm back in the database now and i'm looking at the post table because i want to show you a little bit about how to work with relations in entities you'll remember that i said that entities define their own relations and so the post entity for example has a relation to the user table and it knows that the user id in the post table relates to the user id on the user table and it's very easy to pick out those relations and work with them so let's go and take a look at that in action back in the php here i've got rid of all of that editing code and we're going to get some fetch code again so we'll start off with a post finder and then we'll get the finder for XF:Post is the entity short name this time around and then i'm going to only fetch those where the user id is not zero so not guest posts and then i'm going to pass the posts the post finder even as posts to the view parameters i've got nothing else to do with the the posts in the php so i'm going to be a bit lazy here and actually just pass the post finder there's a little bit of magic that goes on here at the point that you try to iterate the post finder in the template it will know that in order to do that it has to go and fetch the data first so rather than explicitly fetching the data which is what i do most of the time here i'm just passing the post finder directly into the template so let's head over to the template and do something with it so here we are in the test page template and let's open up that piece there and do an xf:foreach the loop is on the parameter name which is posts and each item is going to be called post let's close that off and then for each of those posts i'm just going to account a little bit of information so the let's say the post id equals dollar post dot post id and the oh i know let's let's be a little bit more stylish about this let's have a ul class equals list in line list inline dash dash bullet i think we'll do something that looks a bit nicer and then we'll have individual list items inside that and then we can take this and this will probably work very very badly indeed because i've probably forgotten the syntax but hopefully it will at least be recognizable and then we've got a user id now at the moment we're only using the data that is in the actual post table it's the the basic stuff from the user table and therefore sorry from the post table and therefore in the post entity and i think i was gonna username wasn't it username equals post.username now that's all great let's save that out and just check that it's working and now i should expect to get a bunch of there we go poster d1 was user id1 and admin going right through to post id 86 user id 3 and mr spammer and we expended 5 queries to do that because essentially it's the the page setup plus that one query to actually fetch the posts what about if i want to get the email address for those users well this is the kind of lazy way to do it but it's a good illustration to get a relation all i've got to do is refer to it post dot user dot email so this tells it to use the post entity and then the user relation and then the email property of that user relation now this absolutely will work but there is a caveat and the caveat is the number of queries has gone up to seven because we've now had to identify various different things now there is a little bit of magic going on it's only seven instead of an additional four queries because it already knows about the entity for admin because i'm logged in as that and it's clever enough to know that it doesn't have to go and fetch that again it can use the entity that is essentially my logged in user account and say well that's the same thing so there we are we've got the email addresses but it has cost us additional queries so how can we get around that well that's easy all we have to do is tell the post finder to bring stuff with it so we'll say with which relation do we want the user relation and now when i refresh this we should end up with back to five queries again because essentially what we've done is we've built the join and told it that we are going to be working with the user entity and so it's fetched in advance with that an inner or a left join depending on how we defined the relation now we're not limited to working with a single relation either we can work with all the relations that an entity has defined and we can chain our relations so let's let's say that we want to add the thread so let's say also with thread because obviously a post is going to have a thread relation and that's going to be the thread that contains it and maybe we want to get something from another part of the user entity though i can't think of one at the moment so let's come back to that in a moment but if i wanted to to work with thread then i'll just save that out come into the template and let's add in something to make this stand out a little bit more we'll just have an hr and then let's say from here the thread can't actually type is done post dot thread the relation dot title now this is a useful thing to note the entity system allows you to get around that hideous problem that you often find when you're working with data that's been returned from a from a database in a regular system where you don't know whether the title has come from one table or another unless you've specifically aliased it in your query with entities you don't have to it automatically creates these objects and assigns the correct title to the correct thing so for example the post.username here could be different from the post.user.username in some cases but this allows you to refer to both without having any sort of name collisions anyway let's come back to our thread title and just check that that does actually work and we can see that we're now working with two complete relations and so there we are the thread is my first thread and that's my first thread and all the time we are still only working with five queries so having said that i'd show you a chain relation i found that in my xf_user_profile table i've got location data for my users so let's go ahead and use that so back in my finder here we've query to get posts and we've said that we want to fetch users as well so the user relation for the posts but now i want something that is a relation not of the post but of the user how do we do that well actually it's really straightforward we just say with user dot profile and now we have access to the user profile again fetched with the appropriate joins that are automatically generated so let's save that out and refresh here just to make sure that it didn't blow up it didn't and then we can come in here and say let's get rid of the user id there we don't need that and we'll say that we want the location and the location is post user profile i think it was called location wasn't it location let's save that bring it back and here we go there's the location so now we're using two direct relations on the post table namely user and thread and also a chained relation where we've looked for a relation from one of the relations that belongs to the post itself so that's a quick roundup of some of the major functionality of entities and finders when dealing with data in the database we've seen how to fetch data including data that is related such as users related to posts and user profiles related to users which are related to post etc and we've seen how to update existing data and insert new data but what we need to do next is to work with a custom entity that will be tailored to our new custom data for our notepad scratchpad demo thingy
2021-05-10 14:34
