Laravel Database: Query Builder check not null

This is an article where the focus is how to detect whether or not a query executed via DB Facade class which in the end will be retrieving a collection has a null column or not. DB Facade class has a method used for defining or building a query. The DB Facade class mentioned in this article is part of Laravel framework which is normally exist to be utilized further especially building up a query to retrieve certain data or information from a certain table. It is normally started with a method named ‘table’ specifying the table which is going to be used for querying further. It is shown as follow :

$users = DB::table('table_name')->get();

The get method defined in the end has a special purpose for returning an Illuminate\Support\Collection as already discussed previously which is containing the results where each result is an instance of the PHP StdClass object. But the most important thing is how to detect whether there is a null value in one of its field ?, it is an important task especially if the returned value which is formed in the shape of a collection will be rendered for further display in the page.

It is quite an easy task since deciding whether a single record of collection retrieved back from executing the get method depends on the id field of primary key column on the table. So, as long as that primary key field or column can be checked whether it is null or not null it will avoid printing or sending the collection of data for further printing in the blade view template file of Laravel web-based application.

So, this is how we modify the query definition on DB Facade class to retrieve the collection of data with a filter for checking whether or not the primary key field or column is null or not.

$selected_db = DB::table('app')
->leftjoin('app_db', 'app.id_app', '=', 'app_db.id_app')
->leftjoin('db', 'db.id_db', '=', 'app_db.id_db')
->where('app.id_app', $id_app)
->whereNotNull('db.id_db')
->select('db.id_db', 'db.name as name', 'db.version')
->get();

The above snippet code is a query definition for executing a query on table named ‘app’. The query is joining table named ‘app’ with another table named ‘db’. And it take several field or column named ‘id_db’, ‘name’ and ‘version’ from table named ‘db’. This is just a simple task performed by querying list of database available for a certain application which has a certain id defined by a field named ‘id_app’.

The most important factor is shown in this line :

->whereNotNull('db.id_db')

I want to specifically focus on the query, if there is no record at all and it means the columns retrieved back will have null value including the deciding field or column named ‘id_db’, I don’t want to get it as part of the result. It is clearly stated in the clause of ‘whereNotNull’ and DB Facade class as the query builder will never let any null value possessed by the field or column named ‘id_db’ extracted from the table.

To check it further, we can use the following snippet code :

if($selected_db->isEmpty())

The above snippet code is checking the variable named ‘$selected_db’ if it is empty or not. It can be used further whether or not to pass a certain variable named ‘$selected_db’ to be rendered in the blade view template file.