Laravel whereHas through two separate databases

Question

Problem: I want to get Customers only if they have already placed an order for the current year. Clients and orders are located on two separate Databases (this cannot be changed). All relationships are set up and working correctly, but when I try to do the following, I continue to receive an SQL error as I try to find "orders" in the "customers" database. Is there a way to get Laravel to use the correct database in this scenario?

$customers = $customers->whereHas('orders', function($query){ $query->where('academic_year_id', '=', $this->current_academic_year->id); }); $customers = $customers->orderBy('DFKEY','ASC')->get(); 

Order Model:

 public function customer() { return $this->belongsTo('Customer','dfkey'); } 

Customer Model:

 protected $connection = 'mysql2'; public function orders() { return $this->hasMany('Order','dfkey','DFKEY'); } 

Thanks in advance!

Answer 1

Solved this with a filter:

 public function index() { $customers = new Customer; // Make sure customers are current parents of students $customers = $customers->whereHas('students', function($q) { $q->where('STATUS', '=', 'FULL'); }); //Filter results if(Input::get('academic_year') == 'ordered'){ $customers = $customers->orderBy('DFKEY','ASC')->get(); $filtered = $customers->filter(function($customer) { if ($customer->orders()->where('academic_year_id','=',$this->current_academic_year->id)->first()) { return true; } }); $customers = $filtered; return View::make('admin.customers.index',compact('customers')); } $customers = $customers->orderBy('DFKEY','ASC')->get(); return View::make('admin.customers.index',compact('customers')); } 

Answer 2

Try to write such a query and put your database and tab name as it is →

 $customers = Schema::connection('your_database_name')::table('respective_table_name') ->where('academic_year_id', '=', $this->current_academic_year->id) ->orderBy('DFKEY','ASC') ->get(); 

Answer 3

I solved this problem by adding models of the "join" and "table" variables that define the database in which this model is stored.

For example, I have a model called "User" in a database called "core_database" in the user table. On the other hand, I have a model named "UserEvents" in the database named "logs_database" in the table "user_events"

Therefore, I will have two connections in the config / database.php file:

 'core_db_connection' => [ 'driver' => 'mysql', 'host' => host_ip, 'port' => port, 'database' => 'core_database', 'username' => username, 'password' => password, .... ], 'logs_db_connection' => [ 'driver' => 'mysql', 'host' => host_ip, 'port' => port, 'database' => 'logs_database', 'username' => username, 'password' => password, .... ], 

And the models will be similar:

 class User extends Authenticatable { protected $table = 'core_database.users'; protected $connection = 'core_db_connection'; ... } class UserEvents extends Model { protected $table = 'logs_database.user_events'; protected $connection = 'logs_db_connection'; ... } 

This has been verified in databases on the same database server. Database connections have the same ip host. I have not tried another way

Using this configuration, I can make any query to two or more separate databases on the same database server, in my case, RDS.

I hope this helps you!