控制器中的 CakePHP 查询添加
我正在将原始 PHP 代码迁移到 CakePHP,但遇到了一些问题。由于我在查询到 ORM 转换方面遇到了很大的问题,所以我暂时使用原始 SQL。一切都很顺利,但我遇到了丑陋的代码,并且真的不知道如何让它变得漂亮。我制作了 DealersController 并添加了 function advance($condition = null)(将从 AJAX 使用参数 1-15 和 69 调用它)。函数看起来像:
switch ($condition) {
case '1':
$cond_query = ' AND ( (d.email = \'\' OR d.email IS NULL) )';
break;
case '2':
$cond_query = ' AND (d.id IN (SELECT dealer_id FROM dealer_logo)';
break;
// There are many cases, some long, some like these two
}
if($user_group == 'group_1') {
$query = 'LONG QUERY WITH 6+ TABLES JOINING' . $cond_query;
} elseif ($user_group == 'group_2'){
$query = 'A LITLE BIT DIFFERENT LONG QUERY WITH 6+ TABLES JOINING' . $cond_query;
} else {
$query = 'A LITLE MORE BIT DIFFERENT LONG QUERY WITH 10+ TABLES JOINING' . $cond_query;
}
// THERE IS $this->Dealer->query($query); and so on
所以..正如你所看到的,代码看起来很丑陋。我有两种变体:
1)取出查询添加并为每个条件创建模型方法,然后将这些条件分离为函数。但这不是 DRY,因为主要的 3 个大查询几乎是相同的,如果我需要更改其中的某些内容 - 我将需要更改 16 个以上的查询。
2)创建小型可重用模型方法/查询,这些方法/查询将从数据库中获取小块数据,然后不要使用原始 SQL,而是使用方法。这很好,但性能会很低,我需要它尽可能高。
请给我建议。谢谢你!
I am migrating raw PHP code to CakePHP and have some problems. As I have big problems with query to ORM transformation I temporary use raw SQL. All is going nice, but I met the ugly code and don't really know how to make it beautiful. I made DealersController
and added function advanced($condition = null)
(it will be called from AJAX with parameters 1-15 and 69). function looks like:
switch ($condition) {
case '1':
$cond_query = ' AND ( (d.email = \'\' OR d.email IS NULL) )';
break;
case '2':
$cond_query = ' AND (d.id IN (SELECT dealer_id FROM dealer_logo)';
break;
// There are many cases, some long, some like these two
}
if($user_group == 'group_1') {
$query = 'LONG QUERY WITH 6+ TABLES JOINING' . $cond_query;
} elseif ($user_group == 'group_2'){
$query = 'A LITLE BIT DIFFERENT LONG QUERY WITH 6+ TABLES JOINING' . $cond_query;
} else {
$query = 'A LITLE MORE BIT DIFFERENT LONG QUERY WITH 10+ TABLES JOINING' . $cond_query;
}
// THERE IS $this->Dealer->query($query); and so on
So.. As you see code looks ugly. I have two variants:
1) get out query addition and make model methods for every condition, then these conditions seperate to functions. But this is not DRY, because main 3 big queries is almost the same and if I will need to change something in one - I will need to change 16+ queries.
2) Make small reusable model methods/queries whitch will get out of DB small pieces of data, then don't use raw SQL but play with methods. It would be good, but the performance will be low and I need it as high as possible.
Please give me advice. Thank you!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
如果您担心 CakePHP 如何对每个连接表进行数据库查询,您可能会发现 Linkable这种行为可以帮助您减少查询数量(其中联接是一张表上的简单关联)。
否则,我发现在模型级别创建简单的数据库查询方法来获取较小的信息,然后将它们组合起来,是一个很好的方法。它允许您清楚地概述代码的用途(通过内联文档)。如果您可以迁移到使用 CakePHP 的
find
方法而不是原始查询,那么您将使用conditions
数组语法。因此,解决问题的一种方法是在模型类上使用公共函数,将其适当的条件附加到输入的条件数组中。例如:您可以调用这些函数来构建一个大型
conditions
数组,然后您可以使用该数组从控制器(或者如果您想将所有数据包含在模型中)检索所需的数据。模型层)。请注意,在上面的示例中,conditions
数组是通过引用传递的,因此可以就地对其进行编辑。另请注意,数组中任何现有的“OR”条件都将被此函数覆盖:您真正的解决方案在将新条件与任何现有条件合并方面必须更加智能。不要担心“假设的”性能问题 - 如果您尝试过查询并且查询速度太慢,那么您可以担心如何提高性能。但对于初学者来说,请尝试尽可能干净地编写代码。
您可能还需要考虑将该
function advance()
调用拆分为多个控制器操作,这些操作按其条件
查询的相似性进行分组。最后,如果您还没有查看过,这里是本书中有关从模型检索数据的条目。可能有一些您以前没有见过的技巧: http://book.cakephp .org/view/1017/检索您的数据
If you're concerned about how CakePHP makes a database query for every joined table, you might find that the Linkable behaviour can help you reduce the number of queries (where the joins are simple associations on the one table).
Otherwise, I find that creating simple database querying methods at the Model level to get your smaller pieces of information, and then combining them afterwards, is a good approach. It allows you to clearly outline what your code does (through inline documentation). If you can migrate to using CakePHP's
find
methods instead of raw queries, you will be using theconditions
array syntax. So one way you could approach your problem is to have public functions on your Model classes which append their appropriate conditions to an inputted conditions array. For example:You would call these functions to build up one large
conditions
array which you can then use to retrieve the data you want from your controller (or from the model if you want to contain it all at the model layer). Note that in the above example, theconditions
array is being passed by reference, so it can be edited in place. Also note that any existing 'OR' conditions in the array will be overwritten by this function: your real solution would have to be smarter in terms of merging your new conditions with any existing ones.Don't worry about 'hypothetical' performance issues - if you've tried to queries and they're too slow, then you can worry about how to increase performance. But for starters, try to write the code as cleanly as possible.
You also might want to consider splitting up that
function advanced()
call into multiple Controller Actions that are grouped by the similarity of theircondition
query.Finally, in case you haven't already checked it out, here's the Book's entry on retrieving data from models. There might be some tricks you hadn't seen before: http://book.cakephp.org/view/1017/Retrieving-Your-Data
如果查询的基本部分相同,您可以使用一个函数来生成查询的该部分,然后使用其他小函数来附加不同的 where 条件等。
If the base part of the query is the same, you could have a function to generate that part of the query, and then use other small functions to append the different where conditions, etc.