Laravel Tutorials

Important Links

Home » Laravel Framework »

Database Query uses in laravel

How to insert record in database table?

the ::create method is used to insert a new record in the database table.

student::create(array(
'first_name' => 'Mahtab',
'last_name' => 'Habib',
'rank' => 1
));

Here student is the model name.

Using New object

you can create a new object of modal and assign different attributes to it. and Then call the save() function to save record in table.

$student = new Student([
'first_name' => $request->post('FirstName'),
'last_name'=> $request->post('LastName'),
'address'=> $request->post('Address')
]);
$student->save();

Methods such as firstOrCreate() or firstOrNew() are creating records.

File name : index.php

Retrieving records frm database

Using Eloquent ORM, getting and finding records from the database is very easy.

you will use get() and first() methods. The first() method will return only one record, while the get() method will return an array of records that you can loop over. Also the find() method can be used with an array of primary keys, which will return a collection of matching records.

$student = Students::all();

it gets all the records of students.

####################################

$users = User::select("*")
->where("status", 1)
->orderBy("name")
->get();

####################################

$posts = Post::orderBy('id', 'DESC')->get();

Find specific record by id:

$student = Students::find(1);

find a specific attribute.

$stud = Student::where('name', '=', 'Mahtab')->first();

For the get() method, find a age level greater than 18.

$rankStudents = Student::where('age', '>', 18)->get();

Updating records

find the record you would like to update, change the attributes, and save

$stud = Student::where('name', '=', 'sana')->first();
$stud->age = 1;
$stud->save();

Deleting records

There are two options: record pull-out and execute delete method, or simply use the destroy method. To find and delete a record, simply execute the following commands:

$student = Students::find(1);
$student->delete();

######## OR ##########
Students::destroy(1);
Students::destroy(1, 2, 3);

Note : the parameters of destroy are primary keys only .

To find and delete all students with age level that is greater than 18.

Students::where('age', '>', 18)->delete();

sometimes it makes sense to just use plain SQL, in the form of Raw Queries.

Retrieving data from Table

$users = DB::table('users')->get();
return view('home', ['users' => $users]);

Insert

DB::table('users')->insert(
['email' => 'sana@gmail.com', 'age' => 0]
);
DB::table('users')->insert([
['email' => 'sana@gmail.com', 'age' => 0],
['email' => 'sara@gmail.com', 'age' => 5],
]);

Retrieving A Single Row / Column From A Table

If you need to retrieve a single row from table then you can use the first() method.

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

echo $user->name;

If you don't need an entire row, you may extract a single value from a table using the value() method.

$email = DB::table('users')->where('name', 'sana-areeba')->value('email');

Get a single row by its id

use the find() method

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

Retrieving A List Of Column Values

retrieve a Collection containing the values of a single column, you may use the pluck method.

$titles = DB::table('post')->pluck('title');

foreach ($titles as $title) {
echo $title;
}

You may also specify a custom key column for the returned Collection:

$roles = DB::table('post')->pluck('title', 'name');

foreach ($roles as $name => $title) {
echo $title;
}

DB::insert('insert into users (name,email,password) values(?,?,?)',['sana','sana@gmail.com','password']);

Select

$userinfo = DB::select('select * from users'); return $userinfo;

You may not always want to select all columns from a database table. Using the select method, you can specify a custom select clause for the query.

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

Distinct

The distinct method allows you to force the query to return distinct results:

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

If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the addSelect method:

$query = DB::table('users')->select('name');

$users = $query->addSelect('age')->get();

Where Clauses

he most basic call to where requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. Finally, the third argument is the value to evaluate against the column.

$users = DB::table('users')->where('name', '=', 'sana')->get();

if you want to verify that a column is equal to a given value, you may pass the value directly as the second argument to the where method:

$users = DB::table('users')->where('age', 5)->get();

$users = DB::table('users')
->where('votes', '>=', 100)
->get();

$users = DB::table('users')
->where('votes', '<>', 100)
->get();

$users = DB::table('users')
->where('name', 'like', 'T%')
->get();

pass an array of conditions to the where function

$users = DB::table('users')->where([
['status', '=', '1'],
['subscribed', '<>', '1'],
])->get();

Or Statements

$users = DB::table('users')
->where('age', '>', 18)
->orWhere('name', 'Sana')
->get();

whereBetween / orWhereBetween

$users = DB::table('users')
->whereBetween('age', [1, 18])
->get();

$users = DB::table('users')
->whereNotBetween('votes', [1, 18])
->get();

whereIn

$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();

whereNotIn

$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();

whereNull

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

whereNotNull

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

whereDate

The whereDate method may be used to compare a column's value against a date:

$users = DB::table('users')
->whereDate('created_at', '2016-12-31')
->get();

whereMonth

$users = DB::table('users')
->whereMonth('created_at', '12')
->get();

whereDay

$users = DB::table('users')
->whereDay('created_at', '31')
->get();

whereYear

$users = DB::table('users')
->whereYear('created_at', '2016')
->get();

whereTime

$users = DB::table('users')
->whereTime('created_at', '=', '11:20:45')
->get();

whereColumn

The whereColumn method may be used to verify that two columns are equal:

$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();

$users = DB::table('users')
->whereColumn('updated_at', '>', 'created_at')
->get();

$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at'],
])->get();

Parameter Grouping

$users = DB::table('users')
->where('name', '=', 'John')
->where(function ($query) {
$query->where('votes', '>', 100)
->orWhere('title', '=', 'Admin');
})
->get();

Eloquent union()

$first = DB::table('users')
->where('id',4);

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

$first = User::where('id',5);

$users = User::where('id',3)->union($first)->get();

Eloquent limit()

public function index()
{
$users = DB::table('users')->limit(20)->get();
}

public function index()
{
$users = User::limit(5)->get();
}

How to Delete All record from table?

public function index()
{
User::truncate();
}

public function index()
{
User::whereNotNull('id')->delete();
}

public function index()
{
User:where('id', 'like' '%%')->delete();
}

public function index()
{
DB::table('users')->delete();
}

How to Get all Files in a Directory?

public function index()
{
$path = public_path('test');
$files = File::allFiles($path);

dd($files);
}

public function index()
{
$path = public_path('test');
$files = File::files($path);

dd($files);
}

public function index()
{
$path = public_path('demo');
$files = scandir($path);
dd($files);
}

How to Get last record from table in laravel?

$lastRecord = User::latest()->first();

$lastRecord = User::orderBy('id', 'DESC')->first();

how to get last inserted id in laravel 6?

Method One:

public function getLastInsertedId()
{
$id = DB::table('users')->insertGetId(
['email' => 'sana@gmail.com', 'name' => 'Sana']
);

dd($id);
}

how to get last inserted id in laravel 6?

Method Two:

public function getLastInsertedId()
{
$user = User::create(['name'=>'Sana' , 'email'=>'sana@gmail.com']);

dd($user->id);
}

How to get last executed mysql query in laravel 6 ?

public function lastQuery()
{
\DB::enableQueryLog();

$list = \DB::table("users")->get();
$query = \DB::getQueryLog();

dd(end($query));
}

Get All Records Between Two Dates

public function getRecord(Request $request)
{
$users = User::whereBetween('created_at',[$request->start_date,$request->end_date])
->get();

dd($users);
}

public function getRecord(Request $request)
{

$users = User::where('created_at','>=',$request->start_date)
->where('created_at','<=',$request->end_date)
->get();

dd($users);

}

public function getRecord(Request $request)
{

$users = User::select("users.*")
->whereBetween('created_at', [$request->start_date, $request->end_date])
->get();

dd($users);
}

Subquery Where Clauses

$users = User::where(function ($query) {
$query->select('type')
->from('membership')
->whereColumn('user_id', 'users.id')
->orderByDesc('start_date')
->limit(1);
}, 'Pro')->get();

orderBy

$users = DB::table('users')
->orderBy('name', 'desc')
->get();

$users = DB::table('users')
->orderBy('name', 'asc')
->get();

$res = DB::table('users')
->where('id', '=', $id)
->orderBy('id', 'desc')
->take(5)
->get();

$drivers = Driver::select('id','first_name','last_name','phone_number','registration_id')
->orderBy('first_name', 'asc')
->get();

latest / oldest

$user = DB::table('users')
->latest()
->first();

inRandomOrder

The inRandomOrder method may be used to sort the query results randomly

$randomUser = DB::table('users')
->inRandomOrder()
->first();

groupBy / having

$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();

Updates

$affected = DB::table('users')
->where('id', 1)
->update(['age' => 18]);

DB::update('update users set name = ? where id=2',['sana']);

Update Or Insert

DB::table('users')
->updateOrInsert(
['email' => 'sana@gmail.com', 'name' => 'sana'],
['age' => '1']
);

Deletes

DB::table('users')->delete();

DB::table('users')->where('age', '>', 18)->delete();

DB::table('users')->truncate();

DB::delete('delete from users where id= 2');

Debugging

DB::table('users')->where('votes', '>', 100)->dd();

DB::table('users')->where('votes', '>', 100)->dump();

selectRaw() with Avg/Sum/Count Calculations

If you need to perform groupBy() and then use some aggregation function from MySQL, like AVG() or COUNT(), it’s useful to perform a Raw Query for that specific section.

$users = DB::table('users')
->selectRaw('count(*) as user_count, status')
->where('status', '<>', 1)
->groupBy('status')
->get();

$products = DB::table('products')
->leftjoin('category','category.product_id','=','products.id')
->selectRaw('COUNT(*) as nbr', 'products.*')
->groupBy('products.id')
->get();

$salaries = DB::table('salaries')
->selectRaw('companies.name as company_name, avg(salary) as avg_salary, count(*) as people_count')
->join('companies', 'salaries.company_id', '=', 'companies.id')
->groupBy('companies.id')
->orderByDesc('avg_salary')
->get();

Filtering YEARS: groupByRaw, orderByRaw and havingRaw

if you want to add some SQL calculations inside of “group by” or “order by”? We have methods like groupByRaw() and orderByRaw() for this

$results = User::selectRaw('YEAR(birth_date) as year, COUNT(id) as amount')
->groupByRaw('YEAR(birth_date)')
->havingRaw('YEAR(birth_date) > 2000')
->orderByRaw('YEAR(birth_date)')
->get();

Calculating one field with sub-query: selectRaw()

$products = Product::select('id', 'name')
->selectRaw('price - discount_price AS discount')
->get();

$users = DB::table('users')
->select('name', 'surname')
->selectRaw("(CASE WHEN (gender = 1) THEN 'M' ELSE 'F' END) as gender_text")
->get();

Old SQL Query? Just use DB::select()

$results = DB::select('select * from users where id = ?', [1]);

Retrieving All Rows From A Table

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

return view('user.index', ['users' => $users]);

foreach ($users as $user) {
echo $user->name;
}

Retrieving A Single Row / Column From A Table

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

echo $user->name;

extract a single value from a record using the value method.

$email = DB::table('users')->where('name', 'sana')->value('email');

To retrieve a single row by its id

$user = DB::table('users')->find(3);

Selects

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

distinct method allows you to force the query to return distinct results

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

How to get query string value from URL.

https://desertebs.com/posts?name=sana

public function getName(Request $request)
{
// returns only name
$type = request('name'); // or
$type = $request->name; // or
$type = $request->input('name'); // or
$type = $request->query('name');

dd($name); // sana
}

Getting query string value in the view (blade)

access the value in view, we can use the helper method.

WhereIn query

WhereIn method will give you all the results. all you have to do is to pass an array

User::whereIn('id', [1, 2, 3, 5])->get();

If you dump the query you will get the following MySQL query.

select * from users where id in (1, 2, 3, 5);

Sometimes you may wish to nest the whereIn query.

$user = User::whereIn('id', function($query) {
$query->select('user_id')->from('role_user');
})->get();

The above query will give you all the user detail.

SELECT * FROM `users` WHERE `id` IN (
SELECT `user_id` FROM `role_user`
)

WhereNotIn query

WhereNotIn method is similar to whereIn method. If you want the exclude the id 1, 2, 3 and 5 from the user table.

User::whereNotIn('id', [10, 12, 13, 15])->get();

select * from users where id not in (10, 12, 13, 15);

Join

Inner Join Clause

Left Join / Right Join Clause

Cross Join Clause

Advanced Join Clauses

Sub-Query Joins

Inner Join

In Inner Join selects records if the given column values matching in both tables.

you have two tables, users and posts. Both have ID columns, and there is a user_id in the post, which is a foreign key for the users’ table.

$user = User::join('posts', 'posts.user_id', '=', 'users.id')->select('users.*')->get();

$products = Product::join('categories', 'products.category_id', '=', 'categories.id')
->select('products.*')
->get();
dd($products);

Left Join

LEFT JOIN returns all rows from the left table, even if there are no matches in the right table, The result is NULL from the right side.

User::leftJoin('posts', 'posts.user_id', '=', 'users.id') ->select('users.*') ->get();

$products = Product::leftJoin('categories', 'products.category_id', '=', 'categories.id') ->select('products.*') ->get(); dd($products);

Right Join

Right JOIN returns all rows from the right table, even if there are no matches in the left table, The result is NULL from the left side.

User::rightJoin('posts', 'posts.user_id', '=', 'users.id') ->select('users.*') ->get();

$products = Product::rightJoin('categories', 'products.category_id', '=', 'categories.id') ->select('products.*') ->get(); dd($products);

Cross Join

The CROSS JOIN joined every row from the first table (T1) with every row from the second table (T2).

Size::crossJoin('colours') ->get();

##########

$data = DB::table('sizes') ->crossJoin('colours') ->get(); dd($data);

Sub-Query Joins

DB::table('posts') ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at')) ->where('is_published', true)->groupBy('user_id');

##########

$data = DB::table('posts') ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at')) ->where('is_published', true)->groupBy('user_id'); dd($data);

Advanced Join

DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get();

Itechtuto

Laravel Tutorials

Important Links

Database Query uses in laravel

File name : index.php

Connect Us Socially:

Quick Links