Group
From . .. poMMo .. .
UNDERSTANDING GROUPS
One of bMail's most powerful features is to let users create subsets of their subscribers in an incredibly flexible manner. A lot of time was spent developing the new group management system which debuted in Aardvark PR8. Here's an overview of how the system works.
Filters Explained
- General Behavior: At this time, all filters are compounded with "AND" logic. This means that every filter in a group must evaluate to true for any results(subscribers) to be returned. Think of it as if each filter is "required". For instance if you have a filter that matches subscribers living in Japan, and one that matches subscribers over the age of 20, the group will return only subscribers in Japan that are 21 or over. In the future, you will be able to select between "AND" and "OR" logic. If "OR" logic was used in the above example, the group would return subscribers in Japan of any age, and only those over 21 elsewhere.
- Heuristics to check for colisions: Before creating a filter, bMail first checks to see if it is 'legal'. Filters are 'illegal' if they negate the effect of another one in the same group. For instance, you can not create a filter that checks if zipcode is not equal to 55555 if there exists a filter that matches subscribers with zipcode 77777.
- Chain Filtering: It is possible to chain filters together by combining groups with logic. For instance, pretend you have a group called "Kwakiutls" that has a filter that matches subscribers whose 'descent' is equal to "Kwakiutl". You can then match all subscribers whose descent is not Kwakiutl by creating a group called "Not Kwakiutl" with a filter which matches subscribers who do not belong to the "Kwakiutls" group.
- Note: When chaining groups together, subtraction has priority. The WHERE clause generator will create queries which evaluate the criteria of a group(s) to be EXCLUDED from a group BEFORE those of group(s) to be INCLUDED.
A look into the sourcecode:
Verify Heuristic (must return true before a filter is added or updated)
// dbGroupFilterVerify: <bool> - Returns true if a filter addition/update proves to be sane.
function dbGroupFilterVerify(& $dbo, & $group_id, & $demographic_id, & $logic, & $value) {
$oppLogic = NULL;
switch ($logic) {
case 'is_in' :
case 'not_in' :
// verify target group doesn't point to this one
/* Rempoved -- loops are handled in the code that generates a groups SQL 'WHERE' logic.
$sql = 'SELECT count(criteria_id) FROM groups_criteria WHERE group_id=\''.$value.'\' AND value=\''.$group_id.'\' AND (logic=\'is_in\' OR logic=\'not_in\')';
if ($dbo->query($sql, 0))
return false; */
// verify that this group does not already reference the target
$sql = 'SELECT count(criteria_id) FROM groups_criteria WHERE group_id=\''.$group_id.'\' AND value=\''.$value.'\' AND (logic=\'is_in\' OR logic=\'not_in\')';
if ($dbo->query($sql, 0))
return false;
break;
// set opposite logic, verify that a criteria is not trying to do the opposite as this one
case 'is_equal' :
case 'not_equal' :
$oppLogic = 'is_equal';
if ($oppLogic == $logic)
$oppLogic = 'not_equal';
break;
case 'is_more' :
case 'is_less' :
$oppLogic = 'is_more';
if ($oppLogic == $logic)
$oppLogic = 'is_less';
break;
case 'is_true' :
case 'not_true' :
$oppLogic = 'is_true';
if ($oppLogic == $logic)
$oppLogic = 'not_true';
break;
default :
return false;
}
if (!empty ($oppLogic))
$sql = 'SELECT count(criteria_id) FROM groups_criteria WHERE group_id=\''.$group_id.'\' AND demographic_id=\''.$demographic_id.'\' AND logic=\''.$oppLogic.'\'';
if ($dbo->query($sql, 0))
return false;
return true;
}
The below code is for bMail version < PR9. A new, dynamic demographic system was introduced in PR9 which obsoleted the below methods. For an overview of the current system, see The bMail and MySQL page
Current Generation of SQL WHERE Logic 'OUTDATED'-> This was a little tricky. MySQL supports UNION, however not MINUS, INTERSECT, or EXCEPT... which is a shame. This is no big deal, however, because currently all subscriber demographics are held within the subscribers table. In future versions, demographic data will be stored in a seperate table and left joined to subscribers for this to work -- however, with this the case, it would be more efficient(??) to add subqueries per each group involved as this would result in better reading of the indexes. Frankly, this'll be a bitch to implement without support for MINUS/EXCEPT clauses.
// returns SQL WHERE logic pertaining to a group
function dbGroupSQL(& $dbo, $group_id) {
require_once (bm_baseDir.'/inc/txt_functions.php');
// if group_id is "all", return.
if ($group_id == 'all')
return '1';
// get array of all criteria (saves from many MySQL queries). criteria_id is array key.
$criteriaArray = dbGetGroupFilter($dbo);
// make $groupArray where group_id is array key, and element is an array of that group's criteria_ids
// ie. $groupArray[5][6,12,15] means group 5 has filtering criteria w/ id 6,12, and 15 assosiated w/ it.
$groupArray = array ();
foreach (array_keys($criteriaArray) as $key) {
$criteria = & $criteriaArray[$key];
if (empty ($groupArray[$criteria['group_id']]))
$groupArray[$criteria['group_id']] = array ();
array_push($groupArray[$criteria['group_id']], $key);
}
// determine if any criteria is assosiated with this group.. if not, return
if (empty($groupArray[$group_id]))
return '0';
// Recursively generate WHERE logic
$groupsVisited = array ();
$tree = dbCrawl($dbo, $group_id, $criteriaArray, $groupArray, $groupsVisited);
return genSQL($tree, $criteriaArray);
}
// crawls through a group's filtering criteria, returning an array tree of criteria_id's
function dbCrawl(& $dbo, $group_id, & $criteriaArray, & $groupArray, & $groupsVisited) {
// leave a breadcrumb...
$groupsVisited[$group_id] = TRUE;
$tree = array ();
// Examine each criteria belonging to this group
$group = & $groupArray[$group_id];
foreach (array_keys($group) as $key) {
$criteria_id = & $group[$key];
$criteria = & $criteriaArray[$criteria_id];
// If criteria references another group..
if (($criteria['logic'] == 'is_in') || ($criteria['logic'] == 'not_in')) {
// check to make sure we haven't already been there [loop prevention!]
if (!isset ($groupsVisited[$criteria['value']]))
$tree[$criteria_id] = dbCrawl($dbo, $criteria['value'], $criteriaArray, $groupArray, $groupsVisited);
} else // if not add the criteria_id to the tree and continue.
$tree[$criteria_id] = $criteria_id;
}
return $tree;
}
// reads in a tree of criteria_id's and generates a WHERE clause using a bottom-up method
function genSql(& $tree, & $criteriaArray) {
// create array to translate bMail logic to valid mySql syntax
$logicTbl = array ();
$logicTbl['is_equal'] = '=';
$logicTbl['not_equal'] = '<>';
$logicTbl['is_more'] = '>';
$logicTbl['is_less'] = '<';
$logicTbl['is_true'] = '= \'on\'';
$logicTbl['not_true'] = 'IS NULL';
// generate SQL clauses of children first (if any) -- bottom-up.
$minusSQL = array ();
$unionSQL = array ();
foreach (array_keys($tree) as $key)
if (is_array($tree[$key])) {
$criteria = & $criteriaArray[$key];
if ($criteria['logic'] == 'not_in') // if reference group should be subtracted
$minusSQL[] = genSql($tree[$key], $criteriaArray);
else // if reference group should be added
$unionSQL[] = genSql($tree[$key], $criteriaArray);
}
// AT LEAF &/OR Children have been exhaused. Examine each criteria...
$logicSQL = '';
foreach (array_keys($tree) as $key) {
if (is_array($tree[$key]))
continue; // skip if criteria points to a child
$criteria_id = & $tree[$key];
$criteria = & $criteriaArray[$criteria_id];
// add criteria and logic to where clause
if ($logicSQL == '') // don't prefix with 'AND' if on first criteria...
$logicSQL .= ' ( d'.$criteria['demographic_id'].' '.$logicTbl[$criteria['logic']];
else
$logicSQL .= ' AND ( d'.$criteria['demographic_id'].' '.$logicTbl[$criteria['logic']];
if (($criteria['logic'] == 'not_true') || ($criteria['logic'] == 'is_true'))
$logicSQL .= ' ';
else {
// append value after logic... enclose in ()'s in case we have multiple values..
$valueArray = quotesplit($criteria['value']);
$count = 0;
$valueArraySize = count($valueArray);
$joinStr = 'OR d'; // d matches demographic in subscribers table. Default to multiple is equal
// if you have multiple values for a not_equal logic, join them with AND. ie. if it remains on "OR"
// you get: =>WHERE ( ( d3 = ('Afghanistan') OR d3 = ('Angola') ) AND ( d2 <> ('Arkansas') OR d2 <> ('District of Columbia') OR d2 <> ('Illinois') ) ) .. wouldn't work.'
if ($criteria['logic'] == 'not_equal')
$joinStr = 'AND d';
foreach (array_keys($valueArray) as $key) {
$count ++;
$value = & $valueArray[$key];
$logicSQL .= ' (\''.$value.'\') ';
if ($count < $valueArraySize)
$logicSQL .= $joinStr.$criteria['demographic_id'].' '.$logicTbl[$criteria['logic']];
}
}
$logicSQL .= ') ';
}
$noAND = FALSE;
if ($logicSQL != '')
$logicSQL = '('.$logicSQL.')';
else
$noAND = TRUE;
// append subtractions first, then additions
foreach (array_keys($minusSQL) as $key) {
if ($noAND) {
$logicSQL .= ' NOT ('.$minusSQL[$key].')';
$noAND = FALSE;
} else
$logicSQL .= ' AND NOT ('.$minusSQL[$key].')';
}
foreach (array_keys($unionSQL) as $key) {
if ($noAND) {
$logicSQL .= ' ('.$unionSQL[$key].')';
$noAND = FALSE;
} else
$logicSQL .= ' AND ('.$unionSQL[$key].')';
}
return $logicSQL;
}
?>
For reference, here's an attempt I made to support UNION. This method gets very rough when you keep in mind that if a UNION (groupX belongs to groupY for instance) takes place in a group that was supposed to be subtracted (for instance, if groupA belongs to groupX), you'll get something like; (SELECT * FROM s WHERE (d='1')) UNION (SELECT * FROM s WHERE (d='2') AND UNION (SELECT * FROM s WHERE (d='3')) .. BAD.
// dbGroupCrawler returns an array of groups it has visited
// ex. $visited['group_id'] = <is_in || not_in> for each node (group) it crawls to
// returns false if a loop was detected.
function dbGroupCrawler(& $dbo, $group_id, $logic, & $visited) {
// if the crawler has been to this group before, return false [loop detected]
if (isset ($visited[$group_id]))
return false;
$visited[$group_id] = $logic;
// crawl to other groups if they extend from this one.
$bool = TRUE;
$sql = 'SELECT value, logic FROM groups_criteria WHERE group_id=\''.$group_id.'\' AND (logic=\'is_in\' or logic=\'not_in\')';
while ($row = $dbo->getRows($sql, TRUE, $group_id)) {
$bool = dbGroupCrawler($dbo, $row['0'], $row['1'], $visited);
}
return $bool;
}
// <str> : lays down the 'WHERE' SQL in this form ->
// select ... WHERE ('demographic' [logic] 'value') AND ('demographic' [logic] 'value' or 'demographic' [logic] 'value') .. etc.
// ex. SELECT email FROM subscrivers WHERE (d1 <> 'xxx' OR d1 <> 'yyy') AND (d3 > '5')
function dbGroupSQLB(& $dbo, $group_id) {
require_once (bm_baseDir.'/inc/txt_functions.php');
// create array to translate bMail logic to valid mySql syntax
$logicTbl = array ();
$logicTbl['is_equal'] = '=';
$logicTbl['not_equal'] = '<>';
$logicTbl['is_more'] = '>';
$logicTbl['is_less'] = '<';
$logicTbl['is_true'] = '= \'on\'';
$logicTbl['not_true'] = 'IS NULL';
// Get array of groups to include
$groupArray = array ();
dbGroupCrawler($dbo, $group_id, 'is_in', $groupArray);
// selectArray consists of a group's WHERE clase. ie "(d1='xxx' or d2='yyy') AND (d3 <> 'Me')"
// logicArray consists of either (is_in) => 'UNION' or (not_in) =>'AND NOT EXISTS'. Its keys correspond to selectArray
$selectArray = array ();
$logicArray = array ();
// for each group (this one, and groups involving)
foreach (array_keys($groupArray) as $group_id) {
$whereLogic = & $groupArray[$group_id];
$whereSql = '';
// get criterias, exclude one's pointing to other groups! '-- TODO -- use the build in function getGroupFilter()??
$sql = 'SELECT demographic_id, logic, value FROM groups_criteria WHERE group_id=\''.$group_id.'\' AND NOT (logic=\'is_in\' or logic=\'not_in\')';
$numCriteria = $dbo->records($sql);
$critCount = 0;
// for each criteria of that group
while ($row = $dbo->getRows()) {
$critCount ++; // counter of criteria
$valueArray = array ();
$valueArray = quotesplit($row['value']);
// # of values a demographic is compared against (selects usualy have multiple values...)
$count = count($valueArray) - 1;
$whereSql .= '(';
for ($i = 0; $i <= $count; $i ++) {
$value = '';
if (($valueArray[$i] == '') || ($valueArray[$i] == 'NULL'))
$value = ' NULL';
else
$value = ' \''.$valueArray[$i].'\'';
$whereSql .= 'd'.$row['demographic_id'].' '.$logicTbl[$row['logic']].' '.$value;
if ($i < $count)
$whereSql .= ' OR ';
}
$whereSql .= ')';
if ($critCount < $numCriteria)
$whereSql .= ' AND ';
}
// selectArray consists of a group's WHERE clase. ie "(d1='xxx' or d2='yyy') AND (d3 <> 'Me')"
$selectArray[] = $whereSql;
$logicArray[] = $whereLogic;
}
// selectArray will have at least 1 element...
$SQL = '(SELECT email FROM subscribers WHERE '.$selectArray[0];
// iterate through array starting with 2nd element
for ($i = 1; $i < count($selectArray); $i ++) {
if ($logicArray[$i] == 'not_in') {
if (empty ($selectArray[$i -1])) // check if no previous where clause [protect against 'SELECT a FROM b WHERE AND NOT' ]
$SQL .= ' NOT ('.$selectArray[$i].')';
else
$SQL .= ' AND NOT ('.$selectArray[$i].')';
} else
$SQL .= ') UNION (SELECT email FROM subscribers WHERE '.$selectArray[$i];
}
$SQL .= ')';
var_dump($groupArray);
var_dump($logicArray);
var_dump($selectArray);
return $SQL;
}
And now, the oldschool pre PR8 WHERE generation: it's loop prone, and not as clear.
// returns the proper WHERE syntax for SQL. If append is TRUE, no "WHERE" will be prefixed
function whereSql($append = FALSE) {
$sql = "";
// If criteria exists for the group, begin forming a WHERE clause
if ($this->isCriteria()) {
// create array to translate bMail logic to valid mySql syntax
$logicTbl = array ();
$logicTbl['is_equal'] = "=";
$logicTbl['not_equal'] = "<>";
$logicTbl['is_more'] = ">";
$logicTbl['is_less'] = "<";
$logicTbl['is_true'] = "='on'";
$logicTbl['not_true'] = "IS NULL";
$where = array ();
// for each criteria, add to the where array like (crit = VAL or crit = VAL2 or ...)
foreach ($this->_critLogic as $key => $logic) {
// if user subscriber is/or is not supposed to be in another group, recursively call this function w/ another group loaded
if ($logic == "is_in") {
foreach ($this->_critValue[$key] as $gid) {
$newGroup = new MailGroup($this->_dbo, $gid);
$where[] = "(".$newGroup->whereSql(TRUE).")";
}
}
elseif ($logic == "not_in") {
foreach ($this->_critValue[$key] as $gid) {
$newGroup = new MailGroup($this->_dbo, $gid);
$where[] = "NOT (".$newGroup->whereSql(TRUE).")";
}
}
elseif ($logic == "is_true") {
$where[] = "(d".$this->_critTarget[$key].$logicTbl[$logic].")";
}
elseif ($logic == "not_true") {
$where[] = "(d".$this->_critTarget[$key].$logicTbl[$logic].")";
} else {
$str = "(";
$i = count($this->_critValue[$key]) - 1;
foreach ($this->_critValue[$key] as $k => $val) {
$str .= "d".$this->_critTarget[$key].$logicTbl[$logic]." '".$val."'";
if ($k < $i)
$str .= " or ";
}
$str .= ")";
$where[] = $str;
$str = "";
}
}
if ($append == FALSE)
$sql = "WHERE ";
// check for type of filter here -- ie. any or all
if ($all = TRUE) {
$i = count($where) - 1;
foreach (array_keys($where) as $key) {
$sql .= $where[$key];
if ($key < $i)
$sql .= " AND ";
}
} else {
$i = count($where) - 1;
foreach (array_keys($where) as $key) {
$sql .= $where[$key];
if ($key < $i)
$sql .= " OR ";
}
}
}
return $sql;
}
