LIKE, OFFSET, & ORDER BY

[00:00:00]
>> Brian Holt: Let's talk about like so let's say you didn't remember if it's Postal Service or The Postal Service, all right? Very probably common use case there. But what if I want to find their artist ID, and I'm not sure what it's called, you can do SELECT ArtistID from Artist WHERE name and then you can do this thing called like.

[00:00:30]
And then you basically say, where you can have more characters here so you put a percent sign here and then do Postal Service. And you're basically saying, hey, look for something that has postal service that is affixed by Postal Service, and it might have stuff at the beginning and it might not, all right?

[00:00:51]
And if you do that, you should get 174 because the Postal Service would match this. Also, Postal Service would match it. Definitely not the Postal Service would match it, all right? Anything that's fixed by that would return true there, so that it means zero to many. One thing that anyone coming from another one, there's I like in other versions of other databases that I like does not exist inside of a SQLite.

[00:01:26]
It only handles LIKE, and it's already case insensitive. That's what the idea. The I means insensitive. So yeah, you just have LIKE in SQLite. So let's do another one. Let's do SELECT name FROM Artist, WHERE name LIKE, and I wanna find all of the artists that have orchestra in their name anywhere.

[00:01:52]
So you'll just put it on both sides. Orchestra, and you'll put it on both sides. This means orchestra exist somewhere inside of this particular string, and you'll find that there's quite a bit of them in this database, all right? Orchestra, orchestra, orchestra, orchestra. If you got rid of the last one, you'd only get it where orchestra was at the end.

[00:02:19]
Notice that these all end in orchestra. And if you did it just at the end you'll get anything that begins with orchestra, right, which is just one. Pretty cool. Something that they use from time time. This is useful for queries like this, where you're looking as like, does this string is this string contained in another string.

[00:02:42]
If you are looking for something like full text search, something that like elastic search would do for you, we'll talk about that towards the end of the course. That's also built into SQL as well. This is much more simple. This is just looking for the, does this string contain this?

[00:02:59]
We'll talk about full text search later. Let's do one here just to show you that there are other comparators other than just equals. Artistid is less than or equal to 10.
>> Brian Holt: From, I need from, that's what I did there.
>> Brian Holt: So you can see here, I got everything up into including 10.

[00:03:27]
I can get rid of the equals too. And I won't get the inclusive as well, all right? All that stuff exists as well. If you haven't seen what are these called ligatures before, you can see that when I put the two glyphs next to each other, they combine into one.

[00:03:44]
I have a note at the beginning of the course where I'm talking about all the stuff that I used how to enable that, but that's just why that's combining, is it's just those two things put together. I always forget that I have that on.
>> Brian Holt: Cool, let's talk about pagination, that's something else you have to frequently do.

[00:04:07]
Select star from artists, I run this a lot because I don't want 300 artists every single time that I query, so I will LIMIT 5 and I'll grab only 5 results and it will just give you the first five in the set, and then it'll finish, all right?

[00:04:22]
Obviously, you can do 15 or whatever you wanna do. And then if you wanna paginate through them, you can also offset. So notice that this first one that I did was 1 through 5, and then this one now is 6 through 10. 11 through 15, so on and so forth.

[00:04:44]
I will say this is not best practice if you're writing an app to paginate through something, because what happens if I add something in. I mean, these are sequential, but imagine if I had something in the middle, there could be problematic, because everything would shift and would look a little weird.

[00:04:59]
You like get repeat results or you would skip results. So basically what you'll do is you'll look at the last one and you'll basically just say rather than OFFSET 10, you'll give it a WHERE clause. WHERE ArtistId is greater than 16 or something like that.
>> Brian Holt: Or greater than or equal to rather.

[00:05:27]
No, wrong side.
>> Brian Holt: Okay, same thing, all right? But now you're basing it on the last ID that you saw, all right? That's a little bit more secure assuming these things are sequential wherever you can do that, to base it more on the data, rather than just an offset, will help you and get better pagination results.

[00:05:53]
>> Brian Holt: Okay, and ORDER BY. Let's do ORDER BY. SELECT * FROM Artist. And then you say ORDER BY.
>> Brian Holt: Name, LIMIT 5.
>> Brian Holt: What did I forget here?
>> Speaker 1: You got ordery.
>> Brian Holt: Ordery.
>> Brian Holt: So now notice that these aren't in the order of their IDs anymore, they actually are ordered by the names themselves.

[00:06:33]
And then you can say ascending, which as you might imagine is what it's already doing. And as you probably have guessed, descending will give you everything in reverse, which can be helpful as well.