Husniddin's avatar

Multiple Connection Model Filter

I have 2 databases. 1st is for storing all information and 2nd one is for storing logs Second one has log table with user_id column First one has users table with id

I connected both of them like

    public function user()
    {
        return $this->setConnection('pgsql')->belongsTo(WebAppUser::class);
    }

Now relation working I can get user information like $model->user->first_name

But How can I filter them

Where or WhereHas also not working Showing error

SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table
0 likes
6 replies
LaryAI's avatar
LaryAI
AI
3 years ago
Level 58

To filter the relation between two models that are connected to different databases, you can use the with method and pass a closure to it. Inside the closure, you can specify the conditions for the relation using the where method. Here's an example:

$logEntries = LogEntry::with(['user' => function ($query) use ($userId) {
    $query->where('id', $userId);
}])->get();

In this example, LogEntry is the model for the log entries table, and user is the relation to the users table in the other database. The closure passed to with specifies that we only want log entries that are related to the user with the given $userId.

Note that you need to use the fully qualified class name for the related model in the closure, like this:

use App\Models\OtherDatabase\WebAppUser;

$logEntries = LogEntry::with(['user' => function ($query) use ($userId) {
    $query->where('id', $userId);
}])->get();

This assumes that the WebAppUser model is in the App\Models\OtherDatabase namespace.

Snapey's avatar

You have these databases on different servers?

If not, why bother with all this extra complexity?

Husniddin's avatar

@Snapey Both DBs are in one serve

What you mean by telling extra complexity It there doing this easy way or you mean why I'm seperating DBs?

Snapey's avatar

@Husniddin yes, why separate databases when its the same DB process, the same memory and the same cpu load

Husniddin's avatar

I tried like

        $items = WebAppLog::query()
            ->withWhereHas('user', function ($query) {
                $query->where('id', 2);
            })->get()->toArray();

            dd($items);

not working

$builder->where('user.first_name', '=', 'John');

also not working

Please or to participate in this conversation.