large(r) dataset - Fatal error: Allowed memory size of

Archives Yii 1.0.x Discussions
1

hello,

I'm trying to move an older site/database into Yii, so I already have the DB built.

Let's say I have groups, and the groups have members. I have a group list page where I'd display all the groups and the member count.

If the limit is larger than 5, PHP dies with a Fatal Error:

Fatal error: Allowed memory size of 16777216 bytes exhausted (tried to allocate 624 bytes) in C:xampplitehtdocsyiiframeworkdbarCActiveRecord.php on line 607

It's probably the way I'm doing the count, so here it is:

I set up my group model:

'members' => array( self::HAS_MANY, 'member', 'groupID' ),

and I'm trying to access the number of results in the view:



foreach( $groups as $group )


{


  ...


  echo sizeof( $group -> members );


  ...


}

I’m pretty sure there is a much better way to do this (ie: SQL count!?) or something similar. Or maybe even fancier Yii way to do it :)

thanks,

–iM

2

If you only need to know the member count for each group, you may consider the STAT relation (see the guide about relational AR).

Your memory limit may need to be increased. Depending on how many PHP extensions you enabled, the limit 16MB may be a bit small.

3

I think I have mine set to 64mb. I ran into the same problem once.

4

How many rows and columns are returned this sql outside php?

5

If you are executing thousands of SQLs (usually in command line), you should set YII_DEBUG to be false so that the trace messages are not logged. The trace messages are the major cause of memory consumption in such case.

6

hello,

yes, I have 1000s of records :) the reason why I have my DEBUG turned ON is because I’d like to see (in development mode) the actual SQL query that I’m trying to execute.

I'm fairly new to this "BELONGS_TO and HAS_MANY" syntax and until I figure out how it really works, I'd like to check that I'm executing the right SQL queries…

thanks,

–iM

7

Having 1000s of records is not a problem. Are you executing 1000s of SQLs in a single request? Usually such memory exhaustion is caused by accumulative logging messages.

8

Keep in mind, if you don’t use eager loading every 

  …

  echo sizeof( $group -> members );


  …

performs an sql query.

9

thanks [yoshi] I dropped that solution already…

here is what I'm dealing with:

  • 2 tables


+---------+   +----------+


+ table1  +   +  table2  +


+---------+   +----------+


+id       +   +id        +


+foreign1 +   +foreign1  +


+foreign2 +   +foreign2  +


+---------+   +----------+

and these 2 tables are simply tied together with foreign1 and foreign2.

and when I'm trying to use the SELF_STAT or HAS_MANY relations, I get weird results and it looks like that Yii is trying to joing them like this:

LEFT JOIN table2 ON table1.foreign1=table2.id

which will end up being wrong. I kinda understand what it's trying to do, but with my table design it won't work.

should I use findBySql or re-design the table structure (I don’t really want to)?

thanks,

–iM

10

Hi,

well, i don’t really understand that structure… :-[

What would the plain sql statement look like that you would use to get the results you want?

Is your relation between foreign1 -> foreign1??

11

don’t even ask :) this is an old(er) structure we have to work with, and move the app to a newer platform (ie: Yii)

they used to go through table1 with a foreach() and query the table2 every time.

so instead I just want to join the second table and count the members. (table1=groups, table2=members) joined by the keys (foreign1=foreign1 AND foreign2=foreign2)

maybe I'm gonna have to try a different approach!?

–iM

12

alright:

here is the QUERY I'm executing with Yii:



SELECT 


`groups`.`index` AS `t0_c0`, t1.`name` AS `t1_c2`,


t1.`desc` AS `t1_c4`, t1.`members.index` AS `t1_c13` 





FROM `groups` 





LEFT OUTER JOIN `members` t1 ON (t1.`userID`=`groups`.`index`) 


AND


(t1.`groupID`=`groups`.`index`) 





WHERE (`groups`.`index` IN (8, 15, 13, 11,


12))

and I'd need this instead:



SELECT 


`groups`.`index` AS `t0_c0`, t1.`name` AS `t1_c2`,


t1.`desc` AS `t1_c4`, t1.`members.index` AS `t1_c13` 





FROM `groups` 





LEFT OUTER JOIN `members` t1 ON (t1.`userID`=`groups`.`userID`) 


AND


(t1.`groupID`=`groups`.`groupID`) 





WHERE (`groups`.`index` IN (8, 15, 13, 11,


12))

so as you can see, it's trying to JOIN the members table on the index instead of my userID and groupID (which would totally make sense unser "normal" surcamstances!)

so the question is: is there a way to tell Yii, hey join this 2 tables, but use these keys combined!

thanks,

iM

13

well, i think it's not possible that way.

But i'm trying to understand the logic behind this structure, but i don't get it!

Why does a group have another groupid and also a memberid? And why does a member have another memberid and a groupid… this is pretty strange…

14

i think originally they didn't have an auto_inrement value as an ID (they put it in later) so it's kinda confusing.

btw, I ended up hand-crafting the SQL query for now and access the fields through findAllBySql - hopefully that will do the trick…

… so I ran the above mentioned function (where I’ll have a calculated cell called … calculated_field) but I can’t access it through my model()

so i say:

$groups = Group::Model() -> myFunc(); // myFunc returns the weird query with findAllBySql





foreach( $groups as $group )


{


  echo $group -> calculated_value;


}





.....





CException


Description





Property "Group.calculated_value" is not defined.

–iM

15

ps:

it seems like it returns everything (*) instead of the fields I asked for in the findAllBySql function …

16

had to add a public variable called: $calculated_value!

it is still weird, why it's returning all the columns (*) rather just the ones I'm asking for. Is that because the app is in development mode? (trace, logging is turned ON!)

thanks,

–iM

17

yes, that's right. You need a public accessible variable in your model for every field of your model. The variables for the fields from your database table are generated automatically - so you don't "see" them.

Quote

it is still weird, why it's returning all the columns (*) rather just the ones I'm asking for.

You're working with active records and using findAllBySql() will return a set of active records and therefore each record is filled with it's data.

If you want to use a direct SQL-Query you can use something like:

$sqlQuery = 'SELECT ... ';


$command=Yii::app()->db->createCommand(sqlQuery);


$groups= $command->queryAll();

Take a look at: http://www.yiiframew…de/database.dao

Greets