Elasticsearch to MySQL Model Relationships

In many applications, Elasticsearch is used in conjunction with a relational database like MySQL. The Laravel-Elasticsearch integration allows you to define hybrid relationships between Elasticsearch and MySQL models seamlessly. This enables a flexible and powerful architecture for applications that need the speed and scalability of Elasticsearch along with the reliability and consistency of a relational database.

---

Implementing Hybrid Relationships

Hybrid relationships are crucial for applications that leverage both the rapid search capabilities of Elasticsearch and the structured storage of MySQL. Here’s how to implement these relationships using the HybridRelations trait.

use PDPhilip\Elasticsearch\Eloquent\HybridRelations;

---

Full example

Relationship Diagram

This diagram visualizes the connections between Elasticsearch and MySQL models, illustrating the flexible data architecture enabled by hybrid relationships.

User model (SQL)

use Illuminate\Foundation\Auth\User as Authenticatable;
use PDPhilip\Elasticsearch\Eloquent\HybridRelations;

/**
 * App\Models\User
 *
 * *****Relationships*******
 * @property-read UserLog $userLogs
 * @property-read UserProfile $userProfile
 * @property-read Company $company
 * @property-read Avatar $avatar
 * @property-read Photo $photos
 */
class User extends Authenticatable
{
    use HybridRelations;

    protected $connection = 'mysql';

    public function userLogs()
    {
        return $this->hasMany(UserLog::class);
    }

    public function userProfile()
    {
        return $this->hasOne(UserProfile::class);
    }

    public function company()
    {
        return $this->belongsTo(Company::class);
    }

    public function avatar()
    {
        return $this->morphOne(Avatar::class, 'imageable');
    }

    public function photos()
    {
        return $this->morphMany(Photo::class, 'photoable');
    }
}

UserLog model (Elasticsearch)

/**
 * App\Models\UserLog
 *
 ******Fields*******
 * @property string $_id
 * @property string $company_id
 * @property string $title
 * @property integer $code
 * @property mixed $meta
 * @property Carbon|null $created_at
 * @property Carbon|null $updated_at
 *
 ******Relationships*******
 * @property-read User $user
 */
class UserLog extends Model
{
    protected $connection = 'elasticsearch';

    public function user()
    {
        return $this->belongsTo(User::class);
    }

}

UserProfile model (Elasticsearch)

/**
 * App\Models\UserProfile
 *
 ******Fields*******
 * @property string $_id
 * @property string $user_id
 * @property string $twitter
 * @property string $facebook
 * @property string $address
 * @property \Illuminate\Support\Carbon|null $created_at
 * @property \Illuminate\Support\Carbon|null $updated_at
 *
 ******Relationships*******
 * @property-read User $user
 *
 */
class UserProfile extends Model
{
    protected $connection = 'elasticsearch';

    public function user()
    {
        return $this->belongsTo(User::class);
    }

}

Company Model (Elasticsearch)

/**
 * App\Models\CompanyLog
 *
 ******Fields*******
 * @property string $_id
 * @property string $name
 * @property integer $status
 * @property \Illuminate\Support\Carbon|null $created_at
 * @property \Illuminate\Support\Carbon|null $updated_at
 *
 ******Relationships*******
 * @property-read User $user
 */
class CompanyLog extends Model
{
    protected $connection = 'elasticsearch';

    public function company()
    {
        return $this->hasMany(User::class);
    }
}

Avatar Model (Elasticsearch)

/**
 * App\Models\Avatar
 *
 ******Fields*******
 * @property string $_id
 * @property string $url
 * @property string $imageable_id
 * @property string $imageable_type
 * @property \Illuminate\Support\Carbon|null $created_at
 * @property \Illuminate\Support\Carbon|null $updated_at
 *
 ******Relationships*******
 * @property-read Company $company
 */
class Avatar extends Model
{
    protected $connection = 'elasticsearch';

    public function imageable()
    {
        return $this->morphTo();
    }
}

Photo Model (Elasticsearch)

/**
 * App\Models\Photo
 *
 ******Fields*******
 * @property string $_id
 * @property string $url
 * @property string $photoable_id
 * @property string $photoable_type
 * @property \Illuminate\Support\Carbon|null $created_at
 * @property \Illuminate\Support\Carbon|null $updated_at
 *
 ******Relationships*******
 * @property-read Company $company
 */
class Photo extends Model
{
    protected $connection = 'elasticsearch';

    public function photoable()
    {
        return $this->morphTo();
    }
}

Example Usage

Illustrates how to retrieve and interact with data across Elasticsearch and MySQL.

// Retrieve User and related Elasticsearch models
$user = User::first();
$userCompanyName = $user->company->name; //Company name for the user
$userTwitter = $user->userProfile->twitter;
$userAvatar = $user->avatar->url; //Link to Avatar
$userPhotos = $user->photos->toArray(); //Array of photos

// Retrieve UserLog and related MySQL User
$userLog = UserLog::first();
$userName = $userLog->user->name;

// Retrieve Company and related MySQL Users
$company = Company::first();
$companyUsers = $company->users->toArray(); //Array of users

This setup highlights the versatility of Laravel’s Eloquent ORM in managing complex data relationships across different database systems, ensuring both rapid data access and reliable data integrity.

---