Connection prefix is not used in subqueries

[jualoppaz]

Hi!

I have used your library recently and I only miss a little feature. An example of my project subqueries is the next:

$query = $query
    ->whereHas('user', function($q) use ($email){
        $q->where('user_email', 'like', '%' . $email . '%');
    });

However, my users table is a WordPress table, which requires the prefix property in database connection:

'connections' => [
    env('DB_WORDPRESS_CONNECTION') => [
        'driver' => 'mysql',
        'host' => env('DB_WORDPRESS_HOST'),
        'port' => env('DB_WORDPRESS_PORT'),
        'database' => env('DB_WORDPRESS_DATABASE'),
        'username' => env('DB_WORDPRESS_USERNAME'),
        'password' => env('DB_WORDPRESS_PASSWORD'),
        'unix_socket' => env('DB_WORDPRESS_SOCKET'),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => 'wp_',
        'strict' => true,
        'engine' => 'InnoDB',
    ],
    // other database connections
],

So I must indicate the prefix inside the table name in subquery.

With Eloquent native version my subquery was like this:

$query = $query
    ->whereHas('user', function($q) use ($email){
        $q->from('wordpress.wp_users as users')->where('user_email', 'like', '%' . $email . '%');
    });

And now with your library I only can avoid indicating the connection:

$query = $query
    ->whereHas('user', function($q) use ($email){
        $q->from('wp_users as users')->where('user_email', 'like', '%' . $email . '%');
    });

If were possible using prefix property of database connection it would be fantastic :)

[jualoppaz]

I have just done a pull request with my proposal: https://github.com/hoyvoy/laravel-cross-database-subqueries/pull/2

[maguilar92]

@jualoppaz Thanks for contributing. I will review your request to see the best way to add functionality.

[maguilar92]

@jualoppaz You need to update at 5.6.1 version to have subquery prefix support.

Thanks!

[jualoppaz]

Hi Mario!

I can see you didn't add the alias in the code. I added it because the relationship in my model was called user, meanwhile the table name is wp_users.

My model is like this:

class Client extends BaseEntity{

    //...

    protected $table = 'clients';

    public function user(){
        return $this->belongsTo('Backoffice\User\User', 'user_id', 'id');
    }

    //...

}

If I make a query with this:

$query = $query
    ->whereHas('user', function($q) use ($email){
        $q->where('user_email', 'like', '%' . $email . '%');
    });

Eloquent throws the next error:

Column not found: 1054 Unknown column 'users.id' in 'where clause' (SQL: select count() as aggregate from clients where exists (select from wordpress.wp_users where clients.user_id = users.id and user_email like %email@gmail.com%

I supose that Eloquent uses the relationship name for build the table access in the query, so the prefix is not used in where clause.

I think there are three ways for avoid this error:

• Keep from method in query builder with the alias for table (so this fix wouldn't bring any benefit).
• Include the table prefix in the relationship name (I think it's not very elegant and it would be coupled)
• Include an alias for the table without the table prefix (this solution requires that relationship name agrees with table name but prefix).

Can you think of a better option?

Thank you sou much.

[maguilar92]

@jualoppaz It's now released on 5.6.3. Only withCount using prefixes is not supported.