Transcript for:
Lecture Notes: SOQL and SOSL in Salesforce

what's up everybody I'm Matt Gary and in this episode of coding with the floors we're gonna go over absolutely everything that has to do with Soquel the Salesforce object query language and social the Salesforce object search language so that you know how to build efficiently and effectively query for data in your Apex code oh all right everybody so today we are going to go over both of salesforce's query languages the Salesforce object query language or Sokol and the Salesforce object search language or social and uh you know we'll figure out the differences between the two and when to use one over the other and more importantly we will figure out absolutely everything about both of these languages uh you know from the basics of just setting up a very simple query to some of the more advanced features in both of these languages for querying and we'll also go over some uh of the really advanced stuff like uh you know how to figure out how efficient your queries are so that you make sure that you have really really uh you know efficient and effective query so you don't get yourself into trouble when your tables grow uh and your data grows in your organ you have billions of Records in a Salesforce table or Salesforce object and we'll go over some things like asynchronous SQL as well which are pretty handy when you uh get into bigger and bigger instances and you get more and more data so we're going to go over everything from the basics to the more advanced stuff and by the end of this my hope is that you will know absolutely everything that you need to know to be successful and virtually any situation you can find yourself in when you're querying for data in your Salesforce org but before we get into absolutely everything about Circle and social make sure if you enjoy this video to like it because when you do it helps this video get out to more people just like you that want to learn about this stuff for free too so you enjoy the video like it now let's get back to everything about Soquel and social so with that said the first thing first couple things we need to do uh we'll just start with the first one the first thing that we need to do is figure out uh what the point of these languages are what is the point of SQL and what is the point of social and for both of them the point is to be able to in your code right in your in your Apex code to be able to query for records in your Salesforce objects so that you'll be able to manipulate that data in your Apex right so basically when we're writing these queries we want to retrieve records from one or potentially more of our objects so that we can you know Loop through those records or iterate over those records and make updates to them or modify them or delete them or whatever right and so cool and social are how we retrieve that data to work with it um so it's extremely important it's one of the foundational building blocks so if you have nothing no idea what so cool is uh and you know you've been struggling trying to figure out how to actually manipulate the data in your Salesforce org this is your answer so by the end of this you'll be able to you know do that really really easily and you know have a good time the second thing is uh for all of you who are coming over from another programming language another tech stack that use SQL or SQL which is the structured query language SQL stands for structured query language SQL has some similar characteristics uh but it's very different so um unfortunately it's not really a one-to-one transfer when I came over from a language that used SQL it helped me but there was still so much I needed to learn about Soquel uh to be able to you know write efficient queries that did what I needed them to do and all that kind of stuff so with those couple of introductory things in that groundwork set let's figure out together how to build a circle query okay so before we actually write any Apex code that uses uh you know so cool the first thing that we need to figure out is just how to write a so-called query in general how do we structure a so-called query what does the basic structure of a so-called query mean all that kind of stuff and the easiest way to show this is by using the dev console so the first thing that we're going to do you will rarely see me use the dev console in my videos but for learning purposes I do think this is the easiest thing to learn how to write so-called queries in so um the first thing that we're going to do is open up the dev console and to do that you'll just click this gear uh over here and Salesforce and click on the developer console and the developer console will probably spaz out on you just like it always does for me but whenever you get over here to the dev console you'll notice that there's a tab down here on the bottom called the query editor and you'll click the query editor and in this box here we will write our queries together right and I'm just going to write a simple query for you and explain what that simple query is doing um so that we can kind of get just the absolute Basics under our belt here so what I'm going to do is say select ID and name from count and what this query is going to do when I press this execute button down here in just a minute is it's going to select the fields ID and name and return the values of those fields uh you know the value and the ID field and the values in the name field from the account records on our account object right so basically we're saying we want to query for records on the account object or from the account object and we would like when those records are returned to have the ID and the name field data return to us as well and like that those are the only Fields data that we need returned to us right so when we run this and hit execute you'll see that we get the ID field and the name field of all of these accounts in my system I'm very surprised I've done enough tutorials and random junk to have 2458 record account records in uh my Dev org but basically uh what you can see here is we've got a bunch of accounts and we've got their names and we've got their IDs just like we've requested right here in our Circle query which is pretty cool right and that is the the basics you know the just the absolute basics of writing a query you're going to select the fields that you'd like from an object that you'd like and there's a lot more to this uh you know and and we're going to go over all the different building blocks of circle queries but this at its foundational level is what you're doing you're selecting the data from fields from an object uh that you want that data from right so in this instance I want it from the account object but maybe I wanted it from a contact object or a custom object in my system or whatever else so really important to know now there's a couple of other things that I just want to talk about when you're building these queries just in case you're unfamiliar with them uh the first couple things are number one you know how do you know the names of the field and how do you know the name of an object that you want to query from query data from and um if you're not super familiar with querying I'm going to assume that you're also probably not super familiar with how you figure out what field uh you know what the name of your field is the API name more specifically and what the API name of your object is and it's really easy to figure out the API name of your object in the API name of your field if you go to setup and you go to the wonderful object manager over here and you find your object that you care about so um let's see let me see if I can find a custom object in here somewhere I think I just went by one so I've got this um custom object in here and I want to query for the data in this custom object and so what I'm going to do is I'm going to click on this custom object name here and I can see in the details section of this custom object that the API name is org related list user preference and so this if I go back into the dev console is the object that I'm going to query from query data from now so I would put this in this API name in here for my object and then I would go into fields and relationships and figure out the names of the fields the API names more specifically which are under field name here now you may have seen that some Fields just look like this uh you know they don't have this underscore underscore C and other fields do have this underscore underscore C and the same thing for objects if you're not super familiar with this anytime that you create a custom field or a custom object they are post-fixed with an underscore underscore C so all this underscore underscore c means is that it's not a standard field it's a custom one and when you don't see underscore underscore C it means it's a system generated standard field okay so I'm gonna grab you know a couple of fields here we'll just say field names to exclude from table and we'll bring back up our Dev console and we'll add that here to our list of fields that we would like to select right and if I hit execute on this uh we can see that it did indeed return to us the only result apparently the only record that I have in this table uh and uh it has no data in this field to exclude from table but we at least know that it has no data in here because it's blank so that's kind of how you'll go about finding the objects names and the object field names that you want to query for and you um you know it's really important that you know how to do that because very often the names the API names of your objects or of your fields are actually different from the display names that you'll see in your Salesforce or it so just understand that when you're making these queries you need to go if you don't already know the names of the fields to the object manager find the object that you care about copy its API name and use that API name in the front from statement and you need to find the names of your Fields by going to the fields and relationship section and copying and pasting the fields from there okay now I think that covers the absolute basics of a so-called query so the next thing that we're going to go over is figuring out how to you know limit the data that we return or filter the data that we return by taking a look at where Clauses in Clauses and the limit clause in so-called queries all right so there's a lot to talk about when it comes to filtering out records from your circle queries or uh you know I just want to address this real quick I know there's a lot of people out there that think uh or or believe that it's pronounced sock well and you're probably right but I'm the so cool guy so you're going to hear two different pronunciations sock will Soquel sorry if I've said it wrong but I are pronounce it so cool so now that we're over that let's get back to what we're talking about the filtering of queries um when we're talking about filtering queries one of the things that comes up the most uh when I'm teaching new brand new people about filtering queries is why do I care to filter a query in the first place right if I just uh query for all the data in a Salesforce table or a Salesforce object then I've got all the data and now I can do whatever I want to with it you know I can filter it out in the code or I can do whatever else and uh I totally see that argument at face level but once you kind of figure out how things work uh if you do that it will not work out for you in the long run and that's for a bunch of reasons um for performance reasons and for the most common reason of all Salesforce limits uh which uh you know are present everywhere when you work in Salesforce so let's go over the limits and then we'll talk about the performance stuff um just like with everything else Sokol has limits and in most cases when you are doing a so-called query you are going to be limited to only returning up to 50 000 records in a in the entirety of your Apex transaction so not just in one query but in all the queries that happen in the course of a single Apex transaction which you might have a lot so if you're querying for all the records in every table you're going to hit that 50 000 records pretty quick in most instances unless you have a really really small org um the second thing is uh once your tables grow to a certain size and Salesforce and the millions of Records I don't even think there are like hundreds of thousands of Records um there are also indexing limitations on your so-called queries you're basically your circle query has to be a certain level you know abide by it's difficult for me to go over with somebody goes no no uh background of so-called query uh we're gonna go over this more in the advanced section down down the line but just know that there's something called um indexing limits and once your table hits a certain size that becomes really important or else your queries are just not going to operate and we're going to talk more about this indexing limitation a little further down this video once we get uh through all of the other kind of basic details but I don't want to bog us down right now just know that that's a really important limit that that comes into play with these filtering mechanisms here the last thing is performance right uh Circle queries if you didn't know are probably the least performant thing in your code as in they're going to take the most amount of time to to uh run uh then most of the things that you're going to write in your code uh if you go in kind of Benchmark what takes the longest in your code chances are your queries are always going to be the things that take the longest unless you just got some really bad Apex that you've written um so the more that you can filter these queries the more performant that your queries are going to be which are going to save you time uh when your code is executing and that's important because there is a limit where you can only have your code run up to 10 seconds before Salesforce says hey your code's run too long we're not going to attempt to continue this uh this code execution here and they'll just shut it off right so or they'll just stop your code from executing and they're not going to shut down your sales force instance or anything I want you to get confused so they're going to stop your code from executing if it takes more than 10 seconds so where Clauses and filtering out your data become super important for all those reasons um so now that we kind of have a background as to why filtering is important and the benefits you get for it let's figure out how we actually filter um in practice right so I'm going to go back to the accounts table and we'll say select ID name oops I forgot the from from account where in accounts name is equal to Taco because I have a feeling I've got a few accounts out there that I named taco um so basically what we're saying in this query now is we want the data from the ID and name field from the account object uh where from every record that has a name of taco and we only care about the records that have a name of Taco for our account object and uh thankfully I was right and I've named some accounts taco doesn't surprise me at all and um we've returned only those two rows which were named taco which is pretty cool and you can see that that ran pretty fast if I drop the where account name equals taco you can see that took you know like a second right um where in comparison the other one took just you know it's basically instant is basically there as soon as it loaded the other one you know we had about a second lag there where it loaded and um in your code that will actually take even longer this uh query editor seems to be just a little bit faster than uh when when this is actually executing an apex and the more rows you have the slower it gets you have 50 60 70 100 000 rows this can take forever and ever to populate uh because we're Gathering up a lot of data and a lot of rows so just by adding this name filter um we've got a better query we've already narrowed it down to just the things that we care about um and so our code has less to do and we have a um you know a faster query out of it more performant query now you can filter on virtually any field that you can think of aside from Rich Text fields and long area text Fields um those are not filterable field types so I'm sorry but you can't uh you can't use those field types to filter um on in your queries but let's take a look at nothing you know now that we know what the where Clause is the importance of the where Clause all that kind of stuff let's take a look at the many different ways in which we can filter our data because they're pretty cool and there are a lot of them okay so the next thing that we should go over is probably logical operators and logical operators are the and or and not operators that you can use in these where clauses not is a logical operator that you will not often see but it is still important to know that it exists and how to leverage it so we're going to go over it in just a bit but we're going to start with the and and the or logical operators so right now we have a so-called query set up and we have this where clause in here that filters out our accounts and only gives us accounts with the name Taco in it but what if we want to not only filter it out based on the fact that an account's name is Taco but we also need it to have an annual revenue uh equal to 12 000 or maybe an annual revenue greater than 12 000. um to do that we would use the and Clause right and now what we're saying is we want all the accounts where the account's name is taco and it has an annual revenue of 12 000. and so if we run this query now we're going to end up with is nothing because uh none of our records none of the records in my system anyway have an annual revenue filled out so we went from two accounts now down to zero accounts because no accounts meet both criteria here none of them have the name taco and an annual revenue of twelve thousand so it's important to know that this exists because you can chain a whole bunch of you know and clauses together to get more and more specific about what account you would you know like what accounts you would actually like return so you can get really specific with your uh query results which is great the more specific you can get the better um now what if though you wanted all the accounts where the name was Taco um or the annual revenue was twelve thousand so you don't need them both to be true you only need one of them to be true in that case we would use the or logical operator which basically now says I want all of the accounts uh where a the name is equal to Taco or the annual revenue is greater than 12 000. and if I execute this now we can see that there are a bunch that fall into uh this category right because there are um accounts in existence that have a an annual revenue greater than 12 000 and there are two accounts as we saw before that have the name Taco so uh this or operator gives you the ability to have you know one or more of these true and if one you know if any of these uh essentially criteria that you've set up for this where uh where Clause are true then the records will be returned right and or means one or the other is true not that both are true like and and so um you know it's pretty useful and and pretty cool these logical operators are super important to use and to know they exist because you can get really really specific with your queries another thing that's really important to know about logical operators is you can use parentheses to make even more complex uh filter criteria so for instance right now we have where the name is equal to Taco or it's equal to annual revenue of greater than 12 000 but what if we wanted one of these to be even more specific we could wrap this in parentheses and say an annual revenue greater than 12 000 and name is equal to see one of these in here Gene point right so now what this criteria is telling uh the circle query to return is any accounts where the name is Taco or any accounts where the annual revenue is greater than 12 000 and the name is equal to Gene point so uh we can see that there is one that exists here and if we execute this query now we're only going to get three results we've got the one um account that's name is genepoint and that's annual revenues over uh twelve thousand and then we also have our two Taco accounts still so through the use of these uh parentheses to kind of essentially bundle um uh a bundle filter criteria together then we can get these kind of really specific um filter criteria grouped together in different places so if you ever need criteria like this where you know one thing is true or another grouping of things is true just use parentheses and you can start to group things together like you see here now uh the last one that we need to go over is not and basically what you would do for not is you would say and they're not like this right so now what I'm asking for is any account where the name is taco or any account that does not have an annual revenue over 12 000 and a name of Gene point so if I hit execute now we should get a whole bunch of results uh quite a few right 2457 because these are all the accounts that don't have an annual revenue of greater than 12 000 and a name of Gene point which is basically everything except for that one account right so pretty cool knot is uh useful um they're uh you know so it's good to know that it exists you won't see it in practice a lot but it can be useful from time to time all right now that we've gone over logical operators we should go over comparison operators because uh there are some pretty awesome ones that we have yet to go over and uh you know they're important it's important you know about them because filtering these queries is one of if not the most important things that you will do when writing them all right so we've gone over our logical operators together the and statement the or statement and the not statement but we still haven't gone over what comparison operators are and you know we've actually already used a couple of them this equal sign where we say name is equal to Taco uh the greater than sign where we said annual revenue is greater than 12 000. but there are several more and there's a couple of unique ones uh that you will use relatively often for so-called queries so let's bring them up over here and take a look at them now the first handful are pretty straightforward equals not equals less than less than or equal to greater and greater than or equal to right these are pretty basic things that uh I'm I'm pretty sure you can deduce what they do this means that a string or you know the field is equal to a specific thing this is not equal to a specific thing that's less than less than or equal to greater or greater than or equal to I'm not going to go over those anymore but there's a handful of comparison operators that are unique to um uh to to soak them that you may not be familiar with like the like um operator and the like operator basically allows you to say hey I I would like all of the records where a text field is like this string right it's not exactly this string of characters but it's pretty close right um and we're going to do an example together here in just a second to show you more about that but basically the like operator is saying you know I want a string that's pretty close to the one that I'm I've got here but not exactly so it's kind of like a fuzzy matching type thing if you're familiar with that terminology um the other uh logical or sorry comparison operator that we operators that we have are the in-claws this is one that you will use all of the time or the in a comparison operator you're going to use this one all of the time so we're definitely going to do an example of this together but essentially what we're saying you know if you see this example here is we want all accounts where the billing state has a value of either California or New York and we have not in which is the opposite so we want a billing state that does not have California or New York in it uh or we want accounts that do not have the billing State of California and New York in them and then we have this uh includes and excludes comparison operator which is unique to multi-select pick lists and we'll briefly go over that in just a bit as well so let's bring back up the good old-fashioned um Dev console and take a look at this so right now we have the uh query so that we have a name equal to Taco right well what if we didn't want it to equal Taco we just wanted it to be like taco you know maybe maybe it's not just Taco that we have but we have Taco Bell and taco Tuesday as an account and taco Thursday and taco trouble and who knows what else right there's a lot of accounts out there that have the string Taco in the name but uh there could be a whole bunch of different varieties and the way that we deal with that is with the like operator um the like comparison operator and so now I can say I would like all of my accounts where the name is like taco and I can tell it how I want it to compare the the strings um in this name field so basically what I'm saying here with this percentage sign which is called a wild card character is I'm saying I would like all accounts where the name starts with Taco but it can end with anything essentially um so if I run this now I've got two accounts I've got my taco account and I've got Taco Bell whereas if this was just equal all I'd get was taco right so now I've got two accounts taco and Taco Bell which is pretty cool again if I just made this equal to Taco again all we get is Taco but with that like statement we get all of the strings in the name field that have taco and anything after them now you might also want the reverse you might just want it so that uh you know anything can happen at the beginning but we need Taco to be in there in the end right so the string can start with anything but it has to have Taco uh in there and if we you know run that query you can see the Taco Bell disappears but we get extra Taco which is the name of an account so that uh is also an available option to you or maybe you want to just see if Taco kind of exists in that uh text field at all right maybe we just want to say okay we want to find all accounts where the name just has Taco in it now I will say when we get into query performance more in the advanced section of this video here um this is gonna end up in you taking a pretty big performance hit and uh I can have problems with query indexing but for the sake of showing you how this works if I do this now you can see you get extra taco taco and Taco Bell because we can match on both uh we're essentially saying at this point we just want Taco to be in the string somewhere not at the start not at the end but somewhere right so that's kind of how this like operator works um there's also another wild card that you can use for this like operator and it's an underscore like so and you will very rarely see this uh used but basically what this means is there is one more character just a single character after this that it can match on so for instance this could match on an account named taco it could also match on an account named Tac it could name a match on an account name I don't know tackle or something but if we execute this you can see I still get back Taco despite not having a letter in here it's basically just saying I want one character after it um so when you see those underscores that's what it means it's matching on a single character instead of just like infinite characters essentially which was is what that percentage sign does okay so now we know how to use the lag statement I hope I hope I definitely hope at least um let's take a look at the in statement so we kind of uh looked at it in that example but basically we're going to write something like where an account name is in uh you know this set of data and maybe we want chocolate or something like that so basically what this is saying now is I want all the accounts where the name uh has either the value taco or the value chocolate and if I execute this I don't think I have any chocolates unfortunately but you see that I get the value Taco backed right so I still get that taco account let's see if I can find some other I have a list of all my accounts yet so maybe maybe I want to find all my cool accounts too like so and you can see it brings back a lot of results I've got all of my cool accounts back and I also have my taco account here the not in Clause is essentially the reverse so this is going to return all of the um rows in which are all of the records in which uh you know that aren't named taco or cool account so if I hit execute on this one we're gonna get a whole bunch of rows again but basically they're all the rows that are not taco are all the accounts that are not named taco or cool account and the last one that we have again only applies to multi-select pick lists um let me just uh bring this up if I can find it multi-select pick lists are kind of unique entities when you use a multi-select pick list essentially what happens is it writes a string in the background or it writes to a text field essentially in the background and when you have multiple selections they're split up by a semicolon right so it's like you know in this example that they have here uh if you queried for your multi-select pick list and it had a selected AAA and BBB then AAA semicolon BBB is what would get returned to you in a string uh if you haven't heard multi-select pick lists are kind of a pain but uh you can also use this includes uh set up here for multi-select pick lists so that you can say you know if my multi-select pick list uh is selection includes AAA and BBB or it includes just CCC then it can figure that out right and it's the reverse for excludes if I wanted to figure out if it didn't have CCC in it or something then I can just use excludes and say return to me all records where CCC has not been selected in my multi-select pick list if I used the excludes instead of includes so that's kind of all it's used for um it's kind of unique uh and you know if you get yourself into multi-select pick lists enough you will probably find yourself using that from time to time but just so you know that it exists it is kind of useful um it's kind of useful to know about so yeah the last thing that I want to point out about uh filtering your data using where Clauses or just filtering your data in simple queries in general is that if you try to filter data with encrypted Fields it's not going to work so if you didn't know you can encrypt your fields in Salesforce uh and there are a lot of reasons that you might want to do that to to safeguard your data for one reason or another especially sensitive data but for those fields that are encrypted you're not going to be able to filter on them in your circle queries you're also not going to be able to order on them and there's lots of other things that you can't do with encrypted fields in general but just know that if you are using a field that you feel like should be working and should meet criteria if it's encrypted you're going to be presented with errors that say that you can't filter on that field so just be aware all encrypted field fields are basically not going to allow you to do anything filter wise so I think that's it for filtering now we've got kind of uh our our Tools in place you know how to use comparison operators and logical operators and how do the where Clause works I hope the next thing that we should go over is the order by clause and what the order by Clause allows us to do is essentially organize our data when it's returned to us when we query for it right and you might say well what do I care for why why do I need to order my data what's the point well maybe you have a list that you're presenting to your users um you know maybe you have a data table and you want to present to them a whole bunch of Records and you want them to be in alphabetical order well if you just order your so-called query um then you don't have to do anything else right you're so cool if you use this order by Clause you can order your records however you'd like them ordered on one or multiple fields and then you can just display that data to your user without having to do a bunch of operations on it to get it in a format that the users would like to see it in and to do that ordering is pretty easy you just um use the order by by clause in your circle query like so and if I wanted to order by name I could do that um so by default I believe the uh results are ordered in ascending so ascending order is going to be like a b c d e f g like so and if we hit execute you're gonna see that I'm ordering by name and it's ordering an ascending order so a down to the letter Z if I have any accounts that start with z um but if I wanted it to be in descending order I didn't want it to be a to z I wanted to be Z to A you can actually use descending like so and it will switch it up and now it's starting with the uh University of Arizona because I guess that is as far as I go in the alphabet to the letter U for my account names and it goes down to you know the ace all the way to the bottom so that's kind of how you would order things by name if you wanted to just denote that you were doing it ascending you can also use ASC which stands for ascending and circle and we'll get the same as we got before but it's not actually necessary to put ASC it will only ever be necessary to put d e s c for descending because this is the default functionality for SQL to present things in ascending order there are a few other things you should know about the order by Clause um the first is that you can order by multiple Fields so I could also add annual revenue in here and it will take that annual revenue field into consideration when it's ordering things so you can kind of order by as many fields as you would like to the second thing is that if you don't add the fields that you're ordering by to the fields that you've selected in here what you'll find is it won't order by them right and that's because it doesn't actually have the data to order them so even though um you'll get it returned uh you you won't actually see your data or organized by that particular field so make sure that if you're ordering by it to also include it in your selected Fields here the last is another option where um you can say whether or not you'd like your nulls to show up first or your nulls to show up last like so and what that means is say for instance I've got annual revenue here let's just remove the order by name field and just order by annual revenue I've got my annual revenue here and what I want are for all of my null annual revenues to show up last in my list so if I do that what should happen is the annual revenues that are filled out will show up first in my list and all the rest that are null will show up at the end of my list alternatively if I did nulls first the reverse would happen annual revenues that were null would be at the top and the ones that were filled out would be at the bottom but I'm not able to view them right now because they fall outside the range of 2000 records that are being displayed to me so that's pretty much uh it as far as it goes for ordering this will just make your life you know a little bit easier in a lot of cases where you're presenting this data you won't have to you know do any manual ordering in your code this will just allow you to kind of organize your data as it comes in from the so-called query the next Clause that we should talk about is the limit clause and the limit Clause is basically a clause that allows you in your circle query to say I only want this amount of Records returned to me no matter what so for instance right now this query returns to me 2440 records but maybe I only ever wanted to return no matter what 2 000 records I can just place this statement in here and say I want to limit this query to a total of 2 000 records and if I hit execute what you'll notice is even on this exact same query the previously returned 2440 records I only got two thousand and that is because I've added this limit clause so there are times in your code that you will find that you'll run into where you only want to return maybe the first 200 results or the first 5 000 results or something like that right maybe just one result you just want the first result that you get and using this limit is going to allow you this limit Clause is going to allow you to do that so just know that this exists there are certain circumstances you'll get into to uh you know where you'll want this or need this and if nothing else make sure you limit your queries to a maximum of 50 000. again that's per transaction but you at least don't want a single query going over it uh or else it's going to um hit limits right so in most cases most cases we'll go over the rare cases where that's not true as soon as but that limit Clause allows you to limit the amount of records that get returned to you when you do a circle query so definitely leverage that wherever you can the last so-called Clause that we're going to go over before we go over some more advanced um so-called stuff and start using silkel and actual Apex is the offset clause and the offset Clause is super useful when you have a lot of data that you need to display to a user um but you know you don't necessarily want to display it all at once right or you can't display it all at once because it would just be too slow and you know be detrimental to the user uh you know be a detrimental user experience because everything would load so slow and all that so that's really where the offset Clause comes in handy and basically what you're going to do is say um you know offset and then tell it the amount of Records you would like to offset so for instance right now I I just did a query where I um had 2 440 rows returned to me this is basically all the accounts in the system and I would like to offset this by 500 accounts which means that when I run this query the first 500 accounts are going to get dropped they're not going to be visible or they're not going to be returned in my query to me instead only accounts 501 onward are going to get returned so what I hope to see is a total rows returned of 1940 because 2440 minus 500 would be 1940. so if I hit execute I now have only 1940 records returned to me because I have offset my records by 500. and if I offset them by a thousand we'd have 1440 and so on so what this allows you to do is kind of paginate your queries through this offset mechanism so you know you could imagine a user's viewing 200 records or something and they click the next page button and what that does is it goes back to your query offsets it by 200 and then it Returns the next 200 results to them or something along those lines and that's a really useful way to you know return small subsets of data for a giant set of data to display to your users so offset is really really valuable and it's something that you should uh take into consideration especially when you have a lot of records that you're displaying to users in a user interface all right the next thing that we need to go over with so-called queries are how to query for related records right so for instance maybe on the account I want to query for all of the contacts related to me and get all of the data about those contacts how can I do that in a so-called query additionally maybe I'm a contact and I want to get data about the account that I'm linked to um how do we do that in a circle query and if you're unaware um in Salesforce the way that these connections are made are through what are called lookup fields or Master detail relationship fields and so real quick before we figure out how to actually query for these related records or this related data in our queries let's just take a look at what lookup fields are um and how they kind of Link our objects or really our database tables together so if we come back over here to the object manager and we take a look at say the contact object and we look at fields in relationship we can see that we have this field called account name and it is what's referred to as a lookup to account and basically what this means is uh our two objects are connected and really an object in Salesforce is a database table if you're not aware so this is how we connect our database tables together and relate them to each other in Salesforce through these lookup relationships so anytime that you have a lookup relationship or a master detail relationship on your uh object in Salesforce you'll be able in your SQL queries to query for related information about uh those records or that record in the lookup field additionally if I'm on the account object because I know that the account object or sorry the contact object has a lookup to the account object I can query for all of the contacts related to the accounts that I'm you know currently querying for as well so we can query for that relationship data much like you can um in structured query language or SQL um in in what are called inner and outer joins they're quite a bit different uh and they're quite a bit more limited than what you can do uh with uh SQL SQL joins but uh there's still some functionality there and that's what we're going to take a look at is how do we join that data together into a single query right all right so let's uh take a shot at that by opening up the dev console again and uh well okay and opening up the query editor and this time we're gonna query for the um for contacts right and and uh we're gonna take a look at two types of relationship queries uh but the first one that we're going to take a look at is querying from a child look up relationship to its parent so we're going to do a parent relationship query because I think this is a little easier to grasp all right so let's write our query first we're going to select ID and maybe the first name field from contact and we'll say where first name equals Jam bet I'm pretty sure I got at least one Jam bed in there if not a whole bunch more and when we want to query for data on our account lookup field since this is a standard out of the box field right these are going to be different than the custom Fields we're going to take a look at both how you query for related data on standard Fields standard lookup fields and custom lookup Fields but since this is a standard lookup field it says it's API name here is account ID so let's just grab that bring it over here and drop it in here now if I just query for account ID I'm just going to get the Salesforce generated ID for that account which could be useful to you or it might not be useful to you I don't know depends on your situation right but um if I wanted say for instance to query for the name of the account let's just see what this comes up with for now so I just get the ID right not super helpful but maybe I want more information about this account well for standard Fields basically what you're going to do is drop the ID and put a dot operator and write the name of the field after that dot that you want to grab information about and hit execute and now you can see that jambet is related to an account whose name is Jambo James and uh you know if I also wanted say for instance the account annual revenue again then I can do that as well and bring that in here that's kind of how you get information from your lookup fields from your related uh your records that are related or are linked to your contact um the other thing is maybe you want to in your where where Clause um you know filter based on your lookup field or lookup fields and you can do that too so you can say account dot name is equal to Jambo James like so and we'll get that back but you know if I just made it where it's equal to jumbo or Jambo you get nothing right so you can filter by these fields too which is super useful um and really important to know now a really key distinction here is we just took a look at a standard out of the box lookup field as in when I installed Salesforce that account ID well you don't really install Salesforce my apologies but when I set up Salesforce when the Salesforce instance was set up for me um uh you know it had this field already on the contact object and there's tons of these pre-made fields for you and they're referred to as standard fields but what if I created a custom lookup field to another object in my system so if I come in here and I create a lookup relationship and I make it to who knows it could be anything we'll say it's a look up to um order sure then after I create this field you're going to see that we query for it in a little bit different of a way and um we'll get through it someday some way Maybe and now if I look for that order field that I just made you can see that it is a lookup to the order object but it's got the field name of order underscore underscore C which as we talked about before underscore underscore C just means that it's a custom field that we custom made for our org to do whatever we wanted it to do now if I wanted to query for related data on this order underscore underscore C field what I have to do is change the underscore underscore C to an underscore underscore r at which point I can query for whatever related information I'd like to query for uh it's been a little while since I've used the order object so let's see maybe we want the order end date if we hit execute you can see we don't have one because it's null the order field is null but that underscore underscore R is how how you're going to query for custom lookup or custom uh Master detail relationship uh fields that you create on your objects whereas for out of the box lookups you're just going to drop the ID so if you remember originally this was account ID you're going to drop the ID off of it uh add a DOT and follow it by the name of the field that you'd like to retrieve information about right retrieve data for um and that's kind of how we go from child to parent to grab data in so-called queries the next thing that we're going to take a look at is how to go from a parent to a child and grab data about all of the children in your parent-child relationship and this one is a little bit trickier getting child relationship data is uh really not that difficult but it's a little trickier especially the first time that you do it because figuring out how to properly query for it and then deal with the data is just it's kind of a whole new thing right it's it's different from what we've done so far kind of so we know that this contact object right has a look up to the account object right and and we we literally just went over this so uh because it has a lookup relationship to account uh we know that accounts have contacts that get linked to them right and the database or or you know our account and our contact objects are linked together so what we need to now do is see if we can query for our contact here you know jambet uh when we're querying on the account object for Jumbo James here so let's delete this query that we've got going on right here and create another one and say select ID name from count uh where name is equal to jumbo James like so now all this is going to do right now is uh do what we are used to it doing which is give us back the name jumbo James in an ID so how do we return all of the contacts that are linked to this account that have a look up to this account right um that is the question and the way that we are going to do that is first we're going to take a look at that relationship field on contact the account name field and find this thing down here called child relationship name and this child relationship name is how you are going to figure out uh is it's basically how you're going to query for your contacts right within your account query so we need to grab this and copy it and once you have that from your lookup field you're going to come back over here and we are going to say select ID name and put a comma and then put two parentheses and we're gonna write another SQL query within this one so we're going to say select ID first name from contacts right not contact but contacts because that is what the relationship name of this lookup field is and so once we have that in place if we hit execute what we will get is the ID the name and then what's called a Json string or a Json representation or basically an object representation of the contacts that are linked to this account jumbo James if we take a look at that we've got a couple we've got Jim bet here uh and a couple people named batchable well quite a few people named a batchable so you can see we got a whole bunch of contacts returned to us in this list of contacts which is pretty cool and um so that's a that's a really easy way to figure out you know all you know everything that's related to this one account the last couple things I want to show you about these relationships are uh one real quick thing which is around uh a custom Fields again the place where you find this child relationship name on a custom field is just a little different than where you find it on and out of the box field so you'll notice that it's not right up here you don't see that child relationship name right up here but if you scroll down just a little bit you can see that it's right there child relationship name contacts for this other one as well so if I was querying for instance on the order object instead of the account object I would do the exact same thing select ID first name from contacts just like so but it'd be on order oops order instead of account the last thing that I just want to go over with you guys before we run away from relationships uh and and start talking about some other important things about queries and getting into Apex is if you're in an org that you're really not super super familiar with and you don't want to have to go through your entire object list and figure out what's related and uh you know what's not you can go to What's called the schema Builder and uh start looking into things and this is really a quite quite a bit easier way to figure out your relationships between tables so for instance I maybe want to look at my contact table right and well Let's uh Auto lay this out and we can see from our contact table this is kind of like a more traditional database table of view so anybody that's coming over from a different language or Tech stack this might be a lot easier to figure things out but this is an easier way to kind of take a look at the fields and the lookup relationships and the master detail relationships that link your tables together so you know you could see something like look up user and this last modified date and then you can just come right over here in your object and find user and add that table and then you can start seeing the relationships from user to these objects as well so this schema Builder is a really good way to figure out all of these relationships and how you can start uh you know incorporating object data into your related object data into your queries so really useful to know this exists especially if it's your first time in an org um really really handy tool too take a look at so all right with that said now we're going to uh get into a couple of other so cool related things um some stuff around security and so-called queries uh grouping Fields a bunch of other stuff so let's get into these uh more specific I guess maybe less used but important features that you should know about so cool before we get into actually writing some Apex for it okay so the next thing that we should go over real quick are uh the couple of uh Clauses that you can add to your circle queries that take security into consideration and those two Clauses are with security enforced and using scope and this with security and force Clause that you can add to your circle queries um basically just allows you to take um your object and field level security into consideration when you're querying so the object and field level security are the things that you set up on your profiles your permission sets so for instance if this query was written and got executed in the code but the user that was running the code didn't actually have access to say the last name field on contacts well then this data for the last name field would be stripped out so that the user wouldn't see things that they shouldn't see so very useful this is one of the newer Clauses for so cool very very useful makes your life quite a bit simpler than it used to be when it comes to doing those field level security and object Security based checked check checks the other one is the using scope clause and the using scope Clause is more around record level security so basically what using scope is going to do is say uh you know only return data that has been delegated to a user to take on so for instance you know you can delegate tasks records to a user to do um you can use everything which is just silly you know there's no reason to do this but basically it'll allow you to see all records mine are all records that are owned by the current running user mine and my groups are all the you know uh Records owned by the user and the queue that the user is working in my territory if you're using Enterprise territory management it's all the records that are um owned by users in your territory uh Team territory scoping Rule and team can also be used here um so basically you just have ways to kind of filter the scope or the amount of Records or the scope of the records that you're having returned right and so this can make uh record level security um get taken into consideration a bit when you are acquiring for things but honestly record level security is uh really determined by the width sharing or without sharing clause in your uh Apex class that you're running we're not going to go over that but uh you know that's the most important factor when it comes to record level security but using scope can help so uh help filter things down a little bit more anyway if you want to all right so uh now that we've gone over that there's a couple of other little unique Clauses that I just want to go over real quick the first uh few are these four view for reference and four update and these are pretty cool and I feel like a lot of people uh or maybe most people don't know that these Clauses exist but you can use these four view Clauses to actually update the last viewed by field or recently viewed uh um or and Adam sorry a record to a recently viewed list um for users so say for instance uh you know you build a a a user interface or some kind of lightning web component kind of like this one I have here that's uh that I made that's called the character counting component where you can actually count the characters in a field when a user views this component I mean they viewed the data right it's technically recently viewed data and so if you want to do uh have your query just automatically kind of update the um you know last viewed by or last viewed date rather and um update the recently viewed list view for your user then just using this four view Clause will do that for you which is like pretty awesome in my uh opinion so um you know anytime you build a UI kind of similar to this one where you know they're viewing the data I mean consider using it because it's pretty useful it you know save you some some time and some headache in some situations uh for update is uh basically it's very similar so if you build something again like this uh in and it's more like this record edit form that I have and you know that if somebody lands on this they're probably gonna update it then you might want to use this for update clause in your so-called query which you can just kind of tack on to the end of your circle query both of these you just kind of tack on to the end and um what this does is it actually locks your record so that nobody else can edit it while you're editing it and that gets you out of you know record locking binds that you can get into uh when you start using or sorry when you have a lot of users updating data in your system the last one of these uh Clauses is the for reference clause and the four reference Clause is probably the one that you're going to use the least of them all but basically um you're supposed to use this when the only situation I've heard of this used in is when you uh you know are also displaying related records on a uh view potentially on a in a UI and you want the um last reference date to be updated and you know you want uh recently viewed data to be updated as well um I'll say Israel you're going to use this and if you do use it just like it says here you're probably going to use it in conjunction with the four view Clause um but again this is just saying you know uh well direct the user viewed this viewed a record and they also referenced a bunch of related records uh you know related to that viewed record so let's let's update the fact that they took a look at those so if you want to that one's out there but again um it's less commonly used I I I've honestly never seen this one used so but it's good to know that it exists so the last thing that I want to go over real quick before we get into the group by clause and the having clause and the aggregate functions which are kind of going to do all together uh in our really important to know what they are is this Fields function that was somewhat recently added to the uh sokal language um this Fields function is a function that I honestly am going to suggest you use sparingly if ever um but what it allows you to do is select all of the fields on your object or a you know a subset of all the fields on your object so um this if you're coming over from another tech stack like or that uses SQL or SQL right uh this is the equivalent of Select star so it's cool that it exists now um but uh you know you normally don't really want to use select star uh very often at least in my experience and you don't really very often want to do this either but in the case that you do basically all you need to do to select all of your fields on an object is just say select Fields all if you want all the custom ones you say select Fields custom and if you wanted all your just all your standard Fields you'd say select field standards so if we came uh over to the dev console and we wanted to get all of the fields on the account object for instance say oops just limit this to maybe 50 records and instead of this you'd say fields all like that if we execute you can see there's like a trajillion fields that got returned here um but there you go there's all of our all of our Fields returned and displayed to us which is pretty cool to know exist um but like I said before use it sparingly because that slows down uh you know your return results for your queries as well and uh you really don't want to query for data that you do not need so good to know exists but use it with caution okay the last two Clauses that we're gonna go over together before we get into actually using Soquel in Apex are the group by clause and the having Clause but before we actually go over them we have to be aware of these things called aggregate functions and uh the reason that we need to know about aggregate functions is because both the having by uh clause and or sorry the having clause in the group by Clause uh depend on you using these in your query meaning that you can't use Group by and you can't use having without using an aggregate function and uh yeah I mean that's really important to know if you try to go use one of these group buys or that having Clause without an aggregate function it's just not gonna let you do it more or less so uh let's first take a look at these aggregate functions and figure out what they do basically there are five aggregate functions you have the ability to average a field to count the amount of Records based on a field uh to take the minimum value of a field the maximum value of a field and the sum of all of the uh values in a field across the records that you are querying against um so let's take a look at this in the dev console if we come in here and we do select um we'll say annual revenue again oops Revenue by the way SQL is not case sensitive at all I know I've been capitalizing everything but this technically also works as lowercase I just like to capitalize it because I think it looks cleaner it's more in line with what you'd see in other query languages out there in the world um anyway okay so we've got our annual revenue and maybe we want to see what the max value for our annual revenue is um on our account uh Records that have a an annual revenue filled out so where annual revenue is not equal to null right so basically what we're doing here is we're saying I would like to find out what the max value is in the annual revenue field for all accounts where the annual revenue is actually filled out and if we hit execute here we can see that Max annual revenue uh and uh we can see that it's uh 5.6 billion dollars so that's pretty cool if we just queried for annual revenue all accounts that that have annual revenue and not the max we can see that that does indeed look to be the highest total um annual revenue on any one account and you can of course do any of the other ones so maybe we want to see the average of our annual revenues across all those accounts which have annual revenue filled out if we do that we can see it comes up to uh I don't know what that is maybe like a billion dollars or something um yeah 1.137 billion dollars and of course you can do the minimum and uh the uh other options that you had there the sum and the count so if you counted this you'd come back with eight records which as we saw somewhere around here Maybe we have eight rows that have the annual revenue filled out so you can see that it returned a count of eight records so pretty cool these um aggregate functions and super important to know exist because they can be helpful uh at times when you're writing your code okay so we know that these aggregate functions exist and what their purpose is the next thing that we need to figure out is how we actually use Group by right so let's check out Group by and figure out what it means basically so when we use these aggregate functions maybe we would like to group them by a particular field right and for instance back over here where we've got our annual revenue maybe we want the max annual revenue but we want to um potentially group it by another field on the account object and I think there is an account an account type field so let's take a shot at that and see what we get yeah okay cool there is so basically uh what we're saying here right well we looked at Max annual revenue before we just got that 5.6 billion dollars but now what we're saying is we want to know the max annual revenue by our um our account type essentially and so we've got two rows returned to us now one that says for our customer direct account type the max annual revenue is 5.6 billion dollars and another row that says for our customer Channel our Max annual revenue is uh what is that 950 million dollars I think so this is a really cool way to group your aggregate function results uh together by a particular field um so that's that's more or less what the group by is and what it does it can be very useful if you want to you know group those aggregate uh results together in a particular format it's kind of like when you group in reports uh if you're very familiar with Salesforce reports I'm not going to go over that too much but grouping kind of gives you a lot of those reporting capabilities as well and the last Clause that we need to talk about is the having clause and the having Clause is basically a way to filter the results that aggregate functions return as it states here and essentially right when you have a where Clause normally you can't filter out by an aggregate function right but in our case we want to maybe and that is where this having Clause comes in handy so if we come back here to our aggregate query results that we just set up a moment ago if we wanted to say only return uh you know a Max annual revenue of you know greater than 950 or 950 million dollars I think is what that is we could do a having Clause at the end of this that says having Max annual revenue of greater than 950 here's your zero zero zero zero I think and what we'll see returned is just one row instead of two because that other row had exactly 950 000 as its maximum revenue and now what we've done is essentially filtered it out in our call here by saying we would like only to have accounts that have greater than a Max annual revenue of 950 million dollars so this having Clause is just a way to use aggregate functions as a way to filter out even more data from your query and if you can believe it we are finally at a point where we need to start checking out how to actually use so-called with Apex so now that we know about all of the most important features of Soquel um we should take a look at using it in Apex and figuring out how that works because there are some pretty cool things uh that uh you should know about so let's go ahead and make a class together but in the spirit of never using the dev console uh because I don't love it actually I say that and then I think you know what we're going to Benchmark things so let's just stick to the dev console for now um because it's going to make it easier for us to Benchmark stuff too it's yet the only other thing that uh the dev console is really good at doing so let's uh actually in this Dev console create an apex class and I'll just call this uh Apex SQL example or something like that and for those of you that don't want to follow along in uh the dev console because it is an outdated uh nightmare uh if you want to follow along in an IDE you could most certainly do so and um let me pull up and IDE here so we've got uh Visual Studio code if you don't know how to set up visual studio code I have tutorials going over all of the Ides showing you how to set up um you know Visual Studio code and IntelliJ to use for Salesforce but basically what you're going to do is just hit Ctrl shift p to bring up what's called the command palette and you type in create an apex class which you can see down here and then you'll just click that type in the name of your class hit enter and then you will be able to follow along and um not the developer console and that is what I would suggest you do for all of your development efforts at the very least you should use Visual Studio code to do your development or you could use IntelliJ which is what I IntelliJ and illuminated Cloud 2 which is what I personally suggest because it is by far the best IDE out there in my opinion for Salesforce anyway so whatever you do um I'd say follow along in one of these Ides instead of developer console because I would never suggest developing in it but for the purposes of what we're doing and the things I'm going to show you we're going to stay right here in the dev console for now which hurts my soul a little bit but also it's good for a handful of things all right so um let's go on ahead and write some code together and we're going to create just a public static method that is going to return nothing but let's see we'll call this Quarry or accounts you can name your method whatever you'd like if you're not familiar with how to write Apex at all I have a whole introductory series over the basics of Apex like methods and classes and variables and all that stuff so go check it out I've got a whole playlist for it called the Apex masterclass or beginner Apex tutorials um okay so we're creating a method here uh and we're just going to try to query for some accounts and figure out what we actually do with so-called queries in Apex so first things first if I try to just do what I've been doing in the query editor down here and start writing select you know name from account where uh annual revenue is not equal to null and it's going to complain about this because that doesn't make any sense in Apex uh like it does down here in the query editor so you can already see there's a whole bunch of problems and it's like what the heck are you doing man right so let's figure out how to actually make this query run in Apex uh the first thing that we will want to do is declare what is called a list you could also use a map and we'll go over a map in just a moment what we're going to declare a list of type account like so and we will name it account list and we will set it equal to this query right here and you have to put your query in these brackets so that it knows to place this query in a list uh in in this list essentially so you're going to put this query and these brackets and uh then just write your query like you normally would and what this is going to end up doing is it's going to go out and query the database for our records and it's going to just place all of these records that it finds into this list of accounts for us which is really nice um much simpler than a lot of tech Stacks you would work with where you'd have to actually go out and connect to your database and do a whole bunch more uh to retrieve your data in Salesforce you just have to do this inline select statement which is really really convenient and as you can already see for those of you that come from other Tech stacks I have not made this account object representation myself Salesforce when you create uh custom objects or for your existing standard objects just automatically creates this wrapper for you this account wrapper for you so it's really really cool um Okay so we've got our so-called query set up and we are assigning it to a list um let's run this and see what we get we're going to just output this uh this list into a debug log oops for now and uh yeah so we will save this and what we'll do to run this code is open up what's called the execute Anonymous window and you do that by going to debug and then there's execute Anonymous window and that debug drop down and what we're going to do is I've got to remind myself what I named class Apex silkwall example dot Quarry for accounts and we are gonna hit the execute button to run it and when it runs we are going to get a debug log generated for us over here in the logs panel and you can see that we did indeed get eight rows returned to us for our list just like we have been when we were using the query editor and we can see that we got that list of accounts filled out with all of the information that we asked it to be filled out with which is awesome so when you're acquiring for data this is exactly how you're going to do it you can also set this up using a map like so if you'd prefer to just query directly into a map and you can do that by saying new map ID account and then doing your query directly in here just grab this and drop it right in there like so and this will um return to you a map of your accounts with the ID as the key I'm gonna go ahead and put the ID in there otherwise I might get myself into trouble technically it always queries for ID in the background so you don't ever actually have to put ID in here but I'm not a fan of not doing that it's confusing to people that don't know that that's a thing so I'm going to save that and then we're just going to prove that this outputs in a in the map and run it so we'll execute our last execute Anonymous statement and uh hopefully that ran out there it goes and we can see from our debug statements when they load that we did indeed get a map output for us which is cool um all right so the only other thing that is really really important to know before we get into a little bit more advanced area of so-called Coring in Apex um and uh benchmarking things is that uh you're gonna often need to take a variable that you've declared in Apex and use it in your query here and it's great because you can just kind of do this in line it's really really quite simple but you have to use what are called bind variables so for instance if we wanted to say create an integer of our minimum uh annual annual revenue or something and set it equal to I don't know five thousand and we wanted to say you know where our annual revenue is greater than this minimum annual revenue then we can do that but what we have to do is put a colon and then put r variable in there and this is called bind variables or variable binding or whatever and what this allows us to do is essentially just place this integer variable directly into our query and have it run just like it has been hopefully when I save this my problems go away here I haven't forgot something silly which I maybe did oh yeah it's because I can't spell now if I save this it should work and um yeah so if we actually let me grab this here too add it in my map and so now if we save this um basically what we should get is all accounts where the annual revenue is greater than 5000 which is uh you know the eight accounts again but if we execute our Anonymous um Apex again maybe so slow someday uh we should see that work out a-okay and we do so uh it looks like one of them actually no no we still got eight results so we're good um so yeah and that that's how you'll use uh variables in your queries what you'll find yourself using bind variables for more often than not are actually uh those in Clauses that we talked about a while ago so I'm just going to show you real quick how to use that it's really not much different than what we already did but let's just create a set of data and we'll call it um account types and set it equal to a new set of strings and then we'll put some strings in here once I figure out what the names of the account types are uh from our Channel our customer Direct Dev console is struggling right now uh and customer channel so now we've got this new set of strings and maybe we want to say we only want to select accounts where uh the type oops type is in let's remove this whole situation here we'll say the type oops the type is in the account types set like so and you can just do that and it will work and uh we'll go ahead and copy this into our map so that gives us the results in this debug and we will save that and if we run it we should get a good old-fashioned list of results execute our last Anonymous Apex and uh so we got 10 rows returned that were of one of those account types so pretty cool all right and like I said um that that's probably one of the most popular places that you're going to use a buying variables in in an in Clause so I really want to make sure that I showed that to you now um it's probably time to get into uh some more advanced Circle querying and Apex and uh so what we're gonna do is actually go over what's called um that's at least what I refer to it I was pretty sure it's called um uh Dynamic SQL so basically what we're going to do is we're gonna produce a string right and that string could be built from who knows what uh you know it could be built from Custom metadata or a field set or any number of things out there that you can think of and what we're going to do is build a string uh that we can use to query for data in Apex so let's check that out we'll make another method and we'll call it public static void dynamic count worry like so and so basically what we're going to do is we're just going to declare a string and I'm going to call this string a query to make it easy and we're essentially just going to say you know select ID name from account uh where type in account type and you can indeed still use bind variables even in this string which is pretty cool I need to bring account types down here and then I need to update my string to use the right name for the variable like so so now we've got this query string and you can construct this query string however you want like I said right instead of it having to be kind of hard typed like we have here you can construct this from tons of different things so you can kind of dynamically generate a query through a string and what we're going to end up doing is saying you know a list of accounts account list is equal to database dot query and pass in our query string that we've written let's make this more obvious we'll call this query string to not confuse you and so basically what this is going to do is now we can take this string that we can build you know on the Fly based on who knows what per you know criteria and we can use the database class to actually query for this data using that string and uh you know if we debug this should get the same results that we did before which was uh what like 10 10 records so let's go to our execute Anonymous window and instead of query for accounts we'll call uh dynamic Dynamic count worry which I hope is what I call this and it is oh Dev console it's scaring me I think it's going to crash any second here um okay and so we ran that successfully without any issues and it should bring us back 10 results which it did and um you know this is unbelievably useful uh this Dynamic pouring because you can do some really really awesome stuff with it and you can still use buying variables as you've seen here you just you know do the it's the exact same syntax it's super easy you just put that little colon in front of your variable and it will pick that up in database.query and bind it just like it normally would in an inline query like you see here so just wanted to let you all know that this functionality does exist and it's extremely powerful when you want to uh build some you know really Dynamic code the one downside to this is that you're building this using a string so uh you know I don't know if you've ever clicked the where is this use button or tried to delete something and it told you you can't delete it because you know the field was referenced in an apex class or something when you use Dynamic so cool like this it will not tell you that you're using that field in a so-called query in one of your Apex classes so that is the one downside to Dynamic so cool but uh in general it's still extremely useful and uh yeah shouldn't be overlooked in my opinion okay so um yeah now that we have done that I think it's time to go over benchmarking and using What's called the query plan tool so that you can really figure out if your um you know queries are efficient uh effective and if they're going to perform well on huge sets of data okay before we get into the query plan tool the first thing I want to show you how to do is kind of Benchmark how long your circle query is taking um it's really easy to do it's pretty simple but there's so much conflicting weird information out there in the Salesforce stocks about uh what contributes to Apex CPU timeouts and uh whether or not querying for data against the database using a circle query uh actually contributes to the CPU time limit and so I want to show you guys how you can prove exactly how long it's taking to query for your data and um you know explain a handful of more I guess semi-advanced things that you should know about okay so the first thing is if you read the documentation that Salesforce has provided it's extremely confusing as to whether or not a so-called query actually counts against your Apex CPU time limit of 10 seconds per Apex you know code execution transaction right um according to them when they are reaching into the database for your so-called query and retrieving your data that that period of time where they're reaching in and retrieving your data does not count against your Apex CPU time limit but that's a little misleading because reaching in and grabbing the data is just one step here as you can see when you're using a so-called Korean Apex you're always going to be assigning it typically to a list or a map and to do that it requires them to take that data it retrieved from the database and wrap it into a a representation of an account for your Apex code to be able to use and that takes a good amount of time and that does add to your CPU time limit and this is one of the most time intensive operations that your code is going to run into unless you just have really uh you know poorly written code in general so um if we take a look at this there is a really easy way to kind of Benchmark how long your queries are taking in actual seconds in your Dev console this is not so easy in Ides unfortunately but the dev console still has this really cool thing um that allows you to figure this out pretty quick and actually visualize it but we'll also set some debug logs too to make it even easier so I'm just going to drop um couple things here to make this easier to to look at we're going to just take a look at this actually let me leave this in here so that you guys can look at this code in a repo after I create this video and instead we'll create another method down here and we'll call it um I don't know Quarry benchmarking example or something and uh what we'll do is we'll create a list of accounts again I'm just going to grab the same old setup that we got actually no I won't I've got a different idea here so we've got account list is equal to um we're just going to first query for all of our accounts select ID name from account and then we'll just Loop over our accounts just to see how long each of these operations take right so uh we'll say list account list and all we're going to do is just say account.name is equal to Bob or something and um let's take a look at how long each of these operations takes so we're going to make a system debug and take a look at the limits class and say get Apex oops get uh CPU time so we'll start this with CPU time one and then we will do another CPU time 2 after a week query and we'll do another after this for Loop here boy time three so we can see about how long this is going to take us to execute and uh oh I forgot plus signs those are important so once this saves I will go back to debug execute Anonymous window and I'll call this method instead or a benchmarking example and we are going to oops just hit execute and once this executes we're going to be able to see immediately right okay originally we were at five milliseconds and now after that query we're at 32 milliseconds and now after that Loop where we looped through 2458 rows we're at 78 milliseconds and uh you can see you know that so-called query took a decent amount of time you know it took 20 what 27 milliseconds to finish its operation um which is not a small amount um you know when things start to add up anyway um but you can you can also see that if we said like where um annual or actually let's say where name is equal to Taco for instance we saved that if we ran this uh one more time we'll see that that should take considerably small considerably smaller amount of time right so we went from uh what 27 milliseconds down to just uh eight milliseconds so pretty quick uh in comparison so you can see that you know the size of the data set that you're querying does have a pretty significant impact on the time it takes uh for your code to execute and you can see that in even more detail if you go to your log so you have to open up one of your logs first and you can go to debug and you can either click this view log panels or you can do switch perspective to do analysis I'm going to do a view log panels though and just get the uh I can't remember it's execution overview yeah there we go and see this execution overview and what this will allow me to do is take a look at the time it took for each operation and so this is just you know calling the method that time frame this is the actual grabbing data from the database and giving it to our list time frame and this is uh iterating over our for Loop to uh of data right so if if you click on each of those it shows you in your code um in your debug log exactly what's you know causing that little chunk of time there in that block and you can see that's from the so-called statement um if I clicked on this you could see that uh this is line 21 I believe is where the loop starts yeah basically right after the uh CPU time so um yeah it's a pretty cool little log analyzer and allow you to kind of break down uh exactly how long your so-called queries are taking in comparison to the rest of your code so pretty cool to know about so the next thing that we should go over is What's called the query plan tool and most of you out there even if you're Advanced developers or intermediate developers have maybe never even heard of this query plan tool thing and uh that's okay that just means that you've uh never got yourself into a selectivity uh limit in uh Salesforce which I've got to tell you I've run into twice and they are some of the most horrifying things that you can do get yourself into because basically what happens is your uh table your object in Salesforce is grows so big as in it it is now storing you know millions of Records in it or hundreds of thousands of Records in it and you or someone else in the past has written a so-called query that is not selective enough and what that ends up uh resulting in is all of your queries that are not selective enough after that table grows to a certain amount of Records just outright fail and it's a nightmare to get yourself into so I'm here to tell you how to prevent it um and uh you know if you're in that situation right now I'm here to tell you how to get out of it um and the best way to get out of it is by using the query plan tool at least in my opinion because it's going to help you figure out how to make your query much more selective um and tell you what's wrong with your query in the first place so to figure out how to use the to to find out how to use a query plan tool or just to use it in general first we have to go over to the query editor and more than likely this query plan button down here in the bottom is not there for you and if it's not then you'll go up here to help click on preferences and turn this enable query plan to true and once that's to true what we can do is write a query like so and then hit the query plan button and the query plan button is going to pull up uh this kind of table of information and it's going to list all of the different options that uh Salesforce has based on your query to um you know query for your Fields right right now the only way that Salesforce can query for your data is using a table scan which is basically the last thing that you want to see in this leading operation type field because this is the worst situation you can get yourself into so let me break everything down a little bit for you here because this is a little bit confusing first things first if you know nothing about um you know database indexing or you know indexing in general um I would love to go over it but it's a little bit of a complex concept that would only serve to confuse you even more in this video what you need to know is indexing makes your queries go super fast and using an indexed field can help you create really fast queries if you want to know more about indexing I'll put some stuff in the description some links to some materials that will tell you more about the inner workings of indexing and all there is to know about it but basically what you're going to want to do is find a field over here in Salesforce that's indexed and uh you know hopefully be able to filter your queries using where Clauses with one of these index fields and we'll go over that more in just a second too so let's come back over here and go over this query plan tool first though so so there's a bunch of stuff here the first thing is cardinality cardinality and S object cardinality to my knowledge are basically the same thing and what they are are the approximate amount of Records in the table that you are acquiring so for me that's 2458 records on the account object table or you know in the account object the cost here is the cost of your query uh using this um you know query operation type called table scan what you want to see is a query cost below one if this is higher than one your query is going to fail once you hit selectivity limits so if you're putting in a query and this cost is above one you've got a problem um so let's see uh you know right now I have no where Clause here um and we're resulting in a table scan which basically that just means that I am scanning all of the records in the entirety of my table to get a result let's see um you know if we can use and get this leading operation type to be index or other and uh let me just bring over really quick the cheat sheet here uh that leading operation type can be one of four things it can be indexed like we've been talking about it will use an indexed field to query the object sharing which is maybe your third best option typically uh which will use an index based on sharing rules to determine or or to speed up your query basically table scan which is your worst option which will result in you scanning all the records for your query object and then other which is basically the same as indexed and I I think but we're using internal fields that are indexed by Salesforce or standard Fields typically that have been indexed by Salesforce is when you'll see other pop-up so how do we figure out what fields are indexed we kind of went over that but if you go over to the object manager and you start looking at your Fields you'll see this indexed indicator over here in a checkbox check mark if there's a check mark next to your field then it is indexed and what that means is if you use it to filter on you're going to typically have a pretty fast query so we come back over here to our query and we maybe use the name field to uh filter our Aquarion you can see that the name field was indexed right then we should see a much faster time or cost and you can see that that cost is crazy fast right it's .004 as compared to 2.8 something and you can see that the leading operation type is other and I'm pretty sure it's other because this is a standard field that Salesforce provides that is indexed um and you can see that the field that it's using for that index is the name field right and then it's going to show you its other options for potentially querying for records uh like it you can still see that there's an operation type of table scan that takes much longer than this one but this one that shows up at the top here in the query plan tool is the one that Salesforce is just going to automatically select in the background when it executes your circle query and so you want this top one to be under a value of one which this one is which is great and you're always going to see this ridiculous note down here it never goes away um doesn't matter what object you're on so just know this always shows up and um don't worry about it uh okay so that's basically how you're going to use the query plan tool to figure out how to create more selective queries to get you faster run times on your queries and to make sure that they abide by all selectivity based limits in Salesforce now the next couple things we're just gonna briefly go over which are um actually before we go over them I should I should go over one other thing I'm gonna put this cheat sheet in uh the description of the video so you have it too when you're using this query plan tool and when you're building your queries there is basically nothing I've found that is more useful than this query and search optimization cheat sheet and basically this tells you all about index selectivity thresholds and um it tells you about uh you know the limits around those index selectivity thresholds and it also tells you index selectivity exceptions where you know if you use certain filters with certain conditions you can essentially even if you use an index field result in it uh a query not being indexed so this is extremely extremely useful and I'll have this in the description you can check it out and I would suggest Consulting it uh when you're building your queries if you are even remotely concerned that they may not be selective enough okay with that said now we're going to go over uh briefly some two uh really really Advanced uh areas of so-called two that you will probably not see yourself get into very much but we're gonna go over uh so cool for big objects and then we're going to go over just briefly async Soquel and what it is how you can use it and when you can use it all right so big objects big objects big objects um let's go over the uh many limitations of doing so-called queries on big objects if uh you never had to use a big object I guess you can kind of consider yourself lucky uh they're quite a bit more difficult to deal with although they have their benefits and they're kind of useful if you're going to be archiving data but um big objects uh you know they they have an enormous amount of limitations with Soquel and I just wanted you to be aware of them so you can run so-called queries on big objects um but there's a bunch of things that you cannot do uh like you can't use multiple in Clauses uh which I think this goes over um somewhere down here yeah you can't use multiple name n Clauses um you also can't do a whole bunch of other things uh you you can't use the not equals like not in excludes includes uh or aggregate functions um so you can't use you know group group buy or having or anything like that um and uh you don't want to retrieve the ID field in a big object query so there's just some uniqueness around big objects uh that make them difficult to query at times and it's really important to know you know if you are working with big objects everything I've talked to you about uh is not always true um for those of you that are looking at this and thinking uh what's a big object don't worry about it then you know you're not working with them but if you ever do just know it big objects are a so-called is hampered quite a bit when you use a big objects that said um when you're using big objects as it states right here on the last line if you want to perform these operations not allowed with Soquel then you can use async SQL instead which leads us to async Soquel and um async silk will basically allows you to to query for quite a bit more data than normal so it allows you to query for up to 1 million records um but there are some downsides to async SQL this is pretty cool if you have a really unique case where you can wait for your query data to get returned to you and you don't need it I'll return to you in real time there are certainly situations in which that can be useful but the downside is number one there is no way to just outright do this in Apex you actually have to integrate with uh the uh async uh Soquel uh rest API to get this done you can't just write in inline so-called statement like uh you can with regular Circle um but this can be useful if you're using big objects and it can be useful if you need to query for enormous sets of data uh for one reason or another so when you get into really big orgs or into orgs that use a lot of um big objects just know async so cool could potentially be your friend but you do actually have to write the integration yourself uh to my knowledge at this point in time uh you can't just in line async so cool so uh pretty cool pretty useful in certain situations not necessarily in Apex uh you could get yourself into Heap size limits uh or other problems if you're acquiring for that much data but it's good to know it exists because you have another option here to grab gigantic chunks of data if you'd like to um I'm not going to go over these options too much more but I just did want you to know they existed and primarily I wanted you to know uh about both of these things for big objects because you know a lot of us out there will get into the big object game at one point in time and these are useful if you're using big objects all right I think that wraps up uh so cool now there are things I didn't go over and so cool that you can certainly still take a look at like location based so-called queries where you can actually figure out the distance between um uh you know One account or the other as long as they have addresses filled out in latitude longitude filled out which you can automatically fill out uh and there's a handful of other things in here that you can do with so-called queries that are uh rare you know situations that you'll get into but I think we've covered the vast majority of so-called what you can do with it how you use it in Apex how you can kind of Benchmark your Soquel and things you should be aware of if you're using circle with big objects so now that all of the soakful fun is done we're still not done now we've got to go over social all right so we have finished up with learning about Soquel but we have yet to learn about social the other query language in Salesforce um social is probably not going to take us anywhere near as long to learn as Soquel uh mostly because a lot of the things translate to social but social is quite a bit different there's a handful of things you need to know about it um so you can actually write the queries in the first place because they are different structures than so cool uh that said let's first figure out what social is so as it states right here in front of us social is a Salesforce object search language and you're going to use social in search base uh situations for instance I've used it for custom uh search search bars and things along those lines any any time that you want to do a query across multiple fields on multiple objects for a string of text this is your best best path forward social allows you to do some pretty cool stuff with uh searching and um you know search based situations in your development are primarily when you're actually going to use it so um it is rare I would say that you're gonna run into situations in which you will want to use social it is not a super commonly used uh language but it is extremely useful in those search searching based situations and those customization situations for searches that you can get yourself into so it is very useful to have in your toolbox when uh you start doing that because SQL is not so great uh searching across multiple text Fields um it's it's just it just simply isn't and it it doesn't really do a good job when you want to search for a particular string across a lot of fields on potentially a lot of objects and social is really good at that now the downside of social is that it can only return a maximum of 2 000 records which is not a lot of Records especially in large orgs um so you can hit that limit really fast uh you know and unlike so cool which can return up to 50 000 Records 2000 is way lower right um okay so that's that's kind of the basics of social uh when you would want to use it over soak well it's you're gonna you're gonna see it sparsely used in the Salesforce world but you will see it from time to time and you should consider using it from time to time in a very specific situations so let's go over how to actually build a social query together in the Quarry Builder a good Old Quarry Builder it's that has been our life for the last um however many hours it's been now all right so I've got um the query Builder open and what we're going to do is just build a really simple social query together and yes the query editor does support both Circle and social as it kind of says right here um so let's create a query together all right so the first thing that we're going to do is write find because that's how you're going to start all of your Social queries you're going to start it with the keyword or the Clause find and then you're going to put curly braces and inside of these curly braces you are going to put your search term or Search terms so for instance maybe I want to find the search term Taco which has been like half this video at this point and uh you want to find this in the name fields uh and you want uh sorry let me let me explain this a little bit so what we're doing is we're trying to find this keyword Taco so the string taco in any of the name fields and this is where it gets a little return uh a little confusing and it says returning so basically what we're saying is we want to search the name fields on the account object and we want uh when we search on that account object we want the name field to be returned to us uh the the data in the name field to be returned to us so let's kind of read this query together it's very confusing and you can see it's in a different format than uh Soquel was so okay what we're saying is we would like to find the string taco in any name field on the account object and when and if we find a um string of Taco on the account object and one on one of its records we would like the records to return the data in the name field to display to us so if we if we hit execute on this now what you're going to see is search results in a tab and you can see that any uh account that had Taco in its name field was returned so we've got extra taco taco and Taco Bell the three accounts we've been working with quite a bit throughout this entire tutorial um but uh a cool thing about this is we can actually query on multiple objects so maybe we wanted to also check the contact object um like so then we can do that and now what we're saying is we'd like to find the string Taco in any of the name fields uh on the account object or the contact object and on the account object I would like to return data in a list with the name field and on the contact object I would like to have the data returned to me for first name so if I had to execute on this you can now see the two tabs pop up right one for the account object and we've got the same you know Taco accounts as before and then one for the contact object where we pulled back the first name which was batchable I'm guessing the last name was probably Taco uh or one of the name fields on contact had Taco in it uh we can actually just take a look at name C and yeah the last it looks like the last name on that contact had Taco in it so you can see that this find mechanism actually kind of works like the like um the like operator in um so cool right it's kind of a wild card term it's not an exact match right uh it the the account name didn't have to just be Taco it just had to have the word Taco in it right and same thing for contact the whole name didn't have to be Taco it just had to have the term Taco in it um which is uh pretty cool now before we get into all the other nuances of uh social the thing that I want to go over and kind of highlight the most is this in Clause here in name fields uh and uh let me bring up the you know kind of guide that they have for you here about in Clauses Let me refresh this it's kind of broken there we go so for this in Clause you only have a few options available for you which is a little frustrating uh in in my opinion but you know it's still workable it's still useful either way and so basically you unlike in so cool where you say you know you wanna take a look at particular fields and you list them out right instead in Social what you're going to say is you want to search for this term in a predefined group of fields and those predefined group of fields can either be all fields which allows you to search on all searchable fields for that um well for social queries you can search on all email fields so that's just all fields that are of type email all name fields and all the name fields are listed here I'm not going to go over all of them but you can see that there's quite a number of name fields across the different objects uh phone Fields where you can only see where you'll only search against phone number fields and sidebar fields and this sidebar Fields is what I want to talk about because it's confusing uh and uh doesn't really make a lot of sense I'm not sure why they're called sidebar Fields basically these are any indexed fields on your object so any any fields that are indexed are what are going to end up in these sidebar fields and we talked about earlier how you can see indexed Fields if you go to your fields in uh object manager any of the ones with this check box here in the index column are indexed fields so it's important to note that you can only pick from one of those five search groups you can't deviate from that really um yeah so and it's still very useful it's just you know you're limited in a different way so if I wanted to search on all Fields here I would just type in all Fields hit execute you can see we got one new account in here because one New Field um actually had Taco written to it I'm not sure which field it is but uh it had Taco written to it so it's pretty cool um all right so the other things that we should go over are all the same kind of things that we went over with Soquel um like limits and offsets and order by and all that kind of stuff and we'll also go over some more nuances for this uh returning Clause here and the find clause and what you can place inside of these curly brackets but first let's go over the easy stuff so we can get it out of the way so in this returning clause you can see that we are returning results for the account object in the contact object and we're asking for the name field value for both of them but we could also ask for any other fields just like we did in a so-called query we could say you know ID field name field annual revenue field and we can also say things like where uh name is equal to Taco like so and you can put a limit of 100 on it and you can offset it by 100 so you can see inside of this kind of parentheses here that we have that uh this this kind of works like a so-called query aside from the fact that you don't have to say select in the beginning of it and you don't have to say from the account object right because we already know in this instance that we're acquiring on the account object so this works this this kind of query inside of this uh uh parentheses here for each object that you end up searching on works very similar to a so-called query aside from the fact that you're never going to put a from and aside from the fact that you're not going to put a select at the beginning you'll still be able to use where Clauses to start limiting what gets returned in here you can use limits you can use offsets and you can use order Buys so you know if you wanted to order by name you could do that right now if we hit execute um I think I need to put order by name before these ones second I need to put order by name over here not before or not after my limit in my offset ah and that's important to note I can't use offset in a multi-object um uh search so that's what it's telling me here is I.E can't um use offset when you're you you're searching across multiple entities or multiple objects so now if I run this see how I see how often I I use social I haven't I haven't remembered or memorized all of the uh the uh limits there anyway so now you can see this kind of uh you know we added data that we got returned for us and um we stripped out some data that we don't care about with that where Clause there to filter out our data so pretty cool right these you know essentially act as queries inside of each of these uh objects that you're having or you're wanting to have results returned from which allows you to further kind of narrow down your results for each one of these objects and it's just as important to narrow these down to the best of your ability and social as it is and so cool so if you can narrow them down definitely do the next thing we should go over is this fine clause and the many things that you can do with this right because uh there there's a lot that you can do with it and it's uh an interesting Beast I'd say so let's go over it um if you take a look at the docs what you can do are well quite frankly a lot of things so in this find Clause where we put in our search query um or a search term we can do a bunch of things we can use wild cards kind of like we did with the uh like uh operator and uh so cool but the wild cards are different characters instead of a percent sign and an underscore instead for the percent sign we have an asterisk and for uh the underscore we have a question mark so for instance if I came back over here and put a star at the end of taco and we executed this you'd see a handful of new things came up because uh maybe I I don't know maybe there were some other things that it didn't match on that had the word Taco in it and now those are popping up uh or maybe I use the question mark and the question mark is basically just a wild card for a single character and if I hit execute on this you can see that it pulled up four results here but it didn't pull up any results on the contact all of a sudden it went away and I've noticed uh over time that when you use this particular Wild Card along with a whole bunch of other things that we're going to go over here in just a second that it gets more specific so for instance before it matched Taco on Taco's basically those are the three contacts we got and we got them back because of the word tacos in the name that goes um but when we add this single uh wild card here the Single Character wild card it started matching on exactly the words or the letters that we have here t T you know a wild card of any character and then C O and if we have the Z on the end then it doesn't match anymore so that's why all of a sudden we're not getting the contact matches is because the you know behind the scenes search engine is saying okay now we've got to match on exactly these four characters not these four characters plus a z at the end and you'll notice that same thing you can set that up and if you put this word in quotation marks you'll have that same outcome where you only get four accounts again and you don't get that uh tacos set up that you had before additionally with um this find Clause you can do things like taco or um batch able and you can kind of chain these right and you could also do Taco uh or batchable and tacos or something like that right you don't actually have to put quotes around these that's not necessary you can if you'd like to we'll go over what the the quotes do in just a minute um but I will say let's let's just drop a couple things here if we have taco or batch based on our original run of just taco you'd think that we would get back you know four accounts and three contacts but what you're gonna see here is when I say taco or batch like so which means that our string Fields could have the word Taco oops the word taco or the word batch in them what you're gonna see is all of a sudden those three contacts disappear again and that is because for some reason behind the scenes it's now matching exactly on these four characters and exactly on these five characters so you have to have this exact word in it now instead of kind of that um you know generic match that we had before so as soon as you deviate and use more than one term uh basically it starts matching exactly whereas if you just use one term it kind of works uh and in a bit of a different way so if I change this to say taco or tacos then all of a sudden we've got our contacts back that we had before right um you can also like I said before you use the keyboard and and what this would do is it would only return results where Taco and Tacos were present which are none of them so for uh our purposes nothing got returned you can also do uh nested statements so you could say tacos or sorry taco or tacos and um matchable like so and then we get back our batchable tacos and our four tacos over here man I think I said tacos about a million times in this um okay so it's really important to know that you can do these things you can match on multiple terms but as soon as you deviate from that first uh search term and you start getting more complicated in here the matching system behind the scenes kind of shakes things up a little bit I guess and changes things up now um uh sorry so when you use quotation marks uh like so this is really beneficial when you have kind of like spaces in between your words and you want to match on specific uh words so for instance if I wanted this to match on the term Taco Bell then you'd put it in quotes So that you can match on the term Taco Bell and then we would only get Taco Bell back right for our account here which is uh cool right so anytime you have spaces in your search terms and you want to make sure that uh you know you find items in that order you know taco and then Bell then make sure to wrap them in quotes otherwise it's not really necessary to wrap them in quotes you could it's totally up to you but it's not actually required the only other thing that you can do within this uh find um Clause here is that you can also say and not so it has the term tacos and it does not have the term batchable basically so if we ran that we're going to end up with a bunch of accounts and uh we're not going to end up with any contacts because the contacts all had tacos and batchable in them uh okay so we've gone over our find Clause we've gone over our uh this in Clause all the fields that we can do and we've gone over returning and what you can do with returning now there's a handful of other uh kind of interesting useful uh things that you can do in Social that I'd just like to briefly go over uh the first one is this two label if you use translation workbench at all um this is super useful it will allow you to have um your results returned translated or the sorry the data returned you translated so um you know if you use translation workbench this can come in a lot of Handy and definitely check that out another thing that you can do is using list view which is actually pretty cool and can be useful in some situations so if you wanted for instance the data for your account object or your contact object to only come from a particular list view you can denote that by using this using uh uh clause in your um you know inside of your parentheses for your specific object uh query like we just took a look at a little bit ago and uh the other one that is pretty cool is spell correction um so you can by default spell correction is actually false or sorry turned on so if you wanted to match on something and it finds out that it's spelled wrong it's still going to go out there and say all right well this was probably spelled wrong so we should match on it you can turn this off too if you wanted to so that it did not match on spell correction and that's pretty cool as well now there's a handful of other things you can see that you can do with social uh like highlight social terms when they come out of your uh search results uh include metadata with your social terms do things around networks which are um your experience Cloud sites among other things but you'll find that you won't do those very often and um so we're not going to go over absolutely everything that there is to know about social uh because quite frankly what you've just seen is what you're going to use 99 of the time and um yeah the next thing that we need to do now that we know how to structure a social query how it works how we can you know query over multiple objects is we need to figure out how we can actually use this in Apex right because as you could imagine using this in Apex is quite a bit different than uh so cool query so let's go check that out all right so we're back over here in the dev console and we are just going to uh write some code together now that we've figured out how to kind of structure social let's figure out how we use it in code so I've just created this class called Apex social example to help speed things up a little bit and I'm going to create a new public static void um method that I'm going to call I don't know social example because why not and um what we're going to do in here is actually make a social call so we're going to say a list of a list of s objects and uh you might be looking at this and saying what and I totally understand that so let me explain it before I even add the query in here when we query for using social we don't have one specific object returned to us instead we have a list of a list of objects so we're getting accounts returned and we're getting contacts returned and we're getting a whole bunch of records for both so what we need to capture social results is a list of a list of s objects um because we're capturing you know multiple object results with social as we saw with those multiple tabs uh when we ran this query earlier um this one earlier we can see that we have multiple tabs one for each object and that's how you would represent that here in the code so if we write a query together we'll just say find uh taco and here in these social queries for Apex you are gonna have to wrap your search term in single quotes like so um and oops in in all fields returning [Music] count ID and name I'm going to uh drop this down so it's easier to read and contact ID and name like so and hopefully I have not forgotten anything totally possible let's save it and see what happens okay so we're all good now we've got our list of uh list of s objects and we've got our SQL or sorry our social query assigned to it and basically what this is going to return let's just uh you know what let's just run it and see what it returns so sorry results like so if we go to debug and open execute Anonymous window we can do Apex social example and then call our social example method and execute it if we execute it and we come over here to the logs we can see that we output a list of accounts and a list of contacts which is cool so the question would uh next be how do we actually iterate over this uh which you know would be a great question to ask so um basically what we're going to end up doing is we need a for Loop and inside of our for Loop we are going to say um you know list s object object list social results and then within here we're going to say four um uh s object object object list like so and then we would uh output our object like so um so make sure I didn't do anything crazy here and I didn't so that's good I always love it in Dev console when they tell you you've got problems and you really don't and so we've saved this and now if we uh run our execute Anonymous again we should be able to see our individual records that get output which we can right extra Taco Taco Bell new account batchable tacos batchable tacos batchable tacos taco taco going crazy at this point um okay so um that's kind of how you get your list of results and how you um output or iterate over that list of results um the other thing that we should go over is just like in Soquel there is dynamic social uh and we can build it in a very similar way but we call a different method to uh use Dynamic social so let's check that out so basically just like with so cool we're gonna create a string that is named we'll say we'll call it query string and then um we'll just put the exact same query in here make our lives easier we're gonna have to escape these characters in here single quotes and I need to put this string all on the same line okay so now we've got our Dynamic social that is in string form and now what we need to do is actually query for it right and so the way that we query for it is we are going to of course declare a list of a list of s objects again but then we're also going to say search dot query and we will send in our query string like so and if I grab this for Loop will be able to see that this results in the same outcome as before so let's open our execute Anonymous window and call the Dynamic social example uh I spelled it wrong yeah I definitely spelled it wrong and uh yeah so you can see we got the same results as before but we just did it through Dynamic so or social instead of um that inline social and this has the same benefits as Dynamic so cool and that you could build this and you know and and have it be fed from Uh custom metadata or I don't know whatever else right uh there's a bunch of ways that you could use Dynamic social to really give you some uh really flexible code right instead of having this kind of inline social that we see here so um let's see I think I hope actually I'm wrong there's one last thing that I need to go over with you guys that is important uh that I haven't gone over for either so-called or social and it's it's the last thing and then we're done I hope and that is uh what's called social and Soquel injection and it's really important to uh you know not not take this lightly um and and make sure that you always protect yourself against it so in either SQL or social you can use bind variables I didn't show you that in Social but you can use bind variables for social too so for instance we have you know if we wanted string search term like so is equal to Taco and then I could use a bind variable within social as well and you can also do that in Dynamic social just like in Dynamic SQL but what happens very often is that you are asking for and some custom UI you've built um for the user to fill out some information and then the user fills out some information and what you end up doing is using those bind variables uh and uh in in a bunch of uh dangerous ways basically so the user fills out all those fields they send them into your Apex controller and in your Apex controller you are using those things that they filled out in their form inside of your queries and if you're not careful a malicious user if you set if you set things up that way can start to attempt to query for more than then they should be able to query for right maybe some new fields that they weren't supposed to be querying for or whatever else and so to protect yourself against malicious user input when you're using these bind variables in your uh so cool and social queries what you need to make sure to do is when when these are coming in from outside sources like this this I'm declaring this variable right here so it's not really a big deal but if it was coming in from an outside Source say I was having it passed in and I had a string search term passed in right like so now what I really want to do is create a string uh of escaped search term and set it equal to string dot Escape single quotes and put in my search term here like so and then essentially what I've done is now I can use this search term in here and I don't have any fear of this external user who's sending me in the search term uh trying to be malicious and search on more terms than they should or search in uh ways that I'm not anticipating them to search on and so this just protects you a lot so anytime you're using these bind variables and they're coming in from a user input make sure that you are using string.escape single quotes to protect your query from malicious user input it's one of the most important things that I see overlooked all the time and I almost just overlooked it in my own video so make sure to always Escape single quote your Search terms when they're used as buying variables in your queries okay all right everybody and with that hope I've covered everything I have no idea how long this video is but it feels like the longest video I've ever made and uh yeah anyway you guys got any questions leave them in the comments below I hope this has been helpful and I will see you in whatever the next video is till next time guys [Music]