在CakePHP& amp;中返回很多行的MySQL [英] Returning a lot of rows in CakePHP & MySQL

问题描述

也许我在这里错过了一些简单的事情...或者也许我一直在对此进行编码，以至于我看不到我的问题，但是使用CakePHP(1.3)并执行选择(MySQL)应该是即使有2,000条记录也能够返回结果...对吗?

Maybe I'm missing something easy here...or maybe I've just been coding on this so long I can't see past my problem, but using CakePHP (1.3) and doing a select (MySQL) should be able to return results even if there are, say, 2,000 records... right?

$options = array(
    'conditions' => array(
        'Lead.campaign_id' => $campaign['Campaign']['id']
    ),
    'recursive' => -1,
    'order' => 'Lead.goal DESC',
    'contain' => false,
);

$leads = $this->Lead->find('all',$options);

但是，此查询给了我一个死亡的白屏(设置了 define('LOG_ERROR'，2); 设置).

Yet, this query gives me a white screen of death (with define('LOG_ERROR', 2); set).

如果我添加'limit'=>300，(或类似数字.不一定要是300)到 $ options 数组，然后它将提取结果，所以我知道查询本身不是问题.

If I add a 'limit' => 300, (or similar number..doesn't have to be exactly 300) to the $options array then it will pull results so I know the query itself isn't the problem.

此外，我尝试通过使用 $ options 数组中的'fields'参数来限制返回的列...甚至告诉它仅返回1字段，但这也不起作用.

Additionally, I've tried to limit the columns returned by using the 'fields' parameter in the $options array...even telling it to only return 1 field but that didn't work either.

我也通过它回显了它使用的SQL语句

I've also echoed out the SQL statement that it uses via

$log = $this->Lead->getDataSource()->getLog(false, false);
debug($log);

当然，我必须把'limit'放入其中(否则我只会看到死亡的白屏).然后，我将该查询(无限制)复制/粘贴到phpMyAdmin中，它返回的记录很好(即使显示1000行").这样做使我相信系统 可以处理查询&输出，但也许我在假设我不应该这样做.

Of course, I had to put the 'limit' in for that to show up (otherwise I just get the white screen of death). Then I copy/paste that query (without the limit) into phpMyAdmin and it returns the records just fine (even with 'show me 1,000 rows' on). Doing this leads me to believe that the system can handle the query & output but maybe I'm making an assumption I shouldn't be.

我尝试获取所有行而不对记录进行分页的原因是因为我需要将它们输出到csv文件中以供用户下载(这部分已经在工作... 何时查询有效).

The reason I'm trying to get all rows and not paginate the records is because I need to output them to a csv file for download by the user (this part is already working... when the query works).

有人建议我说这可能是超时问题，我应该提出这个问题，但这对我来说似乎是一个hack，必须有一个更好的解决方案.

It was suggested to me that it's probably a timeout issue and I should just raise that but that seems like a hack to me and there must be a better solution.

由于我正在下载一个csv文件，也许我可以通过某种方式将查询流式传输到一个文件中，然后下载该文件?也许还有更好的解决方案?

Since I'm doing a download of a csv file maybe there's a way for me to stream the query to a file that then gets downloaded? Or perhaps there's even a better solution?

推荐答案

您的内存可能已用完(请检查您的Web服务器错误日志)-Cake会将所有2000行加载到一个数组中，该数组可能遍历了您的脚本内存限制.

You are probably running out of memory (check your web server error logs) - Cake will load all 2000 rows into an array which is probably going over your script memory limit.

您可以提高脚本的内存限制，但还有其他选择:

You could raise your script memory limit but there are other options:

您真的需要取回所有2000行吗?向用户显示那么多的数据对于用户而言将是难以理解的.分页在这里会更好吗?

Do you really need to fetch back all 2000 rows? Displaying that much data to the user would be incomprehensible to the user. Would pagination work better here?

如果您打算基于所有数据进行计算，也许您可​​以重新编写查询以在查询中进行计算?

If you are planning on doing calculations based on all the data, perhaps you can re-write your query to do the calculations in the query?

如果必须使用PHP处理数据，请分块取回(例如一次记录100条).如果该过程可能需要很长时间，则可能需要将脚本超时设置为较高的值...

If you have to use PHP to process the data, fetch it back in chunks (say 100 records at a time). You may need to set the script time out to a high value if the process might take a long time...

这篇关于在CakePHP& amp;中返回很多行的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！