Oracle relations limit of 1000

[jonny7]

When trying to access a large models relations like so

Model::find()->with('relationName')->all();

I get the error, ORA-01795: maximum number of expressions in a list is 1000 error

I'm assuming that I haven't done anything wrong or missed something. Looking around, there appears to be a workaround for this: http://stackoverflow.com/a/17844383/895169

Is this something we should look to implement within Yii to help Oracle DBs or am I using Yii incorrectly?

[SilverFire]

Should be fixed, thank you for the report.
If you feel confident enough - you can help us by submitting a PR.

[samdark]

That's because of WHERE id IN(1,2,3,4,...). MySQL has alike limit as well. Usually it's not a problem because getting 1000 releated records often means something's very wrong. @jonny7 what's your use case for 1000 records on the same request?

[jonny7]

I have Table A with 10k procedure records and Table B is a pathology table with 7k rows. Not all procedures will have related pathology. I am using this mainly for generating statistics and I was just testing that the relation worked as I have found Oracle not as easy as MySQL to use, saw this and wondered about it being a bug or my mistake. Is my use case bad?

Out of interest then, If you have a large database, is active record not the way to go when accessing the data?

[samdark]

Yes. It doesn't really matter if it's AR or not. Getting more than 1000 records in a single request is generally not a good idea. It's not fast and memory greedy.

[SilverFire]

@samdark @cebe do you think it should be fixed?
As for me - yes, it should.

Getting more than 1000 records in a single request is generally not a good idea. It's not fast and memory greedy.

Yes, it's greedy, but sometimes a single SQL with 5000 expressions in the IN condition is much faster than 5 separated queries.

[klimov-paul]

Yes, it's greedy, but sometimes a single SQL with 5000 expressions in the IN condition is much faster than 5 separated queries.

This is no the point. The point is fetching 1000 records will consume too much memory for PHP script running. What the sense of fixing maximum number of expressions in a list error, just to see PHP memory overflow instead?

In general this is not a common use case. Processing of the large amount of data requires more care and attention.
We have implemented querying in batches for the large data-sets processing. As for me - this is enough. Creating a hack aroung IN SQL opeartion does not sounds good.

[aliechti]

For me it is also related to #10203, because most time the viaTable method is handy and nobody knows how to do it with the join method which would fix this issue most time for me.

I've encountered it because I need the count of some entries which are related to another entry.

public function getEntries()
{
    return $this->hasOne(Entry::className(), ['entryID' => 'entryID'])
        ->viaTable(EntryInList::tableName(), ['listID' => 'listID']);
}

Normally I would do a Join...

$model->getEntries()->count();

[jonny7]

I agree with @samdark and @klimov-paul comments about the memory overflow and that it isn't a good use case. I wrote this initially because I was having trouble with Oracle and wanted to just see if my relations worked. When I saw the error I just assumed it was an oracle not playing nicely with Yii. I have used batch insert previously with Yii2, but have never seen the retrieval piece? Is this is http://www.yiiframework.com/doc-2.0/yii-db-batchqueryresult.html ?

[resurtm]

@jonny7, check this method: http://www.yiiframework.com/doc-2.0/yii-db-query.html#batch%28%29-detail

There is also exists usage sample.

[SilverFire]

@klimov-paul well, I tend to agree with you.

[SilverFire]

Fixed by f6a4f3c