framework: Eloquent/Model::withCount doesn't specify database/schema name

Laravel Version: 5.5.33
PHP Version: 7.1.8
Database Driver & Version: Mysql 5.7

Description:

Eloquent/Model::withCount doesn’t specify database/schema name

I have 2 models User and Problem. Their table locates in different database/schema in same server (users locates in connection mysql a.k.a. schema acm, problem locates in connection mysql2 a.k.a. schema boj in my case). I have defined protected $connection for these two models. Anything other works well. And when I call User::with('solved') it works well, but when calling User::withCount('solved'), the generated sql sentence doesn’t specify the problem table’s database/schema, which will cause an error.

(formatted)

(3/3) QueryException
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'acm.problem' doesn't exist (SQL:

select `users`.*, (
    select count(*) from `problem` 
    inner join `boj`.`solution` on `problem`.`problem_id` = `boj`.`solution`.`problem_id` 
    where `users`.`id` = `boj`.`solution`.`acm_user_id` and `boj`.`solution`.`result` = 4
) as `solved_count` 
from `users` order by `active_at` desc limit 150 offset 0)

Steps To Reproduce:

Defines

class User
{
    protected $connection = 'mysql';
    // some other defines 

    public function solved()
    {
        $mysql2 = config('database.connections.mysql2.database');
        return $this
            ->belongsToMany(Problem::class, "$mysql2.solution", 'acm_user_id', 'problem_id')
            ->wherePivot('result', 4);
    }
}

I believe it doen’t matter where the solution table locates and how I call it… Although it may looks ugly.

class Problem
{
    protected $connection = 'mysql2';
    protected $table = 'problem';
    // some other defines
}

Call

User::with('roles')->withCount('solved')->orderBy('active_at', 'desc')->paginate(150);

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 1
Comments: 31 (18 by maintainers)

Most upvoted comments

@yaim No. IMO, cross-database queries on SQLite are way too much of an edge case.

They are possible for file-based databases and apparently even for in-memory ones, but I don’t think that would work with Laravel.

staudenmeir on May 2, 2020

In principle, the package’s implementation is the right approach: https://github.com/hoyvoy/laravel-cross-database-subqueries/blob/master/src/Eloquent/Concerns/QueriesRelationships.php#L84

We compare the connections and prefix the subquery’s table name if they are different. I only find the temporary separator <--> to be a weird choice, not sure why they implemented it this way.

The trait also uses the same approach to fix the issue for has()/whereHas() subqueries. It makes sense to fix both issues in one go and extract the necessary code into a shared helper method.

We also need to consider the package’s open issues, especially this one.

staudenmeir on May 2, 2020

I have found a quick fix. I modified Illuminate\Database\Eloquent\Builder::setModel method like this:

    /**
     * Set a model instance for the model being queried.
     *
     * @param  \Illuminate\Database\Eloquent\Model  $model
     * @return $this
     */
    public function setModel(Model $model)
    {
        $this->model = $model;

        $this->query->from($model->getConnection()->getDatabaseName() . '.' . $model->getTable());
        // Original:
        // $this->query->from($model->getTable());

        return $this;
    }

What I did was force prepend the name of the database to the table name within FROM. The db name is obtained using the model’s connection name.

I made this change to fix #29125

I applied this change and so far my application is working fine and it does not seem to have broken anything that was working. The only issue I see is the rather longer queries.

I could create a PR if this fix does not break anything

alwintom on Jul 12, 2019

In the meantime, you can use this package: https://github.com/hoyvoy/laravel-cross-database-subqueries

staudenmeir on Jan 5, 2019

Thanks @ollieread!

driesvints on Aug 5, 2020

Send to 6.x please, thanks!

driesvints on Jul 28, 2020

I’ve done some testing on this, and I can’t recreate the issue you’re having @driesvints. The query created in QueriesRelationships::withCount() definitely has the new connection. The problem is that the query is turned into its raw SQL and added as a subquery.

Looking at it, the best solution I can see is one where the database name is appended to the from in Builder::parseSub(). The method could be made to be something like this.

protected function parseSub($query)
{
    if ($query instanceof self || $query instanceof EloquentBuilder || $query instanceof Relation) {
        if ($query->getConnection()->getDatabaseName() !== $this->getConnection()->getDatabaseName()) {
            $databaseName = $query->getConnection()->getDatabaseName();

			if (strpos($query->from, $databaseName) !== 0) {
				$query->from($databaseName .'.'.$query->from);
            }
        }

        return [$query->toSql(), $query->getBindings()];
    } elseif (is_string($query)) {
        return [$query, []];
    } else {
        throw new InvalidArgumentException(
            'A subquery must be a query builder instance, a Closure, or a string.'
        );
    }
}

I can’t think of anything prettier. This should have the benefit that it’ll work for any cross-database subqueries, as long as they’re not on totally different connections. It could perhaps check for the presence of . instead of the database name.

I can make a PR if you wish.

ollieread on Jul 28, 2020