Connecting Two Different Database Tables using Laravel

Laravel is a popular PHP web framework that provides an efficient and feature-rich way to build robust applications. One of the key features of Laravel is its ORM (Object-Relational Mapping) system, which allows developers to interact with their databases in a more intuitive and object-oriented manner.

In this article, we will explore how to connect two different database tables using Laravel’s Eloquent ORM system. We will start by understanding the basics of Eloquent and then move on to discuss how to establish relationships between tables.

Understanding Eloquent

Eloquent is Laravel’s default ORM system, which provides a simple and intuitive way to interact with your database. It allows you to treat your database tables as objects, making it easier to perform CRUD (Create, Read, Update, Delete) operations.

In Eloquent, models are used to represent your database tables. A model typically extends the Illuminate\Database\Eloquent\Model class and provides methods for interacting with the corresponding table in your database.

For example, if we have a Client model that represents our clients table, we can use the Client model to perform CRUD operations on the table:

use Illuminate\Database\Eloquent\Model;

class Client extends Model {
    protected $fillable = ['company_name', 'main_id'];
}

In this example, we define a Client model that extends the Model class and specifies the fields that are allowed to be mass-assigned.

Defining Relationships

To connect two different database tables using Eloquent, we need to define relationships between them. A relationship is a way of expressing the connection between two models.

There are several types of relationships that can be defined in Eloquent:

• BelongsTo: A model that belongs to another model.
• HasMany: A model that has multiple instances of another model.
• HasOne: A model that has only one instance of another model.
• BelongsToMany: A model that belongs to multiple models, and vice versa.

Let’s consider an example where we want to connect our clients table with our rules table. We can define the following relationships:

use Illuminate\Database\Eloquent\Model;

class Client extends Model {
    public function main()
    {
        return $this->hasMany(Main::class);
    }

    public function rule()
    {
        return $this->hasMany(Rule::class, 'company_name', 'name');
    }
}

class Main extends Model {
    public function rule()
    {
        return $this->hasMany(Rule::class);
    }

    public function client()
    {
        return $this->belongsTo(Client::class, 'name', 'company_name');
    }
}

class Rule extends Model {
    public function main()
    {
        return $this->belongsTo(Main::class);
    }

    public function client()
    {
        return $this->belongsTo(Client::class, 'name', 'company_name');
    }
}

In this example, we define the following relationships:

• Client has many Main instances (main() method).
• A Main instance belongs to a Client instance (client() method).
• Rule has many Main instances (main() method).
• A Main instance belongs to a Rule instance (rule() method).

Querying Relationships

Once we have defined relationships between models, we can query them using Eloquent’s query builder.

For example, let’s say we want to retrieve all the clients that belong to a specific main. We can use the following code:

$main = Main::find(1);

$client = $main->client;

foreach ($client as $client) {
    echo $client->company_name;
}

In this example, we first find the Main instance with the primary key 1. Then, we use the client() method to retrieve the client that belongs to that main. Finally, we loop through the clients and display their company names.

Eager Loading Relationships

When querying relationships, Eloquent can perform eager loading, which means it loads related data in addition to the main model.

For example, let’s say we want to retrieve all the rules for a specific client. We can use the following code:

$client = Client::find(1);

$rules = $client->rule()->get();

foreach ($rules as $rule) {
    echo $rule->company_name;
}

In this example, we first find the Client instance with the primary key 1. Then, we use the rule() method to retrieve the rules for that client. The get() method performs eager loading, which means it loads the related data in addition to the main model.

Conclusion

Connecting two different database tables using Eloquent is a powerful feature of Laravel’s ORM system. By defining relationships between models and querying them, you can easily perform CRUD operations on multiple tables.

In this article, we explored how to connect our clients table with our rules table using Eloquent’s relationship features. We also discussed how to query relationships and perform eager loading.

Whether you’re building a small application or a complex enterprise system, understanding relationships between models is essential for efficient data retrieval and manipulation.

Last modified on 2023-06-04