Failing where clause if the table name does reference to a DBLINK

[jbaron-mx]

Hello friends.

Hope someone could help me. I'm currently facing a problem if I set a table on my model that reference to a db-link.

This is my model:

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model 
{
protected $table = 'master.USERS@tomlink.tomcom'; # Here is the reference to a db-link tomlink.tomcom
}

It works well if I try to select data only.

return User::all(); # This works perfectly

But if I try to find some record by its id, it fails because Eloquent tries to concatenate the table name with the id field, which is wrong because the db-link is between them:

# Try to find user with id=1
return User::find(1); # It fails

Returns:

Error Message : ORA-04054: database link string TOMLINK.TOMCOM.ID does not exist Statement : select t2.* from ( select rownum AS "rn", t1.* from (select * from master.USERS@tomlink.tomcom where master.USERS@tomlink.tomcom.id = :autoparam0) t1 ) t2 where t2."rn" between 1 and 1 Bindings : [2](SQL: select t2. from %28 select rownum AS "rn", t1. from %28select * from master.USERS@tomlink.tomcom where master.USERS@tomlink.tomcom.id = 1) t1 ) t2 where t2."rn" between 1 and 1)

That failing where clause it supposed to be like this:

where master.USERS.id@tomlink.tomcom = 1 # Good
# where master.USERS@tomlink.tomcom.id = 1 # Wrong

Is there any way to fix this?. Greetings.

[yajra]

@jbaron30, I think you can make a workaround for this by creating a database view to show the db-link table instead of using it directly. Then just reference the database view on your model. But this approach may only be useful for fetching records and not for create/update/delete.

[jbaron-mx]

Thanks yajra, I appreciate your response. Yeah that approach should do the job, unfortunately this is a production server of my client and I don't have the permissions to create o modify any table or views on the database, so I'm restricted to fetch records in that way only.

If someone needs a workaround to this, I had figure out which function was concatenating the table name with the field name and you have to override it as follows:

I've added a function to the OracleEloquent class from this package to override the functionality of the Model class:

    /**
     * Get the table qualified key name.
     * 
     * @return string
     */
    public function getQualifiedKeyName()
    {
        $pos = strpos($this->getTable(), '@');

        if ($pos === false) {
            return $this->getTable().'.'.$this->getKeyName();
        } else {
            $table = substr($this->getTable(), 0, $pos);
            $dblink = substr($this->getTable(), $pos);
            return $table.'.'.$this->getKeyName().$dblink;
        }

    }

Now is working any find method for my Eloquent model. Also note that now I'm using this OracleEloquent class on my model instead of the regular Model class.

use yajra\Oci8\Eloquent\OracleEloquent as Model;

Thank you. I hope in some point of the future Laravel take care of that kind the situations with OCI8.

[yajra]

Glad you were able to sort this out. Will add your fix on OracleEloquent. Thanks!

[yajra]

Your fix was released on v2.4.2. Thanks!