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'));
}

Answer 3

This is actually a very good question! Simple queries directly on that model won't be a problem because Laravel uses the default connection from the model, but if you have a related model in another database and you execute some advanced queries, like: whereHas('relationName'), Laravel will use the connection of parent which will cause the SQL error about non existing column (because it looks up in wrong database).

This is the best solution for this problem:

In your related model make a constructor like this:

public function __construct(array $attributes = [])
{
    $this->table = 'db_name.'.$this->table;
    parent::__construct();
}

Answer 4

Unfurtanelly there is no direct way, but you can do it in 2 steps, which is kind of the same way that Laravel does it normally.

Its simple, clean, and efective, also you dont mess with core Laravel functions that may change on an update

    $orders = Order::where('academic_year_id', $this->current_academic_year->id)
                     ->select('id')
                     ->pluck('id');


    $customers = $customers->whereIn('order_id', $orders)
                           ->orderBy('DFKEY','ASC')
                           ->get();

Hope this works for you.

P.D.: you can skip the equal sign in where

Answer 5

Package hoyvoy/laravel-cross-database-subqueries allows you to do that for databases in the same server. You also need to specify all database connections and models related to them as specified by Agus Trombotto. Finally the model refering to another database must extend from Hoyvoy\CrossDatabase\Eloquent\Model instead of Illuminate\Database\Eloquent\Model.

Answer 6

It's safer to just modify the original getTable function without overwriting the constructor:

public function getTable()
{
    return join('.', [
        $this->getConnection()->getDatabaseName(),
        Str::snake(Str::pluralStudly(class_basename($this))) // original function
    ]);
}

That way functionality of dynamic database switching would work, like Model::on($connection).

Answer 7

Try to write query like this and put your database and tablename 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 8

I 've solved this by adding 'connection' and 'table' variables models specificing the database in wich the model is saved.

For example, i have a model called 'User' in the database called 'core_database' in the table users. In the other side, i have a model called 'UserEvents' in the database called 'logs_database' in the table 'user_events'

So i will have a two conections on 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 like:

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 was tested in databases in the same database server. Database connections have the same host ip. I have not tried with a different way

Using this configuration, i can make any query across two or more separeted database in the same database server, in my case, RDS.

I hope this help you!