Laravel Eloquent finding models with more than one relation to another model on another table

Question

I have two models using different tables on two different connections, User and UserInfo.

User has a UserInfo hasMany relation:

public function userInfo()
{
    return $this->hasMany('path\to\UserInfo','User_ID');
}

and UserInfo has a User belongsTo relation:

public function user()
{
    return $this->belongsTo('anotherpath\to\User', 'User_ID', 'User_ID');
}

I would like to find the first user with more than one UserInfo, however I believe that because they are on different tables on the database I am having issues.

This call

$patient = Patient::with('UserInfo')
    ->withCount('UserInfo')
    ->having('UserInfo_count', '>', 1)
    ->first();

Works as expected for other relations and is what I am trying to achieve, but not with this one. The difference being that the two models are on different tables. When I call this in Tinker, I get the error:

Illuminate/Database/QueryException with message 'SQLSTATE[42S02]:
Base table or view not found: 1146 Table '(TableName).UserInfo'
doesn't exist (SQL: select `User`.*, (select count(*) from `UserInfo`
where `User`.`User_ID` = `eob`.`User_ID`) as `UserInfo_count `User`
having `UserInfo_count` > 1 limit 1)'

Any ideas? I'm very new to eloquent and Laravel in general, sorry if I've gotten any terminology wrong or am missing something simple. Thanks!

Models are always on different tables, that's how it works. I think you mean they are on different databases. Use `setConnection()` in your relationship definition, as in the duplicate. Also note that your code might be better written as `$patient = Patient::whereHas("userInfo")->with("userInfo")->first();` — miken32, Dec 28 '20 at 16:36

Akash Kumar Verma · Answer 1 · 2020-12-28T16:26:03.013

maybe your table names are not defined properly as standard. so you can use table property to bind table name in model.

what is the standard to define table name.

Illuminate/Database/Eloquent/Model.php

/**
 * Get the table associated with the model.
 *
 * @return string
 */
public function getTable()
{
    if (isset($this->table)) {
        return $this->table;
    }

    return str_replace('\\', '', Str::snake(Str::plural(class_basename($this))));
}

Example

Table            Model
users            User
user_profiles    UserProfile

Alternative

in your UserInfo model

protected $table = 'your table name';

In more thing you don't need to add with() method and with withCount() method.

$patient = Patient::withCount('UserInfo')->having('UserInfo_count', '>', 1)->first();

Laravel Eloquent finding models with more than one relation to another model on another table

1 Answers1

what is the standard to define table name.

Example

Alternative