Database Integration in Laravel

Laravel is a powerful PHP framework that simplifies the process of building web applications. One of its core features is seamless database integration, allowing developers to interact with databases effortlessly. Laravel supports various database systems, including MySQL, PostgreSQL, SQLite, and SQL Server, making it versatile for different projects.

Migration

In Laravel, migrations are a powerful feature that allows developers to manage database schema changes in a version-controlled manner. Migrations are essentially PHP files that define the structure of your database tables, making it easy to create, modify, and share database schemas across different environments.

Configuration of the .env File

Before you can start working with databases in Laravel, you need to configure your database connection settings. This is done in the .env file located in the root of your Laravel project. The .env file contains environment-specific variables, including database credentials. Here’s a basic example of what the database configuration might look like:


            DB_CONNECTION=mysql

            DB_HOST=127.0.0.1

            DB_PORT=3306

            DB_DATABASE=your_database_name

            DB_USERNAME=your_username

            DB_PASSWORD=your_password

Make sure to replace the placeholders with your actual database information and change the `DB_CONNECTION` according to the database system you are using. After configuring the .env file, you can run the following command to clear the configuration cache:


            php artisan config:cache

Creating Database Tables

Once your database connection is set up, you can create tables using Laravel's migration feature. Migrations are like version control for your database, allowing you to define and modify your database schema easily. To create a new migration, use the Artisan command:


            php artisan make:migration create_users_table

This command generates a new migration file in the database/migrations directory. You can then define the structure of your table within the up method of the migration file. For example:


            public function up() {

            Schema::create('users', function (Blueprint $table) {

            $table->id();

            $table->string('name');

            $table->string('email')->unique();

            $table->timestamps();

            });

            }

After defining your tables, run the migration with:


            php artisan migrate

Eloquent ORM

Eloquent is Laravel's built-in Object-Relational Mapping (ORM) system. It provides a simple and elegant way to interact with your database using PHP syntax instead of SQL queries. Eloquent allows you to define models that correspond to your database tables, making it easier to work with your data.

What is a Model?

In Eloquent, a model is a PHP class that represents a table in your database. Each instance of a model corresponds to a single row in that table. Models allow you to interact with your database in an object-oriented way, making it easier to manage and manipulate your data.

Creating a Model

To create a model, you can use the Artisan command:


            php artisan make:model Post

This command creates a 'Post' model in the 'app/Models' directory. By default, Eloquent assumes that the table name is the plural form of the model name (e.g., posts for the Post model).

You can also create a Model with the migration:


            php artisan make:model Post -m

It's also possible to create a Model with migration and a Controller with this artisan command:


            php artisan make:model Post -mc

If you want to create a Model with migration and Ressource-Controller, use this artisan command:


            php artisan make:model Post -mr

Basic Structure of a Model

Here’s a basic example of what a model might look like:


            namespace App\Models;


            use Illuminate\Database\Eloquent\Factories\HasFactory;

            use Illuminate\Database\Eloquent\Model;


            class Post extends Model

            {

            use HasFactory;


            // Specify the table if it doesn't follow the naming convention

            protected $table = 'posts';


            // Specify the primary key if it's not 'id'

            protected $primaryKey = 'post_id';


            // Specify the attributes that are mass assignable

            protected $fillable = ['title', 'content', 'status'];


            // Specify the attributes that should be hidden for arrays

            protected $hidden = ['created_at', 'updated_at'];


            // Define relationships

            public function comments()

            {

            return $this->hasMany(Comment::class);

            }


            public function user()

            {

            return $this->belongsTo(User::class);

            }

            }

Key Components of a Model

Namespace and Use Statements: Models are typically placed in the 'App\Models' namespace. You also import necessary classes like 'Model' and 'HasFactory'.
Table Name: If your table name does not follow Laravel's naming conventions (plural form of the model name), you can specify it using the '$table' property.
Primary Key: If your primary key is not 'id', you can specify it using the '$primaryKey' property.
Mass Assignment: The '$fillable' property allows you to specify which attributes can be mass-assigned. This is a security feature to prevent mass assignment vulnerabilities.
Hidden Attributes: The '$hidden' property allows you to specify which attributes should be hidden when the model is converted to an array or JSON.
Relationships: Eloquent makes it easy to define relationships between models. Common relationships include:
- One-to-Many: A post can have many comments.
- Many-to-One: A comment belongs to a single post.
- Many-to-Many: A user can have many roles, and a role can belong to many users.

Defining Relationships

Eloquent, Laravel's powerful ORM, makes it easy to define relationships between models, allowing for seamless data retrieval and manipulation. There are several types of relationships you can define:

One-to-Many Relationship

In the 'Post' model, you can define a one-to-many relationship with comments:


            public function comments()

            {

            return $this->hasMany(Comment::class);

            }

In the 'Comment' model, you would define the the inverse relationship:


            public function post()

            {

            return $this->belongsTo(Post::class);

            }

Many-to-Many Relationship

If you have a 'User# model and a 'Role' model, you can define a many-to-many relationship like this:
In the 'User' model:


            public function roles()

            {

            return $this->belongsToMany(Role::class);

            }

In the 'Role' model:


            public function users()

            {

            return $this->belongsToMany(User::class);

            }

Basic CRUD Operations

CRUD stands for Create, Read, Update, and Delete, which are the four basic operations you can perform on a database. Eloquent ORM simplifies these operations, allowing you to interact with your database using PHP syntax.

1. Create
To create a new record in the database, you can instantiate a model, set its attributes, and then call the 'save()' method.


            $post = new Post();

            $post->title = 'My First Post';

            $post->content = 'This is the content of my first post.';

            $post->status = 'published';

            $post->save();

Alternatively, you can use the 'create()' method, which allows mass assignment:


            $post = Post::create([

            'title' => 'My Second Post',

            'content' => 'This is the content of my second post.',

            'status' => 'draft',

            ]);

2. Read
To retrieve records from the database, you can use various methods:


            //get all records:

            $post = post::all();

            //Find a record by ID:

            $post = Post::find(1);

            //Query with conditions:

            $publishedPosts = Post::where('status', 'published')->get();

3. Update
To update an existing record, first retrieve it, modify the attributes, and then call the 'save()' method:


            $post = Post::find(1);

            $post->title = 'Updated Title';

            $post->save();

            

            //You can also use the 'update()' method directly:

            Post::where('id', 1)->update(['status' => 'published']);

4. Delete
To delete a record, you can retrieve it and call the 'delete()' method:


            $post = Post::find(1);

            $post->delete();

            

            //You can also delete records directly using the 'destroy()' method:

            Post::destroy(1); // Deletes the post with ID 1

Complex Queries with Eloquent

Eloquent also allows you to perform complex queries using its fluent query builder. Here are some examples:

1. Querying with Conditions


            $posts = Post::where('status', 'published')

            ->where('created_at', '>=', now()->subDays(30))

            ->get();

2. Ordering Results


            $posts = Post::orderBy('created_at', 'desc')->get();

3. Pagination


            $posts = Post::paginate(10); // 10 posts per page

4. Eager Loading Relationships


            $posts = Post::with('comments')->get(); //Assuming yu have a 'Comment' model related to 'Post'

5. Aggregation


            $count = Post::where('status', 'published')->count();

            $averageLikes = Post::avg('likes');

6. Joins


            $posts = Post::join('comments', 'posts.id', '=', 'comments.post_id')

            ->select('posts.title', 'comments.content')

            ->get();

7. Subqueries


            $latestPost = Post::select('id')

            ->where('user_id', 1)

            ->orderBy('created_at', 'desc')

            ->limit(1);


            $posts = Post::where('id', $latestPost)->get();

8. Raw Expressions


            $posts = Post::selectRaw('count(*) as post_count, user_id')

            ->groupBy('user_id')

            ->having('post_count', '>', 5)

            ->get();

Eloquent's capabilities for complex queries allow developers to construct sophisticated data retrieval operations with ease. By leveraging these features, you can efficiently manage and manipulate your application's data while keeping your code clean and maintainable.

Seeders

To populate your database with sample data, Laravel provides seeders. Seeders allow you to create and insert data into your tables easily. You can create a new seeder using the Artisan command:


            php artisan make:seeder UsersTableSeeder

In the generated seeder file, you can define the data you want to insert. For example:


            public function run() {

            DB::table('users')->insert([

            'name' => 'John Doe',

            'email' => 'john@example.com',

            'created_at' => now(),

            'updated_at' => now(),

            ]);

            }

After defining your seeders, you can run them using:


            php artisan db:seed

You can also call multiple seeders from the DatabaseSeeder class to populate your database with various data sets. In this case you have to First, create individual seeders for each table or dataset you want to populate.


            php artisan make:seeder PostsTableSeeder

            php artisan make:seeder CommentsTableSeeder

In each seeder file, define the logic to insert data into the respective tables. For example, in PostsTableSeeder:


            public function run() {

            DB::table('posts')->insert([

            ['title' => 'First Post', 'content' => 'This is the content of the first post', 'user_id' => 1],

            ['title' => 'Second Post', 'content' => 'This is the content of the second post', 'user_id' => 1],

            ]);

            }

Open the DatabaseSeeder class and call your individual seeders within the run method. For example:


            public function run() {

            $this->call([

            UsersTableSeeder::class,

            PostsTableSeeder::class,

            CommentsTableSeeder::class,

            ]);

            }

After setting up your seeders, you can run all of them at once using the following command:


            php artisan db:seed