I have the model Property (for a real estate plugin), and the model Transaction (for sell, rent). One property can have many transactions throw table pronet_realestate_property_transactions. The filter work fine after refreshing the page, but duplicate the scope query when apply the filter, from ajax.

When apply the filter, prompt the SQL error

"SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'pronet_realestate_property_transactions' (SQL: select `pronet_realestate_properties`.*, (select pronet_realestate_types.name from `pronet_realestate_types` where `pronet_realestate_types`.`deleted_at` is null and `pronet_realestate_properties`.`type_id` = `pronet_realestate_types`.`id`) as `type`, (select group_concat(pronet_realestate_transactions.name separator ', ') from `pronet_realestate_transactions` inner join `pronet_realestate_property_transactions` on `pronet_realestate_transactions`.`id` = `pronet_realestate_property_transactions`.`transaction_id` where `pronet_realestate_property_transactions`.`property_id` = `pronet_realestate_properties`.`id`) as `transaction`, (select pronet_realestate_zones.name from `pronet_realestate_zones` where `pronet_realestate_zones`.`deleted_at` is null and `pronet_realestate_properties`.`zone_id` = `pronet_realestate_zones`.`id`) as `zone`, (select pronet_realestate_owners.name from `pronet_realestate_owners` where `pronet_realestate_owners`.`deleted_at` is null and `pronet_realestate_properties`.`owner_id` = `pronet_realestate_owners`.`id`) as `owner` from `pronet_realestate_properties` inner join `pronet_realestate_property_transactions` on `pronet_realestate_properties`.`id` = `pronet_realestate_property_transactions`.`property_id` inner join `pronet_realestate_property_transactions` on `pronet_realestate_properties`.`id` = `pronet_realestate_property_transactions`.`property_id` where `pronet_realestate_properties`.`deleted_at` is null and `pronet_realestate_property_transactions`.`transaction_id` in (2) and `pronet_realestate_property_transactions`.`transaction_id` in (2) group by `pronet_realestate_property_transactions`.`property_id`, `pronet_realestate_property_transactions`.`property_id` order by `updated_at` desc)" on line 600 of /home/byr/public_html/vendor/laravel/framework/src/Illuminate/Database/Connection.php

This is the duplicate sql after apply filter.

inner join `pronet_realestate_property_transactions` 
  on `pronet_realestate_properties`.`id` = `pronet_realestate_property_transactions`.`property_id` 
inner join `pronet_realestate_property_transactions` 
  on `pronet_realestate_properties`.`id` = `pronet_realestate_property_transactions`.`property_id` 
where `pronet_realestate_properties`.`deleted_at` is null 
and `pronet_realestate_property_transactions`.`transaction_id` in (2) 
and `pronet_realestate_property_transactions`.`transaction_id` in (2)

Filter config config_filter.yaml

scopes:
  transactions:
    label: Transacción
    scope: transaction
    modelClass: Pronet\RealEstate\Models\Transaction
    nameColumn: name

Scope function in Propertymodel

public function scopeTransaction(Builder $query, $transaction_id) {
    $table = 'pronet_realestate_property_transactions';
    $query->join($table, 'pronet_realestate_properties.id', '=', $table . '.property_id');
    if(is_array($transaction_id)) {
        $query->whereIn($table . '.transaction_id', $transaction_id);
    } else {
        $query->where($table . '.transaction_id', '=', $transaction_id);
    }
    $query->groupBy($table . '.property_id');
}

Any help will be appreciate.
Thanks

Last updated 10 years ago

I found the solution. The problem is a multiple call of scope method. The solution was very simple. In the scope model define a protected var

protected $is_scoped = false;

Them change the scope method like this

public function scopeTransaction(Builder $query, $transaction_id) {
    $table = 'pronet_realestate_property_transactions';
    if(!$this->is_scoped) {
        $query->join($table, 'pronet_realestate_properties.id', '=', $table . '.property_id');
        if(is_array($transaction_id)) {
            $query->whereIn($table . '.transaction_id', $transaction_id);
        } else {
            $query->where($table . '.transaction_id', '=', $transaction_id);
        }
        $query->groupBy($table . '.property_id');
    }
    $this->is_scoped = true;
}

Problem solved!

Uhul Thanks man, just got into the exact same problem. Works for me as well. Cheers.