我在网站上看过相关的问题，但没有一个真正回答我的问题。我在一个使用Propel的网站上有以下声明:
$query = $query
->distinct()
->select(Request::getTransferFieldsWithRelations())
->leftJoinResponse("Response")
->joinWith("Request.SupportStatus SupportStatus")
->joinWith("Request.CustomerGroup CustomerGroup", Criteria::LEFT_JOIN)
->joinWith("Request.Customer Customer", Criteria::LEFT_JOIN)
->joinWith("Request.Site Site", Criteria::LEFT_JOIN)
->joinWith("Request.InternalUser InternalUser", Criteria::LEFT_JOIN)
->joinWith("Request.User User", Criteria::LEFT_JOIN)
->orderBy("CreatedDate", Criteria::ASC);
$conditions = array(
"and" => array(),
"or" => array()
);
if(isset($args["QueryText"]) && $args["QueryText"] != "") {
$query = $query
->withColumn("(MATCH (Request.Subject, Request.Detail) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE) + MATCH (Response.Response) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE))", "RequestRelevance")
->condition('cond1', "(MATCH (Request.Subject, Request.Detail) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE) + MATCH (Response.Response) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE)) > 0.2")
->condition('cond2', 'Request.Id = ?', $args["QueryText"])
->where(array('cond1', 'cond2'), 'or')
->orderBy("RequestRelevance", Criteria::DESC);
}
if(isset($args["OpenCallsOnly"]) && $args["OpenCallsOnly"] == 1) {
$query = $query
->useSupportStatusQuery()
->filterByOutstanding(1)
->endUse();
}
if(isset($args["ClosedCallsOnly"]) && $args["ClosedCallsOnly"] == 1) {
$query = $query
->useSupportStatusQuery()
->filterByIsClosed(1)
->endUse();
}
...
foreach ($conditions as $key => $value) {
if(!empty($value)){
$query = $query
->where($value, $key);
}
}
然而，如果按ClosedCallsOnly排序(那么将近50000个结果)，这个查询在网站上执行需要20秒，如果使用原始SQL，则需要8秒以上。我使用UNION语句将其优化为以下查询:
(SELECT DISTINCT
requests.requestID AS "Id", requests.subject AS "Subject", requests.detail AS "Detail", requests.created AS "CreatedDate", requests.lastresponsedate AS "LastResponseDate", SupportStatus.supportstatusID AS "SupportStatus.Id", SupportStatus.supportstatus AS "SupportStatus.Name", SupportStatus.isnew AS "SupportStatus.IsNew", SupportStatus.isclosed AS "SupportStatus.IsClosed", CustomerGroup.customergroupID AS "CustomerGroup.Id", CustomerGroup.customergroup AS "CustomerGroup.Name", Site.siteID AS "Site.Id", Site.site AS "Site.Name", InternalUser.userID AS "InternalUser.Id", InternalUser.username AS "InternalUser.Username", User.userID AS "User.Id", User.username AS "User.Username", Customer.customerID AS "Customer.Id", Customer.customer AS "Customer.Name", Customer.customergroupID AS "Customer.CustomerGroupId", Customer.rate AS "Customer.Rate"
FROM requests
LEFT JOIN responses Response ON (requests.requestID=Response.requestID)
INNER JOIN supportstatus SupportStatus ON (requests.supportstatusID=SupportStatus.supportstatusID)
INNER JOIN customergroups CustomerGroup ON (requests.customergroupID=CustomerGroup.customergroupID)
INNER JOIN customers Customer ON (requests.customerID=Customer.customerID)
INNER JOIN sites Site ON (requests.siteID=Site.siteID)
LEFT JOIN users InternalUser ON (requests.internal_userID=InternalUser.userID)
LEFT JOIN users User ON (requests.userID=User.userID)
WHERE ((MATCH (requests.subject, requests.detail) AGAINST ('slow pc' IN BOOLEAN MODE)
))
ORDER BY requests.created ASC)
UNION
(SELECT DISTINCT
requests.requestID AS "Id", requests.subject AS "Subject", requests.detail AS "Detail", requests.created AS "CreatedDate", requests.lastresponsedate AS "LastResponseDate", SupportStatus.supportstatusID AS "SupportStatus.Id", SupportStatus.supportstatus AS "SupportStatus.Name", SupportStatus.isnew AS "SupportStatus.IsNew", SupportStatus.isclosed AS "SupportStatus.IsClosed", CustomerGroup.customergroupID AS "CustomerGroup.Id", CustomerGroup.customergroup AS "CustomerGroup.Name", Site.siteID AS "Site.Id", Site.site AS "Site.Name", InternalUser.userID AS "InternalUser.Id", InternalUser.username AS "InternalUser.Username", User.userID AS "User.Id", User.username AS "User.Username", Customer.customerID AS "Customer.Id", Customer.customer AS "Customer.Name", Customer.customergroupID AS "Customer.CustomerGroupId", Customer.rate AS "Customer.Rate"
FROM requests
LEFT JOIN responses Response ON (requests.requestID=Response.requestID)
INNER JOIN supportstatus SupportStatus ON (requests.supportstatusID=SupportStatus.supportstatusID)
INNER JOIN customergroups CustomerGroup ON (requests.customergroupID=CustomerGroup.customergroupID)
INNER JOIN customers Customer ON (requests.customerID=Customer.customerID)
INNER JOIN sites Site ON (requests.siteID=Site.siteID)
LEFT JOIN users InternalUser ON (requests.internal_userID=InternalUser.userID)
LEFT JOIN users User ON (requests.userID=User.userID)
WHERE (requests.requestID = 'slow pc')
ORDER BY requests.created ASC)
UNION
(SELECT DISTINCT
Request.requestID AS "Id", Request.subject AS "Subject", Request.detail AS "Detail", Request.created AS "CreatedDate", Request.lastresponsedate AS "LastResponseDate", SupportStatus.supportstatusID AS "SupportStatus.Id", SupportStatus.supportstatus AS "SupportStatus.Name", SupportStatus.isnew AS "SupportStatus.IsNew", SupportStatus.isclosed AS "SupportStatus.IsClosed", CustomerGroup.customergroupID AS "CustomerGroup.Id", CustomerGroup.customergroup AS "CustomerGroup.Name", Site.siteID AS "Site.Id", Site.site AS "Site.Name", InternalUser.userID AS "InternalUser.Id", InternalUser.username AS "InternalUser.Username", User.userID AS "User.Id", User.username AS "User.Username", Customer.customerID AS "Customer.Id", Customer.customer AS "Customer.Name", Customer.customergroupID AS "Customer.CustomerGroupId", Customer.rate AS "Customer.Rate"
FROM responses
LEFT JOIN requests Request ON (Request.requestID=responses.requestID)
INNER JOIN supportstatus SupportStatus ON (Request.supportstatusID=SupportStatus.supportstatusID)
INNER JOIN customergroups CustomerGroup ON (Request.customergroupID=CustomerGroup.customergroupID)
INNER JOIN customers Customer ON (Request.customerID=Customer.customerID)
INNER JOIN sites Site ON (Request.siteID=Site.siteID)
LEFT JOIN users InternalUser ON (Request.internal_userID=InternalUser.userID)
LEFT JOIN users User ON (Request.userID=User.userID)
WHERE ((
MATCH (responses.response) AGAINST ('slow pc' IN BOOLEAN MODE)))
ORDER BY Request.created ASC)
这个语句的执行时间提高了大约8倍，这真的很好，但不幸的是，我不确定如何将其转换为Propel查询。从其他问题来看，似乎在Propel中使用UNION是不可能的。我知道，使用SQL语句是可能的，在推进，但作为推进查询正在使用在其他地方在这个类，我不知道它将如何可能?我如何在我的网站中实现这个查询?如果需要，我可以为这个类提供更多的代码。
在propel博客上有一篇关于它的文章，解释了当使用原始sql而不是查询API更有趣时，您的情况似乎绝对符合要求(大量连接)。http://propelorm.org/blog/2011/02/02/how-can-i-write-this-query-using-an-orm-.html
下面是给出的一个用例:
"这个查询不是面向对象的，它是纯关系的，所以它不需要对象关系映射。最好的执行方式ORM中的这个查询是跳过ORM并直接使用PDO:"
$con = Propel::getConnection();
$query = 'SELECT COUNT(t1.user) AS users, t1.choice AS lft, t2.choice AS rgt
FROM choice t1 iNNER JOIN choice t2 ON (t1.user = t2.user)
WHERE t1.choice IN (?, ?) AND t2.choice IN (?, ?)
GROUP BY t1.choice, t2.choice';
$stmt = $con->prepare($query);
$stmt->bindValue(1, 'foo');
$stmt->bindValue(2, 'bar');
$stmt->bindValue(3, 'baz');
$stmt->bindValue(4, 'foz');
$res = $stmt->execute();
在这种情况下，我所做的是围绕您的大型查询创建一个视图。
然后可以在schema.xml
中创建一个Propel ReadOnly模型<table name="my_table" readOnly="true">
为这个类生成的模型不会有save()方法，我相信。
另外，根据你使用的数据库平台，你可以创建一个'Materialized View'。
Oracle有这种内置的东西，但是MySQL没有。
您可以在MySQL中创建一个存储过程，它每小时运行一次，并将union中的数据插入到表中。
查询这个预填充的表将快得多。