Building a XenForo 2 Add-on - Part 4 Working with the database - entities and finders

Show video

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

Show video