10.2 Where Clauses
So now we have the methods that we need to retrieve our database results, how can we apply
conditions to these SQL queries? Well in the example above you saw me using the where()
method, let’s take a closer look at this.
1 <?php
2
3 $users = DB::table('users')->where('username', '=', 'dayle')->get();
Here we have the same snippet again, but we are concentrating on the where() part of the chain
:
1 <?php
2
3 where('username', '=', 'dayle')
The great thing about how Laravel handles the where clause is that the chain looks a little bit like
the SQL being generated. In the chain method above we are saying WHERE username = 'dayle'.
The first parameter to the method states the field which we are comparing, the second parameter
states the operator to use for the comparison, and the third parameter is the value which we are
comparing with. We could also use :1 <?php
2
3 where('age', '>', '18')
4 // WHERE age > '18'
5 // Drink up! Not if you are American though, sorry.
What if we want more conditions? Well first we need to decide if we need ‘AND WHERE’ or
‘OR WHERE’. To include an ‘AND’ where clause, simply use the where() method again in the
chain, for example :
1 <?php
2
3 $users = DB::table('users')
4 ->where('username', '=', 'dayle')
5 ->where('sexyness', '>', 5000)
6 ->get();
As you can see in the above example, I have put each chain method on a new line. I find this
is easier to read, and avoids having terribly long lines of code. This chain will perform the
following SQL :
1 SELECT * FROM users WHERE username = 'dayle' AND sexyness > 5000;
If we would prefer to use an OR where condition we simply use the or_where() method, which
accepts the same parameters. For example :
1 <?php
2
3 $users = DB::table('users')
4 ->where('username', '=', 'dayle')
5 ->or_where('face', 'LIKE', '%malemodel%')
6 ->get();
which gives us :
1 SELECT * FROM users WHERE username = 'dayle' OR face LIKE '%malemodel%';
Now I don’t need to explain how every feature of SQL works, there are plenty of other books for
that. I will however name the methods used to accomplish common tasks instead.
Use the where_in(), where_not_in(), or_where_in() and or_where_not_in() methods to
match a field to one of an array of items.
The where_null(), where_not_null(), or_where_null(), and or_where_not_null() methods
to match a field to a NULL value.
Sometimes you will want to nest where clauses together, Laravel provides functionality for this
in the form of ‘Nested Where Clauses’. Let’s take a look at a code example..1 <?php
2
3 $users = DB::table('users')
4 ->where('id', '=', 1)
5 ->or_where(function($query)
6 {
7 $query->where('age', '>', 25);
8 $query->where('votes' '>', 100);
9 })
10 ->get();
By passing a closure containing extra where clauses to another where method, we can create
nested where clauses. The result can be seen in the SQL:
1 SELECT * FROM "users" WHERE "id" = ? OR ("age" > ? AND "votes" > ?)
neat huh?
And now for a more interesting feature! (I told you about how I loathe SQL right?) Dynamic
where clauses give you a really funky way of defining simple where clauses. Check this out..
1 <?php
2
3 where_size(5)->get();
Here we are specifying the field to compare in the method name. Fluent is so clever that it will
deal with it, no problem!
It will even understand AND and OR, check this out!
1 <?php
2
3 where_size_and_height(700, 400)->get();
Expressive, clean, Laravel at its finest.