Joomla database functions for building a query include a useful one for adding to the where clause with with a different condition (AND | OR) to the default you are using. 

But it has a gotcha if you are building a complex query with several possible WHERE elements


Here is the header for the function in /libraries/joomla/database/query.php in J3 and /libraries/vendor/joomla/src/DatabaseQuery.php in J4

 * Extend the WHERE clause with a single condition or an array of conditions, with a potentially
 * different logical operator from the one in the current WHERE clause.
 * Usage:
 * $query->where(array('a = 1', 'b = 2'))->extendWhere('XOR', array('c = 3', 'd = 4'));
 * will produce: WHERE ((a = 1 AND b = 2) XOR (c = 3 AND d = 4)
 * @param string $outerGlue The glue by which to join the conditions to the current WHERE conditions.
 * @param mixed $conditions A string or array of WHERE conditions.
 * @param string $innerGlue The glue by which to join the conditions. Defaults to AND.
 * @return JDatabaseQuery Returns this object to allow chaining.
 * @since 3.6
 public function extendWhere($outerGlue, $conditions, $innerGlue = 'AND')


Note that  it "Extends" the WHERE clause (the clue is also in the name).

If there is no other where clause then it fails with an error.

For me this arose when building conditions from a filter form in a list view for a component. To simplify an example suppose you have two possible filters - filter by category, and filter by tag. For the tag filter you want to have the option of selecting multiple tags and choosing whether to filter by any, all or none of the selected tags.

To filter by any selected tag then you need an OR condition in the where:
 WHERE tag1 IN (tags assigned to the item) OR WHERE tag2 IN (tags assigned to the item)

(tags assigned to the item) will be a subquery.

To filter by all of the selected tags you need to use AND, and to filter by both a category AND some tag condition you also need an AND condition.

So if the user selects to filter by a category and any of the assigned tags then the query looks like this:

WHERE cat = itemcategory AND (tag1 IN (assigned tags) OR tag2 IN (assigned tags))

bearing in mind there may be other filter conditions to be ANDed as well this is a case for using the extendWhere() query function if the user is selecting ANY of multiple tags to simply combine the selected tags with an OR

And here's the gotcha - suppose the user only is filtering by multiple tags combined with a ANY condition. ie there is no pre-existing WHERE clause to be extended. The extendWhere() function starts by replicating the existing where clauses and then appends the new ones - if there aren't any existing clauses then it can't replicate them and throws an untrapped error.

It ought to be possible to check whether the query already has any WHERE clauses - but I can't find out how. So my workaround for now if the requirement is for ANY of more than one selected tags to be present (ie needing an OR between the conditions) then add a dummy where clause  $query->where('1=1');

This at least ensures that the extendWhere() function will work even if there is no other filter selected.

Sample code for tag filtering from the model:

// $tagfilt is the array of selected tags from the tag field on the filter form
// $taglogic is a selector on the filter form to specify that any, or all, or none of the tags must be assigned
// NB there are special cases where no tags are selected and the taglogic is 'none' or 'all'
		if (empty($tagfilt)) {
		    $subQuery = '(SELECT content_item_id FROM #__contentitem_tag_map
 					WHERE type_alias LIKE '.$db->quote('com_xb%.person').')';
		    if ($taglogic === '1') {
// for taglogic=all we want only the items that have no tags
		        $query->where(' NOT IN '.$subQuery);
		    } elseif ($taglogic === '2') {
// for taglogic=none we want only items that are tagged 
		        $query->where(' IN '.$subQuery);
		} else {
		    $tagfilt = ArrayHelper::toInteger($tagfilt);
		    $subquery = '(SELECT tmap.tag_id AS tlist FROM #__contentitem_tag_map AS tmap
                WHERE tmap.type_alias = '.$db->quote('com_xbpeople.person').'
                AND tmap.content_item_id =';
		    switch ($taglogic) {
		        case 1: //all
		            for ($i = 0; $i < count($tagfilt); $i++) {
		                $query->where($tagfilt[$i].' IN '.$subquery);
		        case 2: //none
		            for ($i = 0; $i < count($tagfilt); $i++) {
		                $query->where($tagfilt[$i].' NOT IN '.$subquery);
		        default: //any
		            if (count($tagfilt)==1) {
		                $query->where($tagfilt[0].' IN '.$subquery);
		            } else {
						$conds = array();
						for ($i = 0; $i < count($tagfilt); $i++) {
							$conds[] = $tagfilt[$i].' IN '.$subquery;
		                $query->where('1=1'); //bodge to ensure there is a where clause to extend
		                $query->extendWhere('AND', $conds, 'OR');
		  } //end if $tagfilt



Comments powered by CComment