Lecture 7 ORM and DB Access

Laravel: Data Access
Course Code: CSC 4182 Course Title: Advanced Programming In Web Technologies
Dept. of Computer Science

Faculty of Science and Technology
Lecture No: 15 Week No: 10 Semester: Summer 2020-2021

Lecturer: MD.AL-AMIN; [email protected]
Lecture Outline
1. Introduction to Eloquent ORM

2. Introduction to Query Builder
3. Configuring models for Eloquent
4. DB Configuration
5. SQL Operation using Query Builder
6. Performing Join query using query builder
Introduction to Eloquent
ORM
• ORM stands for Object Relational Mapping.
• Eloquent is a powerful ORM used in Laravel framework.
• Using eloquent is very easy and the syntaxes are easy to learn.
• In eloquent for each database table has a corresponding "Model" which is used
to interact with that table.
• Models allow you to query for data in your tables, as well as insert new records
into the table.
• Operations like create, update, delete is very much easy with eloquent.
• It has built in methods to run complex queries in a simple way.
• Example: User model which
interacts with
users table
$users = User::all();
Eloquent method
to get all info
of users table
Introduction to Query Builder
• Laravel's database query builder provides a convenient, fluent interface to

creating and running database queries.
• It can be used to perform most database operations in your application and
works on all supported database systems.
• The Laravel query builder uses PDO parameter binding to protect your
application against SQL injection attacks.
• Example: DB façade to
begin query
• $users = DB::table('users')->get();
returns a fluent
query builder
instance for the
given table
Configuring models for
Eloquent
• To use eloquent we need to follow some rules while building models.
• Models typically live in the app directory, but you are free to place them
anywhere. (need some change in composer.json)
• All Eloquent models extend Illuminate\Database\Eloquent\Model class.
• Simply the model names should be singular and the table names should be
plural. Model Table
User users
Student students
Course courses
• Eloquent automatically maps with model and table.

• You may specify a custom table by defining a table property on your model.
class Flight extends Model {
protected $table = 'my_flights';
}
Configuring models for Eloquent
• Eloquent will also assume that each table has a primary key column named id.
• You may define a protected $primaryKey property to override this convention.
class Flight extends Model
{
protected $primaryKey = 'flight_id’;
}
• Eloquent assumes that the primary key is an incrementing integer value,
which means that by default the primary key will automatically be cast to an
int. If you wish to use a non-incrementing or a non-numeric primary key you
must set the public $incrementing property on your model to false.
{
public $incrementing = false;
}
• If your primary key is not an integer, you should set the protected $keyType
property on your model to string
{
protected $keyType = 'string';
}
Configuring models for Eloquent
• By default, Eloquent expects created_at and updated_at columns to exist on
your tables. If you do not wish to have these columns automatically managed by
Eloquent, set the $timestamps property on your model to false
{
public $timestamps = false;
}
• If you need to customize the names of the columns used to store the
timestamps, you may set the CREATED_AT and UPDATED_AT constants in your
model.
{
const CREATED_AT = 'creation_date';
const UPDATED_AT = 'last_update';
}
• Other columns will automatically mapped with model properties.
DB Configuration
Connecting with Database
• Open xampp start mysql.

• Create a database using phpMyAdmin
• In project root directory open .env file
Update these values with

yours
Creating Models
• Model file resides in App directory.
• Here I am creating a new folder named Models inside App and creating models.
Namespace should be
App\Foldername
Must extend model

class
courses table with
values
• Create a controller named CourseController

• Create a view for showing all courses.
• Create a function in CourseController.
• Create a route and bind with controller method.
• From the function we will retrieve data from database
Retrieving Values Rout
e
Including our
model
Eloquent all() method retrieves all information of

courses table and returns an array of Courses
allcourses.blade.php is residing in
resources/views/courses folder that’s why
courses.allcourses
In view use as
object.
See the properties
id, name we did
not declared in Output
model but we can
use it because
those are database
columns
CRUD Operations
• Insert Operation
• Already we know how to submit form data. After submission data to controller
function insert_course(Request $request){
$course = new Course();
$course->name= $request->name;
$course->save();
return view('courses.allcourses');
}
• Update Operation
function update_course(Request $request){
$course = Course::where('id' , $request->course_id)->first();
$course->name= $request->name;
$course->save();
}
• Delete operation
function delete_course(Request $request){
$course = Course::where('id' , $request->course_id)->first();
$course->delete();
}
SQL Operation using Query Builder
Retrieving Data
• Retrieving All Rows From A Table

$users = DB::table('users')->get();
• Retrieving A Single Row From A Table
$user = DB::table('users')->where('name', 'John')->first();
• Retrieving A Single Column From A Row
$name = DB::table('users')->where('name', 'John')->pluck('name');
• Retrieving A List Of Column Values
$roles = DB::table('roles')->lists('title', 'name');
• Specifying A Select Clause
$users = DB::table('users')->select('name', 'email')->get();
CRUD Operations
• Insert Operation
• Already we know how to submit form data. After submission data to controller
function insert_course(Request $request){
DB::table('courses')->insert( ['name' => $request->name]);
}
• Update Operation
function update_course(Request $request){
DB::table('courses')
->where('id', $request->id)
->update(['name' => $request->name]);
}
• Delete operation
function delete_course(Request $request){
DB::table('courses')
->where('id', $request->id)
->delete();
}
• Delete all records
DB::table('courses')->delete();
• Truncating A Table
DB::table('courses')->truncate();
Performing Join query
• Basic Join Statement
DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.id', 'contacts.phone', 'orders.price')
->get();
• Left Join Statement
DB::table('users')
->leftJoin('posts', 'users.id', '=', 'posts.user_id')
->get();
Books
 PHP Advanced and Object-Oriented Programming, 3rd Edition; Larry

Ullman; Peachpit, Press, 2013
 PHP Objects, Patterns and Practice, 5th Edition; Matt Zandstra; Apress,
2016
 Learning PHP, MySQL, JavaScript and CSS, 2nd Edition; Robin Nixon;
O’Reilly, 2009
 Eloquent JavaScript: A Modern Introduction to Programming; Marijn
Haverbeke; 2011
 Learning Node.js: A Hands On Guide to Building Web Applications in
JavaScript; Marc Wandschneider; Addison-Wesley, 2013
 Beginning Node.js; Basarat Ali Syed; Apress, 2014
References
1. https://laravel.com/docs/7.x/queries
2. https://laravel.com/docs/7.x/eloquent
3. https://laravel.com/docs/7.x/eloquent#eloquent-model-conventions
Thank You!

Lecture 7 ORM and DB Access

Uploaded by

Copyright:

Available Formats

Lecture 7 ORM and DB Access

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lecture 7 ORM and DB Access

Uploaded by

Copyright:

Available Formats

Laravel: Data Access

Dept. of Computer Science

Lecture No: 15 Week No: 10 Semester: Summer 2020-2021

1. Introduction to Eloquent ORM

• Laravel's database query builder provides a convenient, fluent interface to

• Eloquent automatically maps with model and table.

• Open xampp start mysql.

Update these values with

Must extend model

• Create a controller named CourseController

Eloquent all() method retrieves all information of

• Retrieving All Rows From A Table

 PHP Advanced and Object-Oriented Programming, 3rd Edition; Larry

You might also like