Laravel - Where on relation from different database

Question

I am trying to apply where condition on joined table, but I'm getting this error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'cts.plant' doesn't exist (SQL: select * from tbl_complaint where exists (select * from tbl_plant where tbl_complaint.made_in_plant = tbl_plant.plant_id and sap_code = 99999) order by created desc limit 50 offset 0)

My model classes looks something like this:

Main Model

    class Complaint extends Model {
        protected $connection= 'first';
        protected $table = 'complaint';
        protected $primaryKey = 'complaint_id';

    public function  customerPlant() {
        return $this->hasOne(Plant::class, 'plant_id', 'customer_plant_id')
                    ->select('plant_id', 'sap_code', 'plant_name');
    }
}

Connected Model

class Plant extends Model {
    protected $connection= 'second';
    protected $table = 'plant';
    protected $primaryKey = 'plant_id';

    public function getKeyName() {
        return 'plant_id';
    }
}

Data retrieval:

    $query = Complaint::with([
        'madeInPlantId']
    $query = Complaint::whereHas('madeInPlant', function($query){
        $query->where('sap_code','=','99999');
    });
    $query->get();

I think that problem is I'm not specifying that connected table is in another database.

why do you need connection strings ? Also, i need to say, without wherehas clausule everithing works fine, im getting record with joined data from another table — Miro Hascic
because, i just want to confirm something, we have a setup similar to yours before. the tables are on one server but different databases, all we have to do is prefix the database name to the table name protected $table = 'database1.plant'; — Joe
Can you include the error as text in the question? Dark text in tiny font on black background is quite unreadable — Joni

Joe Joe · Accepted Answer · 2018-12-04T07:15:26

if your tables are on the same server but different databases, you can do this.

class Complaint extends Model {
    protected $table = 'cts.complaint';
    protected $primaryKey = 'complaint_id';

    public function  customerPlant(){
        return $this->hasOne(Plant::class, 'plant_id', 'customer_plant_id')
                ->select('plant_id', 'sap_code', 'plant_name');
    }
}

class Plant extends Model{
    protected $table = 'otherdatabase.plant';
    protected $primaryKey = 'plant_id';

    public function getKeyName() {
        return 'plant_id';
    }
}

Laravel - Where on relation from different database

1 Answers