Skip to content

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.

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;

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

Diagram
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');
}
}
/**
* 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);
}
}
/**
* 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);
}
}
/**
* 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);
}
}
/**
* 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();
}
}
/**
* 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();
}
}

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.