0

I try to remove a where condition to a query statement. To resume, I build a query and add geographical coordinates condition to get POI in a specific area. I execute the query and if there is no result, I remove the condition to get all the POI. Here the script :

//Declare the statement
$statement = $this->connection->select('table', 't')
            ->fields(/*...*/)
            ->range(/*...*/);

//Add the condition
$statement->condition("lat", [$GPSbounds['lat_min'], $GPSbounds['lat_max']], "BETWEEN");
$statement->condition("lng", [$GPSbounds['lng_min'], $GPSbounds['lng_max']], "BETWEEN");

// launch the SQL query
$statementInterface = $statement->execute(); // Execute the statement
$results = $statementInterface->fetchAll(\PDO::FETCH_OBJ); // Get all the results

So, if there is no result, I do this :

if (sizeof($results) == 0) {

    //We remove the bounds conditions
    //=& is reference equality, changes applicated to $conditions are applicated to $statement->conditions();
    $where =& $statement->conditions();
    foreach ($statement->conditions() as $index => $condition) {
        if (array_key_exists('field', $condition)) {
            if ($condition['field'] == 'lat' || $condition['field'] == 'lng') {
                unset($where[$index]);
            }
        }
     }

     //dump $statement->conditions() : condition is removed
     $statementInterface = $statement->execute(); // Execute the statement
     //dump $statementInterface->getQueryString() : condition is in the query so the result are empty again (as the first execution)
     $results = $statementInterface->fetchAll(\PDO::FETCH_OBJ); // Get all the results
}

If I do a var_dump() of $statement->conditions() before execute(), the condition has been removed (not in the array). But after the execute(), if I dump the query string, the condition is present and the query result 0 result again (because of the condition restriction). What can I do ?

3

Executing the same query builder object twice is not supported.

Instead, build the query with everything except the the two conditions, clone it, add the two conditions, if you have nothing, execute the original (or the clone, depending on which object you picked to execute first).

  • It's exactly what I did, I've clone the statement before adding the conditions and executing theme separatly. But I don't know why, I tought it wasn't and very good practise. – Gaius Aug 16 '16 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.