title: Propel ORM - UNION queries
body: <p>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:</p>
  
  <pre><code>$query = $query
    -&gt;distinct()
    -&gt;select(Request::getTransferFieldsWithRelations())
    -&gt;leftJoinResponse("Response")
    -&gt;joinWith("Request.SupportStatus SupportStatus")
    -&gt;joinWith("Request.CustomerGroup CustomerGroup", Criteria::LEFT_JOIN)
    -&gt;joinWith("Request.Customer Customer", Criteria::LEFT_JOIN)
    -&gt;joinWith("Request.Site Site", Criteria::LEFT_JOIN)
    -&gt;joinWith("Request.InternalUser InternalUser", Criteria::LEFT_JOIN)
    -&gt;joinWith("Request.User User", Criteria::LEFT_JOIN)
    -&gt;orderBy("CreatedDate", Criteria::ASC);
  
  $conditions = array(
    "and" =&gt; array(),
    "or" =&gt; array()
  );
  
  if(isset($args["QueryText"]) &amp;&amp; $args["QueryText"] != "") {
    $query = $query
      -&gt;withColumn("(MATCH (Request.Subject, Request.Detail) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE) + MATCH (Response.Response) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE))", "RequestRelevance")
      -&gt;condition('cond1', "(MATCH (Request.Subject, Request.Detail) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE) + MATCH (Response.Response) AGAINST ('" . $args["QueryText"] . "' IN BOOLEAN MODE)) &gt; 0.2")
      -&gt;condition('cond2', 'Request.Id = ?', $args["QueryText"])
      -&gt;where(array('cond1', 'cond2'), 'or')
      -&gt;orderBy("RequestRelevance", Criteria::DESC);
  }
  
  if(isset($args["OpenCallsOnly"]) &amp;&amp; $args["OpenCallsOnly"] == 1) {
    $query = $query
      -&gt;useSupportStatusQuery()
        -&gt;filterByOutstanding(1)
      -&gt;endUse();
  }
  
  if(isset($args["ClosedCallsOnly"]) &amp;&amp; $args["ClosedCallsOnly"] == 1) {
    $query = $query
      -&gt;useSupportStatusQuery()
        -&gt;filterByIsClosed(1)
      -&gt;endUse();
  }
  
  ...
  
  foreach ($conditions as $key =&gt; $value) {
    if(!empty($value)){
      $query = $query
        -&gt;where($value, $key);
    }
  }
  </code></pre>
  
  <p>However, this query takes a good 20 seconds to execute on the website if sorting by <code>ClosedCallsOnly</code> (so nearly 50000 results) and over 8 seconds if using raw SQL. I have optimised it to the following query, using <code>UNION</code> statements:</p>
  
  <pre><code>    (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)
  </code></pre>
  
  <p>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 <code>UNION</code> 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.</p>