Reputation: 1
MultiDatabase Relationship Model
My goal is to create a project for multiple vehicle resellers. I need to stay up-to-date with new brands, models, versions, and years that are constantly being released. However, it’s not practical for me to update all the vehicle sites I manage with the same information repeatedly, as this leads to redundant data, especially when dealing with a large volume of information.
To solve this, my idea was to create a common database to store data that is shared across all sites and needs to be kept up-to-date. The common database contains nested years, versions, models, brands, types tables. The problem lies in the integration. My main database (and future databases, as there will be multiple, all referring to the common database) stores data about the listed vehicles, including a reference to the year_id of the vehicle, which is stored in the common database.
I’ve already set up a model in my Laravel project as usual, including the BelongsTo relationships. I also specified the connection to be used for the Year, Version, Model, Brand, and Type models.
However, when I try to use a controller, it fails. For example, it tries to search for version.id in the wrong database. I’ve tried a few solutions, but none have worked so far.
PS. I'm considering using an API to communicate to the database in the future so I can implement a cache in the response to minimize the requests to the common database, don't know if it will influence now, I think I can manage changing it later.
Heres a part of the controller:
`
public function index()
{
$ajustes = Ajustes::first();
$cars = Vehicles::query()
->with(['year.version.model.brand.type'])
->status(1)
->typeId(1)
->orderBy('vehicles.created_at', 'DESC')
->limit($ajustes->max_cars ?? 5)
->get();
`
Here's a the Vehicle model: ` <?php
/**
* Created by Reliese Model.
*/
namespace App\Models;
use App\User;
use App\Traits\UsesCustomConnection;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Spatie\Activitylog\Traits\LogsActivity;
/**
* Class Vehicles
*
* @property int $id
* @property string $slug
* @property string|null $title
* @property int $status
* @property string $year_fab
* @property int $doors
* @property string $fuel
* @property string $condition
* @property string $color
* @property string $transmission
* @property float $mileage
* @property int $accept_trade
* @property int $only_owner
* @property int $consignated
* @property string|null $plate
* @property float $price
* @property float|null $original_price
* @property string|null $warranty
* @property string|null $description
* @property int|null $potency
* @property int|null $chassi
* @property int|null $engine_displacement
* @property int|null $tractions
* @property int|null $passengers
* @property string|null $bodywork
* @property int|null $axes
* @property int|null $dealership_review
* @property Carbon $created_at
* @property Carbon|null $updated_at
* @property string|null $deleted_at
* @property int $user_id
* @property int $year_id
* @property int $users_id
*
* @property Users $users
* @property Collection|Transactions[] $transactions
* @property Collection|VehicleContacts[] $vehicle_contacts
* @property Collection|VehicleOptionals[] $vehicle_optionals
* @property Collection|VehiclePhotos[] $vehicle_photos
*
* @package App\Models
*/
class Vehicles extends Model
{
// protected $apiConnection = 'vehicles_api_global';
// protected $mainConnection = 'mysql';
protected $connection = 'mysql';
use LogsActivity;
use SoftDeletes;
protected $table = 'vehicles';
public static function boot()
{
parent::boot();
self::creating(function (self $model) {
$model->slug = uniqid();
});
self::created(function (self $model) {
$slug = slugifyHelper(
$model->year->version->model->brand->name,
$model->year->version->model->name,
$model->year->version->name,
$model->year->name,
$model->id,
$model->getConnection()->getConfig("schema")
);
$model->slug = $slug;
$model->save();
});
}
protected $casts = [
'status' => 'int',
'doors' => 'int',
'mileage' => 'float',
'accept_trade' => 'int',
'only_owner' => 'int',
'consignated' => 'int',
'price' => 'float',
'original_price' => 'float',
'potency' => 'int',
'chassi' => 'int',
'engine_displacement' => 'int',
'tractions' => 'int',
'passengers' => 'int',
'axes' => 'int',
'dealership_review' => 'int',
'user_id' => 'int',
'year_id' => 'int',
'users_id' => 'int'
];
protected $fillable = [
'slug',
'title',
'status',
'year_fab',
'doors',
'fuel',
'condition',
'color',
'transmission',
'mileage',
'accept_trade',
'only_owner',
'consignated',
'plate',
'price',
'original_price',
'warranty',
'description',
'potency',
'chassi',
'engine_displacement',
'tractions',
'passengers',
'bodywork',
'axes',
'dealership_review',
'user_id',
'year_id',
'users_id'
];
public function getThumbPath()
{
return "assets/images/vehicle/thumb/$this->image";
}
public function getLogNameToUse(string $eventName = ''): string
{
return "Imóvel";
}
public function photos()
{
return $this->hasMany('App\Models\VehiclePhotos', 'vehicle_id')->orderBy('order', 'ASC');
}
public function getTitle()
{
$typeVehicleSlug = $this->typeVehicle->name;
// return "{$this->year->version->model->brand->name} {$this->year->version->model->name}";
$id = $this->id;
return $typeVehicleSlug . ' ' . $id;
}
public function getFrontTitle()
{
$typeVehicleSlug = $this->typeVehicle->name;
}
public function getCoverImage()
{
$image = $this->image;
if (!empty($image)) {
return asset('/assets/images/vehicle/thumb/' . $image);
}
return null;
}
/* SCOPE */
public function scopeStatus($query, $status = null)
{
if (!is_null($status)) {
if ($status == 0) {
$query->where('status', 0);
} else {
$query->where('status', 1);
}
}
}
public function scopeOrder($query, $order = null)
{
if (!is_null($order)) {
switch ($order) {
case 1:
$query->orderBy('created_at', 'ASC');
break;
case 2:
$query->orderBy('created_at', 'DESC');
break;
case 3:
$query->orderBy('price', 'ASC');
break;
case 4:
$query->orderBy('price', 'DESC');
break;
default:
$query->orderBy('created_at', 'DESC');
break;
}
}
}
public function scopePrice($query, $priceMin = null, $priceMax = null)
{
if ($priceMax == 0) {
$priceMax = null;
}
if ($priceMin == 0) {
$priceMin = null;
}
if ((!is_null($priceMin)) && (!is_null($priceMax))) {
$query->where('price', '>', getFloatFromInput($priceMin));
$query->where('price', '<', getFloatFromInput($priceMax));
} else if (!is_null($priceMin)) {
$query->where('price', '>', getFloatFromInput($priceMin));
} else if (!is_null($priceMax)) {
$query->where('price', '<', getFloatFromInput($priceMax));
}
}
public function scopeActive(\Illuminate\Database\Eloquent\Builder $query, ?bool $value): void
{
if (!empty($value)) {
$query->where('active', '=', $value);
}
}
public function scopeJoins($query)
{
$query->with([
'years.versions.models.brands.types',
]);
}
public function scopeTypeId($query, $typeId)
{
if (!is_null($typeId)) {
$query->whereHas('year.version.model.brand.type', function ($q) use ($typeId) {
$q->where('id', $typeId);
});
}
}
public function scopeBrandId($query, $brandId)
{
if (!is_null($brandId)) {
$query->whereHas('year.version.model.brand', function ($q) use ($brandId) {
$q->where('id', $brandId);
});
}
}
public function scopeModelId($query, $modelId)
{
if (!is_null($modelId)) {
$query->whereHas('year.version.model', function ($q) use ($modelId) {
$q->where('id', $modelId);
});
}
}
public function scopeVersionId($query, ?int $value): void
{
if (!empty($value)) {
$query->whereHas('years.versions', function ($q) use ($value) {
$q->where('id', $value);
});
}
}
public function scopeYearId($query, ?int $value): void
{
if (!empty($value)) {
$query->whereHas('years', function ($q) use ($value) {
$q->where('id', $value);
});
}
}
// public function scopeGearId(\Illuminate\Database\Eloquent\Builder $query, ?int $value): void
// {
// if (!empty($value)) {
// $query->where('gear_id', '=', $value);
// }
// }
public function scopeFuelId(\Illuminate\Database\Eloquent\Builder $query, ?int $value): void
{
if (!empty($value)) {
$query->where('fuel_id', '=', $value);
}
}
/* MUTATORS */
public function setCodeAttribute(?string $value): void
{
$this->attributes['code'] = empty($value) ? null : $value;
}
public function setPriceAttribute(string $value): void
{
$this->attributes['price'] = toFloatHelper($value);
}
public function setKmAttribute(string $value): void
{
$this->attributes['km'] = $value === "0" || $value > 0 ? toFloatHelper($value) : null;
}
public function setColorAttribute(string $value): void
{
$this->attributes['color'] = strtolower($value);
}
public function setCompanyIdAttribute(string $value): void
{
$this->attributes['company_id'] = (int) $value > 0 ? $value : null;
}
/* ACCESSORS */
public function getTitleAttribute(): string
{
return "{$this->year->version->model->brand->name} {$this->year->version->model->name}";
}
public function users()
{
return $this->belongsTo(User::class);
}
public function transactions()
{
return $this->hasMany(Transactions::class, 'offered_vehicle');
}
public function vehicle_contacts()
{
return $this->hasMany(VehicleContacts::class, 'vehicle_id');
}
public function vehicle_optionals()
{
return $this->hasMany(VehicleOptionals::class, 'vehicle_id');
}
public function vehicle_photos()
{
return $this->hasMany(VehiclePhotos::class);
}
public function year()
{
return $this->belongsTo(Years::class, 'year_id', 'id', 'vehicles_api_global');
}
}
`
Here's the Year model: ` <?php
namespace App\Models;
/**
* Created by Reliese Model.
*/
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Spatie\Activitylog\Traits\LogsActivity;
/**
* Class Years
*
* @property int $id
* @property string $name
* @property int $status
* @property Carbon $created_at
* @property Carbon|null $updated_at
* @property string|null $deleted_at
* @property int $version_id
*
* @property Versions $versions
*
* @package App\Models
*/
class Years extends Model
{
use LogsActivity;
use SoftDeletes;
protected $connection = 'vehicles_api_global';
protected $table = 'years';
protected $casts = [
'status' => 'int',
'version_id' => 'int'
];
protected $fillable = [
'name',
'status',
'version_id'
];
public function version()
{
return $this->belongsTo(Versions::class, 'version_id', 'id');
}
/* SCOPES */
public function scopeName(\Illuminate\Database\Eloquent\Builder $query, $value): void
{
if (!empty($value)) {
$query->where('name', 'ilike', '%' . $value . '%');
}
}
public function scopeVersionId(\Illuminate\Database\Eloquent\Builder $query, ?int $value = null): void
{
if (!empty($value)) {
$query->where('version_id', '=', $value);
}
}
/* MUTATORS */
protected function setCodeAttribute(?string $value): void
{
$this->attributes['code'] = empty($value) ? null : $value;
}
}
`
The error I get is pretty straightforward, he can't connect to the right database during the query.
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'prestigeauto.years' doesn't exist (SQL: select * from
vehicleswherestatus= 1 and exists (select * fromyearswherevehicles.year_id=years.idand exists (select * fromversionswhereyears.version_id=versions.idand exists (select * frommodelswhereversions.model_id=models.idand exists (select * frombrandswheremodels.brand_id=brands.idand exists (select * fromtypeswherebrands.type_id=types.idandid= 1 andtypes.deleted_atis null) andbrands.deleted_atis null) andmodels.deleted_atis null) andversions.deleted_atis null) andyears.deleted_atis null) andvehicles.deleted_atis null order byvehicles.created_atdesc limit 4)
I tried to specify the connection in the models, I tried to join them but failed because of the difference in databases. I tried to do 2 separe querys in the spot, but didn't work either.
Upvotes: 0
Views: 71
Answers (1)
Reputation: 4373
If you are working with two different databases (not just schemas within the same database), you will need to establish two separate connections. This is because SQL queries execute within a single connection, meaning you cannot reference two databases or connections in a single query. Additionally, it's not possible to define foreign keys between two separate databases at the database level.
As a result, you cannot directly define relationships between Eloquent models if their underlying tables reside in separate databases. Instead, you’ll need to handle them independently by running separate queries. Laravel allows you to assign a specific connection to each model.
However, if by "database" you actually mean a different schema within the same server, then it's possible to work across schemas. In this case, you can specify the schema name as part of the table name in your model.
If you're working with two connections, you won’t be able to use Laravel’s built-in relationship features (e.g., hasOne, belongsTo). However, you could still encapsulate the logic within the model by defining custom methods. Example:
class Car extends Model
{
protected $connection = 'connection1';
}
class Quote extends Model
{
protected $connection = 'connection2';
public function getCarDetails(): ?Car
{
return Car::where('model_number', $this->model_number)->first();
}
}
Upvotes: 0
Related Questions
- Laravel/Ardent - on save(), error: Relationship method must return an object of type Illuminate
- laravel select where and where condition
- Laravel One to Many relationship not working - returns recursion
- use indirect relation when intermediate model is empty
- Laravel 5 Global Scope: injecting variable
- Laravel eloquent big difference in speed and amount of content returned
- How can I establish a relationship a Model class with another model class of folder in laravel eloquent??
- Multiple where conditions on eloquent model with optional where on relationship