The Joins do not contemplate connections with other databases

[mcolominas]

trafficstars

The joins do not consider if the relationship they are pointing to belongs to another database (within the same server).

Also if they have different prefixes, just add the prefix of the model where the join is made.

[luisdalmolin]

The joins do not consider if the relationship they are pointing to belongs to another database (within the same server).

What would you expect to happen in this case?

Also if they have different prefixes, just add the prefix of the model where the join is made.

Can you elaborate more on this with a simple example? This seems like something that could get implemented, but I'd like to fully understand

[mcolominas]

For example, I have two databases on the same server, each database is configured in laravel with its connection and its own prefix.

I have to execute this code for it to work for me:

$userModel = new User();
$userTableName = $userModel->getTable();
$userDatabase = $userModel->getConnection()->getDatabaseName();
$userPrefix = $userModel->getConnection()->getTablePrefix();
$userFullTable = "`$userDatabase`.`$userPrefix$userTableName`";

Email::join(DB::raw($userFullTable), 'user_id', '=', DB::raw($userFullTable . '.id'));

//Output select * from PREFIX1_email inner join connection2.PREFIX2_users on user_id = connection2.PREFIX2_users.id

When i use joinrelationship:

Email::joinRelationShip('user')

//Output select PREFIX1_email.* from PREFIX1_email inner join PREFIX1_users on PREFIX1_email.user_id = PREFIX1_users.id

[luisdalmolin]

Yeah, the prefix can and should get automatically applied. The connection can be tricky because if it's not on the same DB server, it would fail. So maybe a way to opt-in to include the database as a prefix could be a solution here. I'll give some thinking on this.

[mcolominas]

If you don't want to complicate a lot, you could make the prefix and/or database have to be passed to it, eg:

->joinRelationShip('relation', function($join){
    $join->setPrefix('xxx')->setDatabase('xxx');
});

But the ideal is that it will detect it automatically.

And in case of having 2 databases on different servers, what I would do would be to compare the connection of one and the other and if they do not belong to the same server it would generate an exception

[robin-dongbin]

I have same issue.

[mcolominas]

@winter-ice Due to inactivity, I have modified the code a little, it is not tested in all aspects and it is not 100% implemented, but to perform joins for me it is more than enough, until they fix it, if you want, I can share the code with you.

[robin-dongbin]

I need it, thanks for your share

[mcolominas]

As I have said before, this may not work correctly in some cases, and in the case of using aliases, I have not done any tests, you can always modify the code to adapt it to your needs.

Create the following traid, and instead of using PowerJoins use the traid

use Closure;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Kirschbaum\PowerJoins\PowerJoins;

trait FixPowerJoins
{
    use PowerJoins;

    public function getFullTable()
    {
        $tableDatabaseName = $this->getConnection()->getDatabaseName();
        $tableTablePrefix = $this->getConnection()->getTablePrefix();
        return "{$tableDatabaseName}.{$tableTablePrefix}{$this->getTable()}";
    }

    public function scopePowerJoinHas(Builder $query, $relation, $operator = '>=', $count = 1, $boolean = 'and', Closure $callback = null): void
    {
        if (is_null($query->getSelect())) {
            $query->select(DB::raw(sprintf('%s.*', $query->getModel()->getFullTable())));
        }

        if (is_null($query->getGroupBy())) {
            $query->groupBy(DB::raw($query->getModel()->getFullTable() . '.' . $query->getModel()->getKeyName()));
        }

        if (is_string($relation)) {
            if (Str::contains($relation, '.')) {
                $query->hasNestedUsingJoins($relation, $operator, $count, 'and', $callback);

                return;
            }

            $relation = $query->getRelationWithoutConstraintsProxy($relation);
        }

        $relation->performJoinForEloquentPowerJoins($query, 'leftPowerJoin', $callback);
        $relation->performHavingForEloquentPowerJoins($query, $operator, $count);
    }
}

Create the following class that extends from use Kirschbaum\PowerJoins\Mixins\RelationshipsExtraMethods:

use Illuminate\Support\Facades\DB;
use Kirschbaum\PowerJoins\Mixins\RelationshipsExtraMethods as MixinsRelationshipsExtraMethods;
use Kirschbaum\PowerJoins\PowerJoinClause;

class RelationshipsExtraMethods extends MixinsRelationshipsExtraMethods
{
    /**
     * Perform the JOIN clause for the BelongsTo (or similar) relationships.
     */
    protected function performJoinForEloquentPowerJoinsForBelongsTo()
    {
        return function ($query, $joinType, $callback = null, $alias = null, bool $disableExtraConditions = false) {
            $joinedTable = DB::raw($this->query->getModel()->getFullTable());
            $parentTable = $this->getTableOrAliasForModel($this->parent, DB::raw($this->parent->getFullTable()));

            $query->{$joinType}($joinedTable, function ($join) use ($callback, $joinedTable, $parentTable, $alias, $disableExtraConditions) {
                if ($alias) {
                    $join->as($alias);
                }

                $join->on(
                    DB::raw("{$parentTable}.{$this->foreignKey}"),
                    '=',
                    DB::raw("{$joinedTable}.{$this->ownerKey}")
                );

                if ($disableExtraConditions === false && $this->usesSoftDeletes($this->query->getModel())) {
                    $join->whereNull("{$joinedTable}.{$this->query->getModel()->getDeletedAtColumn()}");
                }

                if ($disableExtraConditions === false) {
                    $this->applyExtraConditions($join);
                }

                if ($callback && is_callable($callback)) {
                    $callback($join);
                }
            }, $this->query->getModel());
        };
    }

    /**
     * Perform the JOIN clause for the Morph (or similar) relationships.
     */
    protected function performJoinForEloquentPowerJoinsForMorph()
    {
        return function ($builder, $joinType, $callback = null, $alias = null, bool $disableExtraConditions = false) {
            $builder->{$joinType}(DB::raw($this->getModel()->getFullTable()), function ($join) use ($callback, $disableExtraConditions) {
                $join->on(
                    DB::raw("{$this->getModel()->getFullTable()}.{$this->getForeignKeyName()}"),
                    '=',
                    DB::raw("{$this->parent->getFullTable()}.{$this->localKey}")
                )->where($this->getMorphType(), '=', $this->getMorphClass());

                if ($disableExtraConditions === false && $this->usesSoftDeletes($this->query->getModel())) {
                    $join->whereNull($this->query->getModel()->getQualifiedDeletedAtColumn());
                }

                if ($disableExtraConditions === false) {
                    $this->applyExtraConditions($join);
                }

                if ($callback && is_callable($callback)) {
                    $callback($join);
                }
            }, $this->getModel());

            return $this;
        };
    }

    /**
     * Perform the JOIN clause for the HasMany (or similar) relationships.
     */
    protected function performJoinForEloquentPowerJoinsForHasMany()
    {
        return function ($builder, $joinType, $callback = null, $alias = null, bool $disableExtraConditions = false) {
            $joinedTable = $alias ?: DB::raw($this->query->getModel()->getFullTable());
            $parentTable = $this->getTableOrAliasForModel($this->parent, $this->parent->getFullTable());
            $parentTable = DB::raw($parentTable);

            if (is_null($alias)) {
                $foreignKey = explode('.', $this->foreignKey);
                $foreignKey = $foreignKey[count($foreignKey) - 1];
                $foreignKey = DB::raw("{$joinedTable}.{$foreignKey}");
            } else {
                $foreignKey = $this->foreignKey;
            }

            $builder->{$joinType}(DB::raw($this->query->getModel()->getFullTable()), function ($join) use ($callback, $foreignKey, $joinedTable, $parentTable, $alias, $disableExtraConditions) {
                if ($alias) {
                    $join->as($alias);
                }

                $join->on(
                    $foreignKey,
                    '=',
                    DB::raw("{$parentTable}.{$this->localKey}")
                );

                if ($disableExtraConditions === false && $this->usesSoftDeletes($this->query->getModel())) {
                    $join->whereNull(
                        "{$joinedTable}.{$this->query->getModel()->getDeletedAtColumn()}"
                    );
                }

                if ($disableExtraConditions === false) {
                    $this->applyExtraConditions($join);
                }

                if ($callback && is_callable($callback)) {
                    $callback($join);
                }
            }, $this->query->getModel());
        };
    }

    /**
     * Perform the JOIN clause for the HasManyThrough relationships.
     */
    protected function performJoinForEloquentPowerJoinsForHasManyThrough()
    {
        return function ($builder, $joinType, $callback = null, $alias = null, bool $disableExtraConditions = false) {
            [$alias1, $alias2] = $alias;
            $throughTable = $alias1 ?: DB::raw($this->getThroughParent()->getFullTable());
            $farTable = $alias2 ?: DB::raw($this->getModel()->getFullTable());

            $builder->{$joinType}(DB::raw($this->getThroughParent()->getFullTable()), function (PowerJoinClause $join) use ($callback, $throughTable, $alias1, $disableExtraConditions) {
                if ($alias1) {
                    $join->as($alias1);
                }

                $join->on(
                    DB::raw("{$throughTable}.{$this->getFirstKeyName()}"),
                    '=',
                    $this->getQualifiedLocalKeyName()
                );

                if ($disableExtraConditions === false && $this->usesSoftDeletes($this->getThroughParent())) {
                    $join->whereNull($this->getThroughParent()->getQualifiedDeletedAtColumn());
                }

                if ($disableExtraConditions === false) {
                    $this->applyExtraConditions($join);
                }

                if (is_array($callback) && isset($callback[$this->getThroughParent()->getTable()])) {
                    $callback[$this->getThroughParent()->getTable()]($join);
                }
            }, $this->getThroughParent());

            $builder->{$joinType}(DB::raw($this->getModel()->getFullTable()), function (PowerJoinClause $join) use ($callback, $throughTable, $farTable, $alias1, $alias2) {
                if ($alias2) {
                    $join->as($alias2);
                }

                $join->on(
                    DB::raw("{$farTable}.{$this->secondKey}"),
                    '=',
                    DB::raw("{$throughTable}.{$this->secondLocalKey}")
                );

                if ($this->usesSoftDeletes($this->getModel())) {
                    $join->whereNull("{$farTable}.{$this->getModel()->getDeletedAtColumn()}");
                }

                if (is_array($callback) && isset($callback[$this->getModel()->getTable()])) {
                    $callback[$this->getModel()->getTable()]($join);
                }
            }, $this->getModel());

            return $this;
        };
    }

    /**
     * Perform the "HAVING" clause for eloquent power joins.
     */
    public function performHavingForEloquentPowerJoins()
    {
        return function ($builder, $operator, $count) {
            $builder
                ->selectRaw(sprintf('count(%s) as %s_count', "{$this->query->getModel()->getFullTable()}.{$this->query->getModel()->getKeyName()}", $this->query->getModel()->getTable()))
                ->havingRaw(sprintf('%s_count %s %d', $this->query->getModel()->getTable(), $operator, $count));
        };
    }
}

In the service provider, add the following in register:

use Illuminate\Database\Eloquent\Relations\Relation;

public function register()
{
    Relation::mixin(new RelationshipsExtraMethods);
}

[mcolominas]

An explanation of why I use the DB::raw() in case you want to implement this or something similar in powerjoin

If DB::Raw() is not used instead of generating <database>.<prefix><table> it would generate <prefix><database>.<prefix><table>

[robin-dongbin]

After some testing, I removed DB::Raw(),because it cause some issues in my side, now it works well. I just use different database but not use prefix. Seems it not an issue for me.

Thanks your your share again.

[Pablo1Gustavo]

Some updates? I miss this feature a lot

[luisdalmolin]

I'm playing around with some solutions with the time I got. This is not a simple change, so will not be quick, but I'm looking into it.

I could potentially add an "prefix" option in the meantime that would solve, as you would be able to specify a "prefix" (the other DB name) from the tables from other connections.