You know, more often than not, the indexes for your resources will be more than enough. You can use filtering and search in order to get the data you need. But there are times when it just won't cut it to have a single index for a resource, because the columns you need to look at simply aren't there on the standard index. Let me give you an example. You've seen the book index now more times than I care to count in this series, and it's straightforward. We see the cover, the title, the author, how many copies the library owns, whether or not it's featured, and some of the metadata. But there are scenarios where the columns defined here just aren't enough.

it's featured, and some of the metadata. But there are scenarios where the columns defined here just aren't enough. For example, someone comes into the library and they say, hey, I'd like to borrow the book 1984. We like 1984, don't we? Well, yeah, you can type 1984 and see it appear in front of you, but this is not enough information to tell them whether you have a copy available, because copies does not take into account people who currently have the book on loan. So we need some form of other view, another index that will inform us whether or not a book actually is in stock, whether somebody is able to loan a copy of it.

So we need some form of other view, another index that will inform us whether or not a Book actually is in stock, whether somebody is able to loan a copy of it. Thankfully, Nova has an amazing concept called lenses, and lenses allow us to completely customize the index when we want further insight into a resource. They can be a little difficult to understand when you first start using them. So I'll tell you what, let's build this example together so that you're comfortable using lenses in your administration panels. It all starts, as most things do, in the terminal where we can run php artisan nova: lens, and then we'll call this one bookStock. Now let's just take 30 seconds to familiarize ourselves with bookStock.

and then we'll call this one BookStock. Now let's just take 30 seconds to familiarize ourselves with BookStock. It has a search property. This will allow us to perform a search on items in our lens, much as we can perform a search on items in an index. We have query. Well, this is where we're going to select the columns that should actually appear inside our lens. We then have fields, so you don't use the standard fields for your resource. You customize them based on the columns that you select in your query.

We then have fields, so you don't use the standard fields for your resource. You customize them based on the columns that you select in your query. And then underneath we have cards and filters and actions, all the things that you would usually find on a resource. So think of a lens like a resource index, but a resource index in which you have complete control over the query and the fields that will be used to display it on the front end. Now let's start by filling out this database query here. Out of the box, Nova is going to wrap your query with two methods. One is with ordering. This will handle any form of sorting you define inside the front end.

One is with ordering. This will handle any form of sorting you define inside the front end. And the other is with filters. You can apply custom filters to lenses just as you can apply them to standard resources. So it's here that we want to actually start attaching columns to our query. What columns do we actually want to select? Well, we could select the ID of the book, the cover of the book, the title of the book, and that will give us the metadata needed in order to be able to understand what we're working with inside the lens index. Let's also select numberOfCopies, which is kind of our baseline for this.

working with inside the lens index. Let's also select numberOfCopies, which is kind of our baseline for this. Once we've done that, we'll want to add a dynamic column in SQL. So I'll use addSelect, which receives an array. I want a column called copiesOnLoan. copiesOnLoan is going to be a closure. Obviously that receives a query that we can join on to. And we'll say query->selectRaw. I want to count all of the columns that are returned here. And I want to select that from the pivot table book_customer.

I want to count all of the columns that are returned here. And I want to select that from the pivot table book_customer. Obviously I'll need to join that correctly. So we can say where column. And I'll say book_customer.book_id is equal to books.id. And we don't want to include any books that have been returned because obviously they're no longer on loan. So I'll add a whereNull check for the returned_at column. I want to add a second dynamic column called copies_in_stock. This is again going to be a closure that accepts a query.

I want to add a second dynamic column called copies in stock. This is again going to be a closure that accepts a query. And we can say query->selectRaw. And we want to take the number of copies that we have of the book in total and minus the number of copies on loan. Now of course the question may come into your head, why on earth are you doing this as SQL? Wouldn't it be easier to implement as a computed field in Nova? Well sure it would be easier code wise, but the problem is it would break Nova's pagination. If we want to be able to sort on these fields or filter by these fields, we have to implement it in SQL so as not to break other parts of Nova.

If we want to be able to sort on these fields or filter by these fields, we have to implement it in SQL so as not to break other parts of Nova. Thankfully Eloquent and Laravel's database layer make it incredibly easy to do even complex SQL queries like this one. So it's not too bad. But before we go any further, it would be good to make sure this SQL query actually executes correctly. So we need to register it with our Nova Book resource. At the bottom of the Book resource is a method called lenses. And much like filters, we just have to instantiate our custom lens.

At the bottom of the book resource is a method called lenses. And much like filters, we just have to instantiate our custom lens. So in this case it will be new BookStock. Now from the front end you'll see we have a new icon here which represents lenses tied to this resource. And I can click this and select BookStock to be taken to this brand new view with our custom SQL query. And because we actually see results here, we know that our query is actually executing and working correctly. We only have one field defined at the moment, ID.

and working correctly. We only have one field defined at the moment, id. So let's go ahead and define some other fields as well. So back in our lens we have the fields method. And here's the id we just saw. Because it's a lens, we don't have to worry about validation rules or anything like that. It's a read only view. So we just have to define the visuals using fields in the fields method. Let's think about the actual fields we have. We obviously have the cover, the title, and numberOfCopies.

Let's think about the actual fields we have. We obviously have the cover, the title, and number of copies. Let's handle those first. cover is going to be an image. So we'll say image, make, cover. title will be text. Again, text, make, title. And we have number of copies which makes sense to be a number, doesn't it? So number, make, number, let's try and spell that correctly, number of copies. And then we have our two generated columns, copies on loan and copies in stock.

So number, make, number, let's try and spell that correctly, number of copies. And then we have our two generated columns, copiesOnLoan and copiesInStock. So let's duplicate this line a couple of times and we'll say copiesOnLoan and of course copiesInStock. Well our lens is really starting to take shape. We have our cover image, we have our title as usual. But then we have our three columns here, the number of copies, the copies that are currently on loan, and the copies in stock. Now there are a few fields here it would be good to sort by. The title and all three of these fields would make sense to be sortable.

Now there are a few fields here it would be good to sort by. The title and all three of these fields would make sense to be sortable. So let's add that. I'll select all of these fields and we'll tag the sortable method onto the end of them. Let's see how that works. Sure enough, I can sort by the title as I might expect. I can sort by number of copies as I might expect. But when I try to sort by copies on loan, I get a SQL error. And you'll see it's attempting down here to actually order by books.copiesonloan. The issue is that copies on loan is not part of the books table.

And you'll see it's attempting down here to actually order by books.copiesOnLoan. The issue is that copiesOnLoan is not part of the books table. It's prefixing the books table because it expects that's where we're selecting columns from. But this is a dynamic column. It doesn't exist. Thankfully we can solve that using a method without table order prefix up here at the top. That will fix our issue. Now if we refresh, you'll see we are able to sort by copiesOnLoan just fine.

That will fix our issue. Now if we refresh, you'll see we are able to sort by copiesOnLoan just fine. We're also able to sort by copiesInStock. So already we have a very useful view that we can use to see if something is in stock, how many copies are currently out, and how many copies we have in total. Of course it would be nice to be able to filter this index down, this lens down, by a search query as well. And lenses absolutely support doing that. At the top here we have our search property as we have in our resources. So let me add the book's title and ID here in order to support searching across those.

At the top here we have our search property as we have in our resources. So let me add the book's title and ID here in order to support searching across those fields. When we refresh we now see a search box at the top and I can search 1984 to simply filter down to that book. So now when someone comes to the library and says, can I have a copy of 1984, we open the lens, we search, we say okay, we have two copies in total, there are zero on loan, and we have two in stock. Yes, you can borrow a copy. Let me present you with another situation so that we can expand further on lenses.

Yes, you can borrow a copy. Let me present you with another situation so that we can expand further on lenses. You want to see which books are currently completely out of stock, they're being loaned, and you have no copies available. That would help us to determine whether we needed to order more of a particular book. And thankfully we can combine filters with lenses, so we're tying together all of our learnings here, in order to provide exactly that functionality. Let's create a filter that will allow us to check just books that are completely out of stock. Once more to the terminal where we'll say php artisan nova:filter, and we'll call this

of stock. Once more to the terminal where we'll say php artisan nova filter, and we'll call this one stockFilter. And in the generated file I'll scroll down to the bottom where we have options as in the previous episode. I'll create in stock and out of stock, and I'm going to use operands here. So I'll say greater than or equal to four in stock, and I'll say less than four out of stock. Now in our apply method we can chain on a where. We want to use that dynamic generated column, which is copiesInStock.

Now in our apply method we can chain on a where. We want to use that dynamic generated column, which is copiesInStock. I'll use the value as the operand, and I'll set 1 as the comparison. So if we're checking for in stock, then we're checking for books where the copiesInStock column is greater than or equal to 1, whereas if we're checking for out of stock, we're checking for books where the copiesInStock column is less than 1. That should be all we need for our filter. Let's go apply it inside our lens. Down at the bottom of our lens we have filters as we have in resources, and I can say new StockFilter.

Down at the bottom of our lens we have filters as we have in resources, and I can say new stockFilter. Let's go ahead and refresh our index, open the filter dropdown. Sure enough, there's our stockFilter. And if we select out of stock, well, we get a SQL error. We get a SQL error because we're doing a where statement on copiesInStock, but copiesInStock is a dynamic column. It doesn't actually exist. So we can't perform that check due to SQL constraints at the same level. We have to use a subselect.

So we can't perform that check due to SQL constraints at the same level. We have to use a subselect. Let's go and update our query. So here on line 36 I'll give ourselves a little room, and we'll say query from sub, and this accepts a closure, which will receive the subquery. The subquery is actually going to be this query here. So I can close those lines off. Because it's a subquery, I need to say which table it's from, so I'll say from books. I'm resetting the query to the top level. And then down here I can close this up, and I need to pass a second parameter, which is

I'm resetting the query to the top level. And then down here I can close this up, and I need to pass a second parameter, which is the alias table name. I'm going to call the alias table name books, and by doing so I will no longer need withoutTableOrderPrefix, because obviously now it thinks we're actually searching on a table called books. A little bit of trickery there, but it works very well and causes no issues. Hopefully now if we refresh, we only see copies that are currently out of stock. And obviously our Cedar has a few issues because we have negative copies, but in real life, well, you just wouldn't be able to loan out a book anymore because you have zero copies.

And obviously our Cedar has a few issues because we have negative copies, but in real life, well, you just wouldn't be able to loan out a book anymore because you have zero copies in stock. You have 10 available in total, and you've got 10 on loan. You haven't got any left. On the other hand, if we only search for books that are in stock, you can see that all our results are positive. Any of these books are up for grabs by people who come into our library. Before we wrap up, currently the only way to access this lens is to go to the books index, click this button, click book stock.

Before we wrap up, currently the only way to access this lens is to go to the books index, click this button, click book stock. But I think this particular lens is so important that it deserves a space on our custom menu. Let's add it. In our NovaServiceProvider, we'll head down to our custom menu here, and perhaps under the Book resource itself, I'll add a new menu item with a lens. We have to pass the resource class, which is obviously Book, and then we pass the lens we're interested in, which in our case is the book stock lens. With that done, we should see it appear inside the menu here. And from anywhere, we can now very quickly access the stock of all our books.

With that done, we should see it appear inside the menu here. And from anywhere, we can now very quickly access the stock of all our books. So in summary, why use a lens? Well, a lens gives you an entirely new perspective on the data in your administration panel. If you have fields that are computationally expensive and you don't need to see them all the time, or if they're only really useful under certain circumstances, then display them inside a lens. Keep them out of your standard resource index. You're not losing anything. A lens can be searched.

You're not losing anything. A lens can be searched. A lens can be sorted on. It has filters. It has actions. It's just as powerful as any other index, but for a very particular use case. And it can make the lives of your administrators a whole lot easier if they have repetitive tasks where they need to see data in a certain format.