eloquent-power-joins

eloquent-power-joins copied to clipboard

Versions of packages used and PHP. PHP: 8.1 Laravel: 9.16 Eloquent-power-joins: 2.6.4

If I set an alias to the main model from which the relationships originate, the relationships fail, because they refer to the name of the model's table, instead of the alias if it exists. At all times I see that reference is made to getModel()->getTable(), that is why the table is always searched, when I think that reference should be made to getQuery()->from, which contains the name of the model table or, failing that, the name with the defined alias. In case I am wrong in referring to the way to get the alias, then use the best way to get the name of the table with its alias if it exists. In my case I like to use aliases, because sometimes the names of the tables are very long and it is very inconvenient to write the name several times when using relationships.

Successful example

$data = ProviderProduct::query()
   ->joinRelationshipUsingAlias('provider', 'p')
   ->joinRelationship(
       'productVariation.parentProduct',
       [
           'productVariation' => fn($join) => $join->as('pvp'),
           'parentProduct' => fn($join) => $join->as('pr'),
       ]
   )
   ->select(
       [
           'provider_products.id AS provider_product_id',
           'provider_products.provider_id',
           'p.company_id',
           'provider_products.product_brand_id',
           'provider_products.product_variation_product_id AS variation_id',
           'pvp.product_id',
           'provider_products.provider_code',
           'provider_products.cost AS provider_cost',
           'provider_products.price AS provider_price',
       ]
   )
   ->get();

Failed example

$data = ProviderProduct::from('product_providers', 'pp')
   ->joinRelationshipUsingAlias('provider', 'p')
   ->joinRelationship(
       'productVariation.parentProduct',
       [
           'productVariation' => fn($join) => $join->as('pvp'),
           'parentProduct' => fn($join) => $join->as('pr'),
       ]
   )
   ->select(
       [
           'pp.id AS provider_product_id',
           'pp.provider_id',
           'p.company_id',
           'pp.product_brand_id',
           'pp.product_variation_product_id AS variation_id',
           'pvp.product_id',
           'pp.provider_code',
           'pp.cost AS provider_cost',
           'pp.price AS provider_price',
       ]
   )
   ->get();

Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 
'provider_products.product_variation_product_id' in 'on clause' (SQL: select `pp`.`id` as `provider_product_id`, `pp`.`provider_id`, 
`p`.`company_id`, `pp`.`product_brand_id`, `pp`.`product_variation_product_id` as `variation_id`, `pvp`.`product_id`, `pp`.`provider_code`, 
`pp`.`cost` as `provider_cost`, `pp`.`price` as `provider_price` from `provider_products` as `pp` inner join `providers` as `p` on 
`provider_products`.`provider_id` = `p`.`id` inner join `product_variation_products` as `pvp` on 
`provider_products`.`product_variation_product_id` = `pvp`.`id` inner join `products` as `pr` on `pvp`.`product_id` = `pr`.`id` limit 5)'

On the other hand, I wanted to comment that in the PowerJoins trait on lines 139 and 139 the variable $alias is repeated.

138     $alias = $this->getAliasName($useAlias, $relation, $relationName, $relation->getQuery()->getModel()->getTable(), $callback);
139     $alias = $useAlias ? $this->generateAliasForRelationship($relation, $relationName) : null;

I also ran into this same issue with SoftDeletes as well.

Any update to this BUG?

It seems that you are encountering an issue with the Eloquent-power-joins package when using an alias for the main model in a query with relationships. This is likely because the package is using the name of the model's table to build the relationship join clauses, instead of using the alias that you defined.

One potential solution to this issue could be to override the getTable method in your model class to return the alias name that you have defined, instead of the actual name of the table.

Here is an example of how you could do this:

use Illuminate\Database\Eloquent\Model;

class MyModel extends Model
{
    protected $alias;

    public function setTable($table, $alias = null)
    {
        parent::setTable($table);

        if ($alias) {
            $this->alias = $alias;
        }
    }

    public function getTable()
    {
        return $this->alias ?: parent::getTable();
    }
}

Then, when defining your query, you can use the setTable method to set both the name of the table and the alias, like this:

$data = MyModel::from('my_table', 'mt')
   ->setTable('my_table', 'mt')
   ->joinRelationshipUsingAlias('other_model', 'om')
   ->joinRelationship(
       'related_model.another_related_model',
       [
           'related_model' => fn($join) => $join->as('rm'),
           'another_related_model' => fn($join) => $join->as('arm'),
       ]
   )
   ->select(
       [
           'mt.id AS my_model_id',
           'mt.field1',
           'om.field2',
           'rm.field3',
           'arm.field4',
       ]
   )
   ->get();

This should allow the Eloquent-power-joins package to use the correct alias when building the relationship join clauses, and resolve the issue that you are experiencing.

I hope this helps! Let me know if you have any further questions or need more assistance.

Could you please verify if this is still an issue with the 3.0.0 version?

Feel free to reopen this issue if it is