0

I'm using the eloquent method has() to retrieve only rows where the parent exists and is not deleted and it's working well.

But this doesn't work when am passing "user" as a parameter because the user is coming from a different connection.

I have set the connections in both models but am still getting a table not found error for users. Please how can I do to check if the parent from another database connects exist? For example.

My Course model

class Course extends Model
{
    protected $connection= "mysql";
    
    public function user(){
        return $this->belongsTo(User::class);
    }
}

My User model from another database connection "accounts".

class User extends Model
{
    protected $connection= "accounts";
    
    public function courses(){
        return $this->hasMany(Course::class);
    }
}

And in my CourseController, I want to do this.

class CoursesController extends Controller
{
   public function index(Request $request){
       $term = $request->get("query","");
       return Course::has("user")->where("name","like","%$term%")->paginate(12);
   }
}

This will work if the courses table and users table are in the same database but since there aren't am getting a table or view not found SQL error.

If there was a way to pass in the connection name in the has method it will be great but the has method only takes one parameter which is the model name.

I really need to get this working and my users are stored in another database.

Etta
  • 339
  • 4
  • 10
  • As far as I know it is not currently possible to bridge two different connections in this way. If your databases are on the same physical server you can probably use something like `protected $table = 'otherdbname.users'` to use the same connection and refer to a different schema, but there's no generic way to bridge different connections (since they can also be on different servers) – apokryfos Jul 17 '20 at 22:45
  • Why are they in different databases? Do they both have the same data? Is the data synchronized? This seems like an odd setup, unless it's an exercise. – Bill Doughty Jul 17 '20 at 22:49
  • @apokryfos I can actually do `$course->user` and it returns the user object though they are from different databases. – Etta Jul 17 '20 at 22:52
  • @BillDoughty the users are on a different database because the Course app is using users from another application used to do single sign-on for all other applications. – Etta Jul 17 '20 at 22:57
  • If the users are in sync on both databases, just use the sign-on user to look up the same user on the courses database. Then you have the user and courses on the same database. – Bill Doughty Jul 17 '20 at 23:05
  • Users are from another database. The goal here is to avoid selecting courses that users have been deleted. I use the same logic on many of my queries and it works but it doesn't work with user because the user is from another database. To remedy it, I commented the 'has' method and added withTrashed() on the user relation in the Course model. – Etta Jul 17 '20 at 23:17
  • Eager and lazy loading of relationships should still work for 1-1 or 1-n relationships because it will do a separate query however, `whereHas` or similar will not because that translates to a `SELECT * FROM users WHERE exists (related query)` which will not work on different databases. m-n relationships might or might not work depending which database the pivot is on. – apokryfos Jul 18 '20 at 06:31

0 Answers0