Propel ORM - UNION queries

1
votes

I have looked through related questions on this on the website but none of them really answer my question. I have the following statements in a website using 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);
  }
}

However, this query takes a good 20 seconds to execute on the website if sorting by ClosedCallsOnly (so nearly 50000 results) and over 8 seconds if using raw SQL. I have optimised it to the following query, using UNION statements:

    (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)

Execution time of this statement improves roughly 8x which is really good but unfortunately, I am not sure how to translate it to a Propel query. From looking at other questions, it seems that using UNION in Propel isn't possible. I know that using SQL statements is possible in Propel but as Propel queries are being used everywhere else in this class, I am not sure how it would be possible? How could I implement this query into my website? If needed, I can provide more code for this class.

2
phpmysqlunionpropel
Sometimes you just have to write raw SQL, particularly when optimizing large queries like this. – Shelvacu

2 Answers

1
votes

There is an article about it on the propel blog, explaining when it is more interesting to use raw sql rather than query API, and your case seem to definitely fit the bill (lot of joins). http://propelorm.org/blog/2011/02/02/how-can-i-write-this-query-using-an-orm-.html

Here is one of the presented use case:

"This query is not object-oriented, it’s purely relational, so it doesn’t need an Object-Relational Mapping. The best way to execute this query inside an ORM is to skip the ORM and use PDO directly :"

$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();
0
votes

What I did in this situation was to create a view around your large query.

Then you can create a Propel ReadOnly model in your schema.xml

<table name="my_table" readOnly="true">

The models generated for this class won't have a save() method, I believe.

Also, depending on the database platform you are using, you can create a 'Materialized View'.

Oracle has this type of thing built in, but MySQL doesn't.

You can create a stored procedure in MySQL though, that runs hourly and inserts the data from your union into a table.

Querying this pre-populated table will be much quicker.