Laravel: Select only some columns by default, or override it
Behold! The power of global scopes!
In my Podcast application, the table that holds the sound files come with Closed Captions, which are generated thanks to Google Cloud Platform. When a author submits its podcast, the CC will be saved in a couple of hours in the closed_captions column.
As you may guess, large podcasts will contain large portions of texts in the closed_captions column, which can comprise of more than 95% of the row information. When querying the table, this column will be also selected, so you will have to exclude the columns in each part of your application, even the parts where you don’t remember.
Luckly, there is a way to always select some columns by default, and override the select statement without any problem.
Enter Query Scopes
Query Scopes allow to simplify a query by just calling a single method to constraint the query. We need a solution within this method to select all columns without adding anything to our syntax, so we will use a global query scope instead of a local query scope, since that latter would mean adding the scope method in every part of our code.
Currently the SQL standard doesn’t allow to “select all columns except one”. You can use SQL Views or Temporary Tables, but both means to hook directly into the database, and that indicates a deeper problem in your data. I hope someday we will be able to do something like
SELECT * EXCEPT foo, bar.
We will create a SelectConstraintScope class, and in the apply() method we will put the magic.
Scopes are usually applied just before the Query Builder becomes a SQL statement ready to be executed, so we can see what the developer has built and modify it before the application builds an SQL statement from it.
In this case, the scope will check if we didn’t filter our query by columns, information that falls into the columns property of the Builder. If none was issued, the property will be null or empty, so we just need to apply our list if it is.
Since columns don’t change from one night to another, this seems like a very handy scope to avoid meddling with the table schema or else.
Update: How this works?
Well, let’s say I’m gonna pull out two of the latest Podcasts:
Podcast::latest()->take(2)->get();This will pull out the Podcasts without the closed_captions by default, which is nice. Now let’s say I need the whole damn thing. No problem:
Podcast::latest()->take(2)->select([’*’])->get();That’s fine. Now I need to select only two columns, the filename and the path. Again, just put them:
Podcast::latest()->take(2)->select(['filename', 'path'])->get();And it can work with nested relations too, since it will load the scope unless I specify the columns I want.
// Load the filename and path of Author’s Podcasts
$author->load(’podcast:filename,path’);This code is part of my Laratraits package for Laravel. Give it a chance if you don’t want to spend hours creating something from scratch.
