Как развязать эти условия для достижения этой структуры запросов?

Ответ 1

Здесь немного "сложный" подход (выглядит чисто, хотя), который использует подготовленные операторы. В случае будущих изменений я добавил некоторые "функции" общего назначения. Прочитайте комментарии с пояснениями (будет удобнее, как мне кажется):

//assume established PDO connection - example:
try {
    $pdo = new PDO("mysql:dbname={$database_name};host=localhost", $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

//static: conditional strings without parameters check (no keys required)
//conditional: assoc - keys should match both query placeholders and variable names
$static_where = [];
$optional_where = [
    'last_visit'   => 'behaviour.TIMESTAMP >= DATE_SUB(CURDATE(), INTERVAL :last_visit DAY) AND behaviour.TIMESTAMP < DATE_ADD(CURDATE(), INTERVAL 1 DAY)',
    'from_country' => 'audience.country = :from_country'
];

$static_having = [];
$optional_having = [
    'more_than'    => 'COUNT(*) >= :more_than',
    'time_spent'   => 'timeSpent >= :time_spent',
    'lead_scoring' => 'new_table.percentile_rank >= :lead_scoring'
];

//params: query parameters array - assigned manually + resolved from optional variables
$params = [];

//resolve condition from $urls array
if (count($urls) == 1) {
    $static_where[] = 'url = :url';
    $params['url'] = reset($urls);
} else if (!empty($urls)) {
    foreach ($urls as $idx => $url) {
        $params['url' . $idx] = $url;
    }
    $static_where[] = 'url IN(:' . implode(', :', array_keys($params)) . ')';
}

//filtering existing params used in query
//empty() is not a good idea for general purpose though,
//because some valid values might be recognised as empty (int 0, string '0')
$params += array_filter(
    compact(array_keys($optional_where), array_keys($optional_having)),
    function ($value) { return !empty($value); }
);

//concatenating conditional strings
//with corresponding params that weren't filtered out
//or these without params (static)
$where_clause = implode(' AND ', $static_where + array_intersect_key($optional_where, $params));
$having_clause = implode(' AND ', $static_having + array_intersect_key($optional_having, $params));

//don't need clauses without conditions - same as if (!empty($where)) {...}
empty($where_clause) or $where_clause = 'WHERE ' . $where_clause;
empty($having_clause) or $having_clause = 'HAVING ' . $having_clause;

$sql = "SELECT 
          behaviour.hash,
          Sum(behaviour.timespent) AS timeSpent,
          new_table.percentile_rank,
          Count( * ) AS total 
        FROM behaviour,
        INNER JOIN audience ON behaviour.hash = audience.hash,
        INNER JOIN new_table ON behaviour.hash = new_table.hash 
        {$where_clause}
        GROUP BY behaviour.hash 
        {$having_clause}";

//PDO part
$query = $pdo->prepare($sql);
$result = $query->execute($params);
//...

Ответ 2

Во-первых, вы должны гарантировать, что SQL-инъекция невозможна. Для этого используйте PDO.

Далее, чтобы решить вашу актуальную проблему, вам просто нужно создать два списка с условиями. Один из условий, которые вы хотите иметь в части запроса WHERE, и один с условиями, которые необходимо выполнить в части запроса HAVING.

    $pdo = new PDO(/* See http://php.net/manual/en/pdo.construct.php */);

    $whereConditions = [];
    $havingConditions = [];
    $parameters = [];

    if (!empty($last_visit)) {
        $whereConditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL :last_visit DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) ";
        $parameters['last_visit'] = $last_visit;
    }
    if (!empty($from_country)) {
        $whereConditions[] = "audience.country = :from_country";
        $parameters['from_country'] = $from_country;
    }
    if (!empty($more_than)) {
        $havingConditions[] = "COUNT( * ) >= :more_than";
        $parameters['more_than'] = $more_than;
    }
    if (!empty($time_spent)) {
        $havingConditions[] = "timeSpent >= :time_spent";
        $parameters['time_spent'] = $time_spent;

    }
    if (!empty($lead_scoring)) {
        $havingConditions[] = "new_table.percentile_rank >= :lead_scoring";
        $parameters['lead_scoring'] = $lead_scoring;
    }

    if (count($vals)) {
        $escapedUrlList = implode(', ', array_map(function ($url) use ($pdo) {
            return $pdo->quote($url);
        }, $vals));
        $whereConditions[] = "url IN($escapedUrlList)";
    }

    $whereClause = count($whereConditions) ? ' AND ' . implode(' AND ', $whereConditions) : '';
    $havingClause = count($havingConditions) ? ' HAVING ' . implode(' AND ', $havingConditions) : '';

    $statement = $pdo->prepare("
        SELECT behaviour.hash, 
            Sum(behaviour.timespent) AS timeSpent, 
            new_table.percentile_rank, 
            Count(*) AS total 
        FROM behaviour, 
            audience, 
            new_table 
        WHERE behaviour.hash = audience.hash 
            AND behaviour.hash = new_table.hash 
            {$whereClause}
        GROUP  BY behaviour.hash
        {$havingClause}
    ");

    $result = $statement->execute($parameters);

Ответ 3

Ниже представлен менее сложный способ использования конкатенации строк вместо implode. "Трюк" - это запуск условий с помощью 1=1. Поэтому каждое следующее условие может начинаться с AND.

$andWhere  = '';
$andHaving = '';
$params = [];


if (!empty($last_visit)) {
    $andWhere .= " AND behaviour.TIMESTAMP >= CURDATE() - INTERVAL :last_visit DAY AND behaviour.TIMESTAMP < CURDATE() + INTERVAL 1 DAY";
    $params['last_visit'] = $last_visit;
}
if (!empty($from_country)) {
    $andWhere .= " AND audience.country = :from_country";
    $params['from_country'] = $from_country;
}
if (!empty($more_than)) {
    $andHaving .= " AND COUNT( * ) >= :more_than";
    $params['more_than'] = $more_than;
}
if (!empty($time_spent)) {
    $andHaving .= " AND timeSpent >= :time_spent";
    $params['time_spent'] = $time_spent;
}
if (!empty($lead_scoring)) {
    $andHaving .= " AND new_table.percentile_rank >= :lead_scoring";
    $params['lead_scoring'] = $lead_scoring;
}

$urlPlaceholders = [];
foreach ($vals as $key => $val) {
    $urlPlaceholders[] = ":url_$key";
    $params["url_$key"] = $val;
}
if (count($vals) > 0) {
    $inUrl = implode(',', $urlPlaceholders);
    $andWhere .= " AND url IN ($inUrl)";
}

$sql = "
    SELECT behaviour.hash, 
           Sum(behaviour.timespent) AS timeSpent, 
           new_table.percentile_rank, 
           Count(*) AS total 
    FROM   behaviour 
    JOIN   audience  ON behaviour.hash = audience.hash
    JOIN   new_table ON behaviour.hash = new_table.hash
    WHERE  1=1 {$andWhere}
    GROUP  BY behaviour.hash 
    HAVING 1=1 {$andHaving}
";

#var_export($sql);
#var_export($params);

$sth = $dbh->prepare($sql);
$sth->execute($params);
$data = $sth->fetchAll(PDO::FETCH_ASSOC);

#var_export($data);

Имея пример данных типа

$last_visit   = '';
$from_country = 'UK';
$more_than    = '5';
$time_spent   = '3';
$lead_scoring = '';
$vals = ['u1', 'u2'];

Вы получите следующий запрос:

SELECT behaviour.hash, 
       Sum(behaviour.timespent) AS timeSpent, 
       new_table.percentile_rank, 
       Count(*) AS total 
FROM   behaviour 
JOIN   audience  ON behaviour.hash = audience.hash
JOIN   new_table ON behaviour.hash = new_table.hash
WHERE  1=1  AND audience.country = :from_country AND url IN (:url_0,:url_1)
GROUP  BY behaviour.hash 
HAVING 1=1  AND COUNT(*) >= :more_than AND timeSpent >= :time_spent

с этими привязками:

array (
  'from_country' => 'UK',
  'more_than' => '5',
  'time_spent' => '3',
  'url_0' => 'u1',
  'url_1' => 'u2',
)

Демо на rextester.com

Ответ 4

Если есть единственная проблема, почему бы не разбить ее на другой блок следующим образом:

$conditions = array();

if (!empty($last_visit)) $conditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL '".$last_visit."' DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) ";
if (!empty($from_country)) $conditions[] = "audience.country = '".$from_country."'";


$conditionString = implode(' AND ', $conditions);

$conditions_having = array();
if (!empty($more_than)) $conditions_having[] = "COUNT( * ) >= '".$more_than."'"; 
if (!empty($time_spent)) $conditions_having[] = "timeSpent >= '".$time_spent."'";
if (!empty($lead_scoring)) $conditions_having[] = "new_table.percentile_rank >= '".$lead_scoring."'";

$conditionString .= " GROUP  BY behaviour.hash" 
if(count($conditions_having))
$conditionString .= " HAVING ".implode(' AND ', $conditions_having); 

$sql = "SELECT behaviour.hash, 
       Sum(behaviour.timespent) AS timeSpent, 
       new_table.percentile_rank, 
       Count( * ) AS total 
FROM   behaviour, 
       audience, 
       new_table 
WHERE  ($url) AND ".$conditionString;

Ответ 5

Вы можете изменить только вашу функцию implode на этот код

$conditionString = implode(' ', array_map(function($item) {
if ((strpos($item, 'timeSpent') !== false))
    return 'HAVING '.$item;
return 'AND '.$item;
}, $conditions));

Помните, что ваш код уязвим.

для получения дополнительной информации см. это: SQL Injection В CAPEC

Ответ 6

  $url= 'url="'.implode('" OR url="', $vals).'"';

  $conditions = array();
  $havings = array();

    if (!empty($last_visit)) $conditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL '".$last_visit."' DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) ";
    if (!empty($from_country)) $conditions[] = "audience.country = '".$from_country."'";
    if (!empty($more_than)) $havings[] = "COUNT( * ) >= '".$more_than."'"; 
    if (!empty($time_spent)) $havings[] = "timeSpent >= '".$time_spent."'";
    if (!empty($lead_scoring)) $havings[] = "new_table.percentile_rank >= '".$lead_scoring."'";


    $conditionString = implode(' AND ', $conditions);

    $havingString = '';
    if(count($havings)>0) {
         $havingString = ' having '.implode(', ', $havings);    
    }

    $sql = "SELECT behaviour.hash, 
           Sum(behaviour.timespent) AS timeSpent, 
           new_table.percentile_rank, 
           Count( * ) AS total 
    FROM   behaviour, 
           audience, 
           new_table 
    WHERE  ($url) AND ".$conditionString.$havingString;