Laravel Query Builder & Eloquent ORM

 An application always needs to interact with a database and Laravel makes this task hassle free. Few tools that make Laravel an awesome framework is the inclusion of “Query Builder and Eloquent ORM”. Through this blog I intend to share few quick pointers on these concepts.

 Query Builder:

In LaravelLaravel the database query builder provides an easy way to create and run database queries. It can be used to perform all the database operations in your application, from basic DB Connection, CRUD, Aggregates etc and it works on all supported database systems like a champ.

 The notable factor about query builder is that, since it uses the PHP Data Objects (PDO), we need not worry about SQL injection attacks (Make sure we don’t inadvertently remove this protection). We can avoid all those lines of code to sanitise the data before feeding it to the DB.

 So, how do we create a simple select query to fetch all values from the users table?

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

 DB::table is responsible to begin a fluent query against a database table. The table from which the value has to be selected is mentioned inside the brackets within quotes and finally the get() method gets the values. Similarly to fetch a single row we can modify the above code by adding a where clause

1	$user = DB::table('users')->where('name', 'shivam')->first();

Here, we are trying to fetch a row that has the value Shivam in its name column. The first() method will only return the first find. What if we need only the user id of Shivam. Instead of returning the entire result array we can simply pluck out that specific column?

1	$user_id = DB::table('users')->where('name', 'shivam')->pluck('id');

For specifying more than one column we can use the select clause

1	$users = DB::table('users')->select('name', 'email')->get();

I now believe you are getting the grip. Things get more interesting further down.

We often write queries against certain ‘where conditions’. So how do we fetch the list of users whose user_id is less than 10?

1	$users = DB::table('users')->where(id, '<', 10)->get();

Yes, we split up the operator and the operands as three parameters and feed it to the where conditions. Now we have a situation, we need to fetch all those users whose user_id falls between 10 and 20.

1	$users = DB::table('users')->whereBetween('id', array(10, 20))->get()

Laravel has the whereBetween(), whereNotBetween(), wherein() and whereNotIn() methods to which we can pass values as an array.

Why are we passing values as an array and not as comma separated parameters?

At the start of this blog I did mention about SQL injection attacks. Let’s say that the values 10 and 20 are taken as user inputs. As programmers we cannot trust what the user types into the input field. He can be a valid user who enters proper values or someone trying to enter false values and crash your DB.

1	$users = DB::table('users')->whereBetween('id', array($from, $to))->get()

Here $from and $to are user inputs. If we look in to the Laravel’s database connection class for the select() method this array is wrapped around PDO connection and it is responsible to sanitize the data before the query is executed. So you have clean queries!!

Using Query builder we can also write raw SQL queries

1	DB::select(DB::raw(“SELECT * FROM `users` WHERE name = ‘$name’ ”));

Here $name is obtained from user input. $name may contain malicious code therefore we need to alter the above code to make it SQL friendly.

1	DB::select(DB::raw("SELECT * FROM `users` WHERE `name` = :username"), array('username' => $name));

So the array value when passed through the PDO connection gets sanitized.

What is Eloquent in Laravel ?

Eloquent is object that is representative of your database and tables,in other words it’s act as controller between user and DBMS

For eg,

• DB raw query,

select * from post LIMIT 1

• Eloquent equivalent,

$post = Post::first();

$post->column_name;

What is ORM?

ORM or Object Relational Mapper is a technique to access objects without having to consider how those objects are related to their source.

What is Eloquent?

The ORM included in Laravel is called Eloquent and it enables us to work with the database objects and relationships using an expressive syntax. It is similar to working with objects in PHP. In Laravel, each database table has a corresponding “Model”. Eloquent ORM provides Active Record implementation which means that each model we create in our MVC structure corresponds to a table in our database.

Creating an Eloquent model is similar to creating a class. All the model files should be inside the app/models folder.

class Group extends Eloquent { }

All Eloquent models extends from the Eloquent class. The lower-case, plural name of the class will be used as the table name unless another name is explicitly specified. Eloquent will also assume that each table has a primary key column named “id” unless specified. We can specify a table as follows:

class Group extends Eloquent

1 2 3 4 5

{   protected $table = 'group_list';   // will point to group_list table if mentioned   }

Here, Group model will correspond to groups table (by default). We can access the data in the groups table using the basic CRUD operations.

By default Eloquent models will have auto-incrementing keys.

Create:

1 2 3 4 5 6 7 8 9 10 11

$new_group = new Group; $new_group->name = 'NewGroup'; $new_group->description = 'Awesome Group'; $new_group->save(); <read:< pre="">   <strong>// To get all groups</strong>   Group::all();   // To find a group by passing the group id etc.

1	Group::find($id);

// Try to retrieve a model by primary key else throw an exception

1 2	$model = User::findOrFail(1); $model = User::where('id', '>', 5)->firstOrFail();

Update:

// Retrieve and update

1 2 3

$group = Group::find(1); $group->name = ‘Group01’; $group->save();

// Using a WHERE clause

1	Group::where('name', '=', ‘Group01’)-date(array('name' => ‘Group1’));

// Delete one record

1 2	$group = Group::find(1); $group->delete();

// Delete several

1 2	Group::destroy(1, 2, 3); Group::where('id', '<', 10)->delete();

There are more topics to cover under Eloquent, hopefully in the next blog.

Conclusion:

It’s way of representing your database values with object you can easily use with

your application.

by: webkul.com