Nestjs TypeORM Search & Filter, Join Relationships, Select Specifics - Query Builder & Repository

hello guys in this next J's tutorial we are going to perform different kinds of queries to fetch data from the database using type orm first we are going to use the repository API to perform the queries and then we also see how to perform queries using query Builder on the screen is the list of things we are going to cover we are going to see how to filter result instead of just fishing any result from the database just imagine where the user can filter results in this example cities let's say you want to filter the cities by the time zone or by the country or continent that kind of thing also we are going to see how to use search input for example the user has a sarch bar where they can input something we perform the SAR in the database and return machine records we are going to also take a look at how to select relationships in this example City belongs to Country and to continent we are going to see how to select city as well as the continent that belongs to or vice versa that means we can pick a continent and also get the cities that belong to that continent we're also going to see how to select specific columns instead of returning all the columns in the tables whether it is the main table we are cing the the primary focus or the related tables we can see how to select specific columns also we are going to look at how to search and filter the same sarch and filter but this time we are going to use conditions that are based on the related columns for example we can filter cities the list of cities returned by the continent name even though continent name is not in the city's table we can can do something similar for sarch by searching the related tables that is what the this last two items are for all right before we get started I want to quickly explain my setup now you don't need to worry about this simple NJ application that I have created for this demonstration but I'm going to explain a few things what you just need to know is that in this setup I have three entities that is one for continents another one for countries and another one for cities and then for each of those entities I have a corresponding table so I have these three tables countries continents and cities our focus is infection data from the database using type orm so you can just ignore all the other details about setting up this project and all that for cities I have this city service right now it has only one method find or it just returns any city right there's no search of filter whatever happening here then I have the controller for cities it has one method F or as well which is for returning finding cities it basically gets the cities all of them then for city as well this is the entity class I have the table named cities this city entity has these fields ID name description active which is a Boolean time zone continent ID cont ID and these ones are relationship stuff so just to point out a few things here um I will skip ID name description those are just normal things then here here I have um many to one relationship uh vice versa that is one to many relationship between City and continent I have something similar for Country so a continent has can have zero or more cities that is multiple cities a country can also have zero or more that is multiple cities and this is how you map the relationship in njs with type omm all right with that simple explanation let's um call this end point in the controller that will help us better understand the problems we are trying to solve I will head over to postman now and call the endpoint to get cities I will send this request what we get is the list of all the cities but think about it city has the these fields what if we want to filter cities by time zone for example so I'm going to take this time zone gmt-5 and send the request it doesn't change anything because the implementation we have now just returns everything this is New York what if user searches for York or whatever in the search bar we're supposed to match cities that has that um text in it but that is not working because right now we just return everything that is in the city's table these are the problems we are trying to solve with these action items so let's just get started the first thing I want to do is to filter results based on user input so for that I'm going to let me just leave this method the way it is and create another one one I'll come down here and say find many of course you can name this whatever you want then I will go to the controller and replace this guy with that of course if I call the end point no difference right I think it is restarting all right so there's no difference but what we want to do first is to filter filter is different from the S maybe they mean the same but by F I mean anything aside the sarch so to match the exact input for example GMT plus 5 but this one is partial if you enter York instead of New York it is still going to find the records that is for S but for fter has to match the exact records so for fter I'm going to come down here let me have const con and D to be equal to empty object for now so instead of just find I want to find where those conditions are met these conditions here I'm going to populate the conditions in a moment but what are these conditions they're going to be based on the user input what we get from here so I'm not going to hard code the condition for this filter what I will do instead is to make use of the user input for that I have this dto class already created for find cities it is called find cities dto so we can filter by all these and we can search by the search field and then we can also see how to search by relationship that is the continent name even though we are trying to fetch cities so I'm going to say that this is going to have dto which is of type find Ci's dto let me go ahead and grab the items inside that dto so there should be name oh not that please should equal to D and inside here there will be name there will be time zone and uh the Boolean field active and Country ID I and then I think continent name as well as sash we not going to make use of continent name and sash yet we'll get to that in a moment so then I will have to I actually need this I don't know why I removed that so what can we do what I want to do here is simple if name is supplied something like this so I can say if name is supplied then conditions dot name to be equal to that name but of course this is typescript is saying that name does not exist on this type so what we can do is something of this nature I'm going to modify this in a moment um string any so that error goes away but this is going to mess things up if you're not careful because for example if I say condition do a column that doesn't even exist in the city's table it doesn't complain so you can easily make a mistake so instead of this um object type there what I want to do of this generic type what I want to do is I will just hover over this to figure out the type of data the data that is for this we Clause so I'm just going to copy from here to here just going to steal that information and then come over here and paste it and import that from type orm now you can see that it Cates the Mist take here even though it was intentional so I can say city name to be equal to that now I can repeat this for all these other fields but that will take me three lines per field what I can do instead is to have some kind of a short hand that will give me the same result so I will come in in here and use the spread syntax um not that I'm going to say if if name is supplied come on if name is supplied then I want to have name to be equal to name otherwise I'll just leave it empty this is just going to give me the same result like before of course I can remove this since that the same thing I will do the same thing for other fields so for time zone I'm going to say if time time zone is Supply then time zone is equal to time zone because it is the same I can remove it if active I can do the same for active no not really so because active is a Boolean if I say something like if active it mean that this can only apply if this is true but we want you to apply whether it is false or true the user can select to find cities that are not active or cities that are active so this simple if statement is not going to work for us what I can do instead is to check if the key active is in D that means if the key is there whether it is true or false we don't care right so if the key exist in the D we just find it and finally I can do the same thing for for Country ID the other two we get to them later so now I expect that our our filter is going to work for any of these fields all right let's go ahead and give this a try I will save that and head over to the browser here I have how many let me count how many I have I have 46 records right but let me remove this s it's not doing anything right now if if I go ahead and send this request again we are going to get only cities in the Time Zone GMT minus 5 because of that filter so I'm going to send a request uh what happened oh my let me see what happened so I think I know what happened in the in the controller we did not supply the D which we need to grab from the um query string here so let me go ahead and do that over here I'm going to tell NJ to give me the query string from the request I say query to be of type find C's D and then in the method call I can just Supply that query I will save and try that again see if it works now send the request you can see that we get only three results and each one of them has gmt-5 of course we can combine this for example let's say I want to find a city named Toronto among all the cities that have gmt-5 so I'm going to come to the name field and paste Toronto if I send the request this time I get only one record because there's only one record that has the name Toronto and um time zone City by the way I don't mean as in fact factually as in Real World what I have here is just some dummy data I'm using for this demonstration there could be there's probably more than one I don't know so but our future works you can go ahead and apply for um other things as well like country ID Etc but this is proof of concept enough so far our Filter Works but let me go back here I'm going to remove this oh I will just leave that if you look at the cities the fields in cities it doesn't have continent name of course it should not have continent name that should be in the continent table but what if we want to search by this continents table you know that cities and continents are related through the one to manyu relationship so for example um say I just type a share here and I will remove the other ones just type A here remove we going to get back the 46 records there's no fut here so we're going to get the all the cities in whatever continent that is the next problem we are going to solve so that user can search by continent or by country the same logic applies so I'll go over to the code and this is going to be really straightforward what I want to do is let me copy this guy and come down here so I'm going to say if the continent name is applied if continent name is applied you come here and say continent type orm allows that so for continent I'll put another column then I can search for fields that are in continent in this case I can say name to be equal to continent name note that this is not just a few this is an object the relationship in this entity right this is for continent all right so I can go back here and let's give it a try by supplying the continent name over to postman I'm going to to send the request again with Asia as a continent this time we get 13 records but of course from the city names you can already guess this is Baghdad Dubai Mumbai beiji Tokyo Etc but this brings us to the next problem we need to solve we can see that we have least 13 items in this list but the continent name is not showing it is not showing because it is found in a different table the next thing we are going to do is to see how to select related columns let me move this up so we're going to select related columns before we do this for cities let me quickly show you using continent so this is continent service right now it will just return all the continents I we go go ahead and call the continent Endo so this gets all the continent but let's get continents along with the cities that belongs to that continent for that I'll come over here and say select relations so using this repository API select relations here we can list out the relations we want to select are different syntaxes for this first I'm just going to use this syntax so I will save that only C is for now and let me try it again you can see we get the cities here is another syntax to do that so we got the continent and the cities another thing we can do is instead of this array of strings I can come here and pass um that object they call it Braes I'm going to say cities through that is going to select cities we can select multiples for example countries because countries has similar relationship with continents just like cities and say true I will save that and go again so this time we get cities and we also get countries but this is returning all the fields in both the cities and the countries let's go back and select specific columns instead so for that you can pass this next option this is going to be select here you can once again you can use the array to specify the things to select but this is going to return all the fields in whatever you select instead of that what I want to do is to use the object again again then for at the top level that applies to the primary uh entity in this case continent so I'm going to say to select ID you can just say true and not that true and then name True by the way if you if you say false that field will be omitted to not be selected but let me just leave this as true then for the relationships you can come here and say cities right and you put the colum here you can specify the fields from City's table that you want to select just going to select ID and the city name going to say name true we can do something similar for other relationships like countries for countries let me just select only the ID we're going to see how how to do something similar when using query Builder instead of this uh the repository API that we currently using so I'll go back now and try to call the end point again this time you can see that for cities we have the specific fields we selected and for countries we have only the ID because that is the only thing we selected to test the force um I can say name if I say true that will be selected but false it will not be selected by default it wasn't selected because we did not list it here but what if there are certain conditions maybe a brilliant condition that tells whether or not this should be selected so that's where this becomes helpful I will go back now and try to let me call the end point again what just happened it seems I left the thing at false so so through and send it again you can see we get the the name and ID for countries all right so I'll go back to cities service now what we have seen that we just did for continent we can do something similar for cities unlike continent that is one to many that is one continent can have many cities the reverse is the case for cities a city can only belong to one continent so we are not expecting to see the nested array of data for each of the fields for cities unlike in the case for continents so I'll go here and say [Music] relations um continent I'll select the ID for the continent and I'll select the name oh come on that should be relations not continent what am I doing true so that is relation not select by the way if I don't specify any any select here he's Assuming he's going to assume that I want to return all the fields in that relations that I want to return everything so I'll go ahead and call the city's end point again this time we are going to get the continent populated as well continent has only two Fields the ID and the name but if you want to select specific field you can do something similar to what we've seen before you can say to select um ID through for City and for continent and just say continent let me also select the city name through and maybe let me select time zone in addition to that true for continent ID true and name so I'm going to save that and this I can just remove that relations send the request the server was restarting uh what just happened oh I need the relation sorry so I need the relations but I specify the fields I want to select so I have the the field from City and the field from continent let's just be sure that this is working as expected so I'm just going to select the name of the city because city of continent continent has only two Fields I will send the request again you can see that that actually works okay so we have covered a few a couple of things from our objectives filter and selecting the related uh Fields what I want to do next is to look into SAR how to search multiple columns based on the user input for this we are going to let me have another method called find many two find many number two already have one up there this time we are going to make use of query Builder instead of the repository API but I still need these items here so I'm going to copy this line and bring it down here the Syntax for query Builder is different first let me create a query Builder object const query build equals to this repository. create query Builder you can give it an alas like city so we can do something like city. name city. continent Etc let's look at how to do filter using query builder then finally we see how to perform search for um one or multiple columns so I'm going to come down here and by the way you can check typm documentation to learn about this cre and the different methods you can call on it for the first one name I'm going to say if name is supplied then I want to say qu Builder dot where where city. name equals name so we pass something for that placeholder name to be equal to name I'm going to copy this and paste a bunch of times for the other fields so copy and paste for time zone I'm going to say if time zone is supplied I want to find cities this is going to and query where city. name is this and time zone is that and active depending on which one is supplied right similar to what we have before just different syntax I we do the same thing we did above for active if active is in dto let me close this guy if active is found in dto so I'm going to say city. active equals that value of active and then country ID if it is found there going to say city. country ID and this one is going to be a little bit different so let me just make it clearer because continent name is not a column in City's table so what I want to do just to make it clear if continent name is supplied then I can say qu do um join join what join continent we give it a um Contin then I can say where come on where continent. name is equal to the continent name continent name so we are just doing a left join here we are joining the continent table and we are selecting cities where continent name matches the supplied continent Name by the way we can let me just have this joined whether or not the that field is supplied because we also want to select fields from the continence table whether or not user SES by continent name so I can come down here and just leave that guy here the join statement all right and I guess that is it here I can actually return I can return return pr. left join left join. get get many okay so this method is ready to be tested what I can do now let me go to City's controller and replace replace the find menu with the second find many that is find many number two I will head over to postman and to City's endpoint first and first no search of fter if I send this oh man what happened okay I think I know what happened so the issue here is that I need to use this aers says left join continent it doesn't know what I'm talking about so left join City do relationship called continent if you remember from the city's entity we have that relationship continent right here so let join city um do continent I'm going to save and send the request again and this time we get how many records all the records obviously 46 now let's go ahead and try um something we did before I'm going to filter cities by Time Zone GMT ne5 we get three records like before of course if you type a time zone that doesn't exist we not get anything so it means that the futter for time zone works and of course I can already tell that the one for name Works unless we have a typo in the code let me not bother about that for continent I want to search continent Africa we send the request we have three records for the continent Africa Nairobi Cape Town and K so the filter works now let's look at how to select relationships so we can see the continent name right and finally we look at the sarch to select relationship is really straightforward similar to what you can do with the previous example so here I'm going to use an array because you're not using this object syntax it is not available down here in the per Builder if I'm not mistaken so what I want to select is everything inside City and for now I'm going to select everything inside continent as well I will save that and go back here you can see that we get city and continent nested inside each of these cities but what if you want to select specific columns it is really simple you can say City do ID and select City do time zone and you can do something similar for continent like continent. name so I'm selecting only three columns two from City and one from continent I will send a request you can see that exactly that's exactly what we we have here all right so we have covered filter and select specific columns including selecting from relationship column now let's look at sarch if the search field is supplied so I'm going to say if search so the SQL is going to be a little bit different because it is going to have a bunch of or in the in the statement first let's identify the candidates that are good for search name is an example and then time zone we also want to search in the relationship table that is the continent table by continent name the SQ is going to look something like this select let me say all from cities and then we have where where um let's say name equals some value some value let's say time zone whatever that is supplied for the fter right this should be and and time zone equals some other value and then for the sash part we need to group them together using all for all the fields in the sash we're considering for the sash this is just SQ otherwise you get um unexpected result so here we can say for the sarch we name name equals whatever or time zone equals whatever or um continent name you get a point if you don't do it like this if you remove the grouping we are going to say where everything on the left is met or something on the right is met okay but that's not what you want you want all the conditions to be matched in this case it's going to be grouped together let's just write the code so I'm going to say query VI dot where for the group I'm going to say new bracket for the grouping this is going to this is imported from type by the way this is going to give us access to the query Builder but let's call it QB so just to distinguish then inside here we can say the QB query Builder dot where here then for the search we want the search to be the search is going to be case insensitive so I'm going to say lower case like converting it to lower case where C do name equals no it's not going to be equals because we are searching we not matching exact record exact value so for something like New York if someone type just new you're going to match all the cities that has new in the name or in the time zone or the continent name so I'm going to say like like what and then we put the placeholder here for sash why is it D okay I did not close the first one so where city. name like once again we say lower case here and then the substitute for the placeholder which is going to be the sarch but you we are not going to leave the S like this otherwise the value must match exactly the value for the S instead we do this is just SQL stuff so I'm going to say percentage that is to match from the beginning whatever before the text and then the imputed text which is sarch and then the end as well so this is going to match partially whatever that The Columns we specify here like before if you say new it's going to match New York New Jersey you get a point that is for the city name column we can do something similar for other columns so I'm going to say all remember this is going to be all conditions right the next one is going to be time zone so I'm going to say where city. time zone equals that or for the continent so we can say where continent do name equals the sash as well so this is going to sash in the three columns all right so let's go ahead and give this s a try I will head over to postman first let me remove let me count how many stuff we have here let me remove Africa and send the request so we have 46 all the records if I go ahead and search for let me search for Asia we don't have any records okay I spelled that wrong so it's supposed to be Asia so you can see for Asia we have 13 records and these are the cities in the continent of Asia can also see the continent of course we can combine this with the other FS that we've seen before for example let's I don't think we have we have not tried country ID so let me just put country ID of 26 and see if we get Mumbai I say we get only one record which is the city with country ID 26 and in the continent Asia so Asia sashes this is finding the record based on the continent right so this is based on the continent Let's test something that is not going to be matched in the continent that means something that will be found in the city's table for that I'm going to pick uh instead of picking let me just type partially New York but just new I expect this to match New York and that's what we got if you type something that doesn't exist of course you're not going to get any result all right so our search works and I believe that we have touched on all the items we have in this list of our action items okay guys this brings us to the end of this lesson thank you for watching until next time enjoy coding

Channel: ZestMade

Views: 3,504

Keywords:

Id: OeWqt7677Kw

Length: 40min 18sec (2418 seconds)

Published: Thu Nov 09 2023