Handle checkLists in the Shortcut query builder, and in the SQL. Fixe…

…s bug #11871.
plainblack · Oct 5, 2010 · 97ffe51 · 97ffe51
1 parent 85e1815
commit 97ffe51
Show file tree

Hide file tree

Showing 3 changed files with 27 additions and 10 deletions.
diff --git a/docs/changelog/7.x.x.txt b/docs/changelog/7.x.x.txt
@@ -7,6 +7,7 @@
  - fixed #11894: Europe London timezone decrements birth date
  - fixed #11857: make page printable?
  - fixed #11891: Shop credit not displayed in payment method screen
+ - fixed #11871: Metadata display and criteria builder problems
 
 7.10.1
  - fixed #11851: Story Topic: top story variables should be available all the time

diff --git a/lib/WebGUI/Asset/Shortcut.pm b/lib/WebGUI/Asset/Shortcut.pm
@@ -38,6 +38,9 @@ sub _drawQueryBuilder {
 			"!=" => $i18n->get("isnt")
 		};
 	}
+	$operator{checkList} = {
+		"+~" => $i18n->get("contains"),
+	};
 	$operator{integer} = {
 		"=" => $i18n->get("equal to"),
 		"!=" => $i18n->get("not equal to"),
@@ -93,6 +96,8 @@ sub _drawQueryBuilder {
 		# The value select field
 		my $valFieldName = "val_field".$i;
         my $options = $fields->{$field}{possibleValues};
+        ##Only allow one at a time to be selected, and work with current JS for choosing which one.
+        $fieldType = 'radioList' if $fieldType eq 'checkList';
 		my $valueField = WebGUI::Form::dynamicField($session,
 			fieldType=>$fieldType,
 			name=>$valFieldName,
@@ -601,44 +606,49 @@ sub getShortcutByCriteria {
 	# |              |             |
 	# |- $field      |_ $operator  |- $value
 	# |_ $attribute                |_ $attribute
-	my $operator = qr/<>|!=|=|>=|<=|>|<|like/i;
-	my $attribute = qr/['"][^()|=><!]+['"]|[^()|=><!\s]+/i; 
+    my $operator = qr/<>|!=|=|>=|<=|>|<|like|\+~/i;
+    my $attribute = qr/['"][^()|=><!]+['"]|[^()|=><!\s]+/i; 
 
 	my $constraint = $criteria;
 
 	# Get each expression from $criteria
 	my $db = $self->session->db;
 	my $counter = "b";
 	my @joins = ();
+    ##Transform the expression into valid SQL
 	foreach my $expression ($criteria =~ /($attribute\s*$operator\s*$attribute)/gi) {
 		# $expression will match "State = Wisconsin"
 
         	my $replacement = $expression;	# We don't want to modify $expression.
 						# We need it later.
 		push(@joins," left join metaData_values ".$counter."_v on a.assetId=".$counter."_v.assetId ");
 		# Get the field (State) and the value (Wisconsin) from the $expression.
-	        $expression =~ /($attribute)\s*$operator\s*($attribute)/gi;
+	        $expression =~ /($attribute)\s*($operator)\s*($attribute)/gi;
 	        my $field = $1;
-	        my $value = $2;
+            my $current_operator = $2;
+	        my $value = $3;
 
 		# quote the field / value variables.
 		my $quotedField = $field;
 		my $quotedValue = $value;
 		unless ($field =~ /^\s*['"].*['"]\s*/) {
 			$quotedField = $db->quote($field);
 		}
-                unless ($value =~ /^\s*['"].*['"]\s*/) {
-                        $quotedValue = $db->quote($value);
-                }
+        unless ($value =~ /^\s*['"].*['"]\s*/) {
+            $quotedValue = $db->quote($value);
+        }
 
 		# transform replacement from "State = Wisconsin" to 
 		# "(fieldname=State and value = Wisconsin)"
 		my $clause = "(".$counter."_p.fieldName=".$quotedField." and ".$counter."_v.value ";
-	        $replacement =~ s/\Q$field/$clause/;
-	        $replacement =~ s/\Q$value/$quotedValue )/i;
+        $replacement =~ s/\Q$field/$clause/;
+        $replacement =~ s/\Q$value/$quotedValue )/i;
 
 		# replace $expression with the new $replacement in $constraint.
-	        $constraint =~ s/\Q$expression/$replacement/;
+        $constraint =~ s/\Q$expression/$replacement/;
+        if ($current_operator eq '+~') {
+            $constraint =~ s/and ${counter}_v\.value\s*\+~\s*$quotedValue/and FIND_IN_SET($quotedValue, REPLACE(${counter}_v.value,"\\n",","))/;
+        }
 		push (@joins, " left join metaData_properties ".$counter."_p on ".$counter."_p.fieldId=".$counter."_v.fieldId ");
 		$counter++;
 	}

diff --git a/lib/WebGUI/i18n/English/Asset_Shortcut.pm b/lib/WebGUI/i18n/English/Asset_Shortcut.pm
@@ -49,6 +49,12 @@ our $I18N = {
 		lastUpdated => 1053183804
 	},
 
+	'contains' => {
+		message => q|contains|,
+		lastUpdated => 1053183804,
+        context => q|to be a member of a set, inclusive|,
+	},
+
 	'1' => {
 		message => q|Asset to Mirror|,
 		lastUpdated => 1031514049