Laravel whereHas across two separate databases

Question

Problem: I want to get Customers only if they have already placed an order for the current year. Customers and Orders are on two separate Databases (this can't change). The relationships are all setup and working correctly but when I try the following I keep getting an SQL error as it is trying to search 'orders' on the 'customers' database. Is there anyway to force 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

Late to the party but for anybody else who has a similar issue, the below should work (as long as both databases are on a single server).

Set BOTH the connection and table explicitly.

protected $connection = 'mysql2';
protected $table_name = 'mysql2.orders';

Or if you want - dynamically set the table like this:

protected $table = 'orders';
public function __construct() {
    $this->table = DB::connection($this->connection)->getDatabaseName() . '.' . $this->table_name;
}

Or even

public function __construct() {
    $this->table = DB::connection($this->connection)->getDatabaseName() . '.' . $this->getTable();
}

Answer 2

Solved this by using 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'));
}