Eager loading ORA-01795

[MuriloSo]

I have the 'Pedido' model that is in an oracle database, and the 'PedidoTeste' model that is in a mysql database. 'PedidoTeste' has 2000 records belonging to 'Pedido', when I try to retrieve all records from 'PedidoTeste' with the 'pedidos' belongsTo relationship I receive the error ORA-01795.

Here is my code of 'Pedido'

class PedidoTeste extends Model
{
    const ID = 'id';

    const PEDIDOS_RELATION = 'pedidos';

    protected $table = 'pedidos_teste';
    protected $primaryKey = self::ID;
    protected $fillable = [self::ID];
    public $timestamps = false;
    protected $with = [self::PEDIDOS_RELATION];

    public function pedidos(){
        return $this->belongsTo(Pedido::class, self::ID, Pedido::ID);
    }

}

The error is here:

dd(\App\Model\PedidoTeste::all());

System details

• Operating System: CentOS 7
• PHP Version: 7.1.25
• Laravel Version: 5.7.20
• Laravel-OCI8 Version: 5.7.2

[yajra]

Can you please provide the error dump on logs?

[lucastoneatto]

The same thing happens to me: Operating System: Windows server PHP Version: 7.1.3 Laravel Version: 5.7 Laravel-OCI8 Version: 5.7

The same problem in #73 but the solution not resolve this problem

[lucastoneatto]

[stickeerehan]

Can confirm #73 issue is occurring using:

Operating System: ubuntu18.04 PHP Version: PHP 7.1.26 Laravel Version: v5.7.28 Laravel-OCI8 Version: v5.7.4

Stacktrace for the issue is the following:

 at /var/www/cpw-partner-portal/vendor/yajra/laravel-pdo-via-oci8/src/Pdo/Oci8/Statement.php:183)
[stacktrace]
#0 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\\Database\\Connection->runQueryCallback('select \"NAME\", ...', Array, Object(Closure))
#1 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\\Database\\Connection->run('select \"NAME\", ...', Array, Object(Closure))
#2 /var/www/cpw-partner-portal/vendor/yajra/laravel-oci8/src/Oci8/Query/OracleBuilder.php(106): Illuminate\\Database\\Connection->select('select \"NAME\", ...', Array, true)
#3 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2083): Yajra\\Oci8\\Query\\OracleBuilder->runSelect()
#4 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2569): Illuminate\\Database\\Query\\Builder->Illuminate\\Database\\Query\\{closure}()
#5 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2084): Illuminate\\Database\\Query\\Builder->onceWithColumns(Array, Object(Closure))
#6 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(519): Illuminate\\Database\\Query\\Builder->get(Array)
#7 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/BelongsToMany.php(584): Illuminate\\Database\\Eloquent\\Builder->getModels()
#8 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Relations/Relation.php(144): Illuminate\\Database\\Eloquent\\Relations\\BelongsToMany->get()
#9 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(567): Illuminate\\Database\\Eloquent\\Relations\\Relation->getEager()
#10 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(536): Illuminate\\Database\\Eloquent\\Builder->eagerLoadRelation(Array, 'dealers', Object(Closure))
#11 /var/www/cpw-partner-portal/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(504): Illuminate\\Database\\Eloquent\\Builder->eagerLoadRelations(Array)
#12 /var/www/cpw-partner-portal/app/Http/Controllers/Portal/ImpersonateController.php(54): Illuminate\\Database\\Eloquent\\Builder->get()

Already extending the Oracle eloquent ( you can tell as it calls the OracleBuilder.php ), but it looks like when calling a relationship, it doesn't chunk the list.

Error message + snippet of the query generated:

Error Code    : 1795 Error Message : ORA-01795: maximum number of expressions in a list is 1000 Position      : 5906 Statement     : select "NAME", "DEALER_USER"."USER_ID" as "PIVOT_USER_ID", "DEALER_USER"."DEALER_ID" as "PIVOT_DEALER_ID" from "DEALERS" inner join "DEALER_USER" on "DEALERS"."ID" = "DEALER_USER"."DEALER_ID" where "DEALER_USER"."USER_ID" in (3, 6, ....

I've noticed quite a few issues raised regarding this - would be good to look into this if possible @yajra

[stickeerehan]

Further update:

Issue occurs when eager loading a relationship and the main model has more then a 1000 entries. Before:

User::select('id', 'first_name', 'last_name')
    ->with('posts')
    ->get();

Current workaround:

User::select('id', 'first_name', 'last_name')
    ->chunk(1000, function($collection) use (&$users) {
        $collection->load('posts');
        $users = $users->merge($collection);
    });

[renux91]

You need to replace the function "whereInRaw" with chunk in OracleGrammar. This worked for me. @yajra need to update this

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.