Introduction

Database tables frequently have connections to one another. A blog article could receive a lot of comments, or an order might be connected to the individual who placed it. Eloquent supports a range of typical partnerships and makes maintaining and working with these relationships simple:

Defining Relationships

Eloquent relationships are described as operations on your model classes in Eloquent. When relationships are defined as methods, they offer strong method chaining and querying capabilities since they are also effective query builders. For instance, we may chain further query restrictions on the relationship of this post:

await User.with({posts:function(query){
	query.where('active', 1)
}}).get();

Let's first learn how to define each connection type that Eloquent supports before getting too involved with utilising relationships.

One To One

One of the most fundamental kinds of database relationships is one-to-one. One Phone model, for instance, may be linked to a User model. The User model will have a phone method added to it in order to specify this relationship. Phone should invoke the hasOne function and return the outcome. Your model has access to the hasOne function through the @ostro/database/eloquent/model base class:

const Model = require('@ostro/database/eloquent/model')
class User extends Model {
	 /**
     * Get the phone associated with the user.
     */
    phone() {
        return this.hasOne('~/app/models/phone');
    }
}

module.exports = User

Based on the parent model name, Eloquent determines the foreign key of the connection. In this situation, it is automatically presumed that the Phone model has a user id foreign key. You can send a second parameter to the hasOne function to override this convention:

return this.hasOne(Phone, 'foreign_key');

Eloquent also presupposes that the foreign key should have a value that corresponds to the parent's main key column. In other words, Eloquent will search the user id column of the Phone record for the value of the user id column. You may add a third argument to the hasOne function if you want the relationship to utilise a primary key value other than id or your model's $primaryKey property:

return this.hasOne(Phone, 'foreign_key', 'local_key');

Defining The Inverse Of The Relationship

So, using our User model, we can access the Phone model. To access the person who owns the phone, let's establish a relationship on the Phone model next. The belongsTo method may be used to define the inverse of a hasOne relationship:

const Model = require('@ostro/database/eloquent/model')
class Phone extends Model {

	 /**
     * Get the phone associated with the user.
     */
    user() {
        return this.belongsTo('~/app/models/user');
    }
}
module.exports = Phone

Eloquent will try to locate a User model with an id that matches the user id field on the Phone model when the user method is called.

By looking at the relationship method's name and adding the _id suffix, Eloquent can discover the foreign key name. Eloquent thus presumes that the Phone model contains a user id field in this instance. However, you may supply a custom key name as the second argument to the belongsTo function if the foreign key on the Phone model is not user id:

/**
* Get the user that owns the phone.
*/
user() {
   return this.belongsTo(User, 'foreign_key');
}

You can supply the parent table's unique key as a third argument to the belongsTo function if the parent model does not use id as its main key or if you prefer to locate the related model using a different column:

/**
* Get the user that owns the phone.
*/
user() {
   return this.belongsTo(User, 'foreign_key', 'owner_key');
}

One To Many

Relationships where one model is the parent to one or more child models are referred to as one-to-many relationships. A blog article, for instance, may receive an endless number of comments. One-to-many connections are defined the same way as all other Eloquent relationships by creating a method on your Eloquent model:

const Model = require('@ostro/database/eloquent/model')
class Post extends Model {

	 /**
     * Get the comments for the blog post.
     */
    comments() {
        return this.hasMany('~/app/models/comment');
    }
}

module.exports = Post

Querying Relations

Since every Eloquent connection is specified by a method, calling that method to get an instance of the relationship may be done without actually running a query to load the connected models. All varieties of Eloquent relationships furthermore act as query builders, enabling you to add more limitations to the relationship inquiry before ultimately running the SQL query against your database.

Consider a blog application, for instance, where a User model has several linked Post models:

const Model = require('@ostro/database/eloquent/model')
class User extends Model {
   
   posts() {
       return this.hasMany('~/app/models/post');
   }
}
module.exports = User

Querying Relationship Existence

You might want to restrict your results depending on the presence of a relationship while retrieving model records. Consider the scenario where you wish to find every blog post with at least one remark. You may achieve this by passing the relationship's name to the has and orHas methods:

const Post = require('~/app/models/post');
let posts = await Post.has('comments').get();

If you want even more flexibility, you may set additional has query restrictions, such as checking the content of comments, using the whereHas and orWhereHas methods:

const Post = require('~/app/models/post');
// Retrieve posts with at least one comment containing words like code%...
let posts = await Post.whereHas('comments', function ($query) {
   $query.where('content', 'like', 'code%');
}).get();

 

Eager Loading

The query for all the books in the database table will be run once by this loop, and then a separate query will be run for each book to retrieve the author. As a result, if we had 25 books, the code above would execute 26 queries: one for the original book and another 25 for the authors of the individual books.

Fortunately, eager loading allows us to condense this procedure to only two requests. Using the with method while creating a query, you may indicate which relationships should be eager loaded:

let books = await Book.with('author').get();
 
for (let book of books) {
    console.log(book.$$author.name);
    //or
     console.log(book.relation('author').name);
}

Only two queries will be done for this operation, one to retrieve all books and the other to retrieve all authors for all books:

select * from books
 
select * from authors where id in (1, 2, 3, 4, 5, ...)

Constraining Eager Loads

In some cases, eager loading a connection may be desired together with the insertion of new query criteria. You may achieve this by providing the with method with an array of relationships, where the array key is a relationship name, and the array value is a closure that imposes extra limits on the eager loading.

const User = require('~/app/models/user');
let users = await User.with({'posts' : function ($query) {
   $query.where('title', 'like', '%code%');
}}).get();

In this illustration, Eloquent will only eager load posts that have the term "code" in the post's title field. To further alter the eager loading process, utilise further query builder methods:

const User = require('~/app/models/user');
let users = await User.with({'posts' : function ($query) {
   $query.orderBy('created_at', 'desc');
}}).get();