Difference between revisions of "Boolean Operations"

From JRiverWiki
Jump to: navigation, search
m (clarified space handling)
 
(14 intermediate revisions by 3 users not shown)
Line 1: Line 1:
===Mimicking other database functions like ''AND'', ''OR'' and ''XOR''===
+
{{see also|Expression Language}}
  
Probably the single most used expression tool is the if(...) expression. I use them everywhere from the slideshow caption to rename from properties to custom panes, columns or library fields. With a little lateral thinking, we can format MEDIA CENTER database expressions that while not quite as elegant, perform the subject functions using a series of "if(...)" questions.
+
The [[Expression Language]] does not have AND, OR and XOR [http://en.wikipedia.org/wiki/Logical_connective logical connectives]. However, these can be emulated using either the Math() expression or by using nested If() statements.
We use the other expression tools in conjunction with if(...) to ask questions, to which there can only ever be a yes or no answer, and then output text based on those answers.
 
Most commonly, if(...) will be used in conjunction with the IsEqual expression, or the IsEmpty expression. A comprehensive list of available functions can be found [http://wiki.jrmediacenter.com/index.php/Media_Center_expression_language here].<br>
 
Let's run through some examples using the IsEqual expression.
 
  
First we ask the question, then we tell the expression evaluator what to do if the answer is yes, followed by what to do if the answer is no.
+
In the general forms shown below, ''test1'' and ''test2'' are Boolean expressions such as IsEmpty(...), IsEqual(...) or Regex(...).  The TRUE and FALSE Booleans indicate the outcome of the connective test.
In its simplest form, it might look like so:<br>'''if(isequal([artist],cliff richard,1),''this will be rubbish'',this might be OK)'''
 
This asks: is the artist Cliff Richard? If yes, display "this will be rubbish", and if no, display "this might be OK"
 
Use that expression to make an expression based column (right click on a column header and choose columns > expression column) to see the expression in action:<br>
 
http://www.theganghut.co.uk/pics/ia/12/cliff.jpg
 
  
Now that's a nice, simple example of an if(...) based expression. It is quite possible to do far more complex things with these because it is perfectly acceptable to ask further if(...) based questions. Building on our "cliff richard" example:<br>
+
== OR ==
'''if(isequal([artist],cliff richard,1),if(isequal([name],wired for sound,1),please don't play this,this will be rubbish),this might be OK)'''<br>
+
The OR connective can be emulated using any of the constructs below:
Here, we've asked the question, "is the artist Cliff Richard" and if it is, is the track named "wired for sound"? If the track is called wired for sound, display "please don't play this", if the artist is cliff richard, but the track is not wired for sound, display "this will be rubbish", and if it is not cliff richard, display "this might be OK"<br>
 
http://www.theganghut.co.uk/pics/ia/12/cliff2.jpg
 
  
Placement of parenthesis and commas is important. For each opening parenthesis, there must be a closing one to complete the expression. Look at the first, simple expression above again.
+
  <tt>if(math(test1 | test2), TRUE, FALSE)</tt>
1st, we ask if the artist is cliff richard, the italicised part of the expression deals with what to do if the answer is yes and the expression ends, while the following normal text deals with what to do if the answer is no, and the expression ends. We could quite feasably ask another question here instead of ending the expression.
+
When writing complex expressions, I find it helps me to deal with each branch as I come to it, so I deal with each yes answer until I've finished asking questions, then back track down the string to deal with what to do if the answer is no.
+
  <tt>if(math(test1 + test2), TRUE, FALSE)</tt>
 +
 +
  <tt>if(test1, TRUE,
 +
      if(test2, TRUE, FALSE))</tt>
 +
   
 +
  <tt>if(isequal(isempty([album gain]):isempty([replay gain]), 1, 7), at least one empty, all not empty)</tt>
  
One final point when using the IsEqual expression...
+
Examples
Note that I've used the number one in the artist and name questions above? This tells the evaluator to do a case-insensitive text compare when asking the question. There are 8 different compare modes that can be used and you can read about them on the [http://wiki.jrmediacenter.com/index.php/Media_Center_expression_language#IsEqual.28....29:_Compares_values_and_outputs_a_.221.22_if_the_values_pass_the_test.2C_and_.220.22_if_they_don.27t_pass_the_test. expressions page]. Number eight can be particularly useful as it causes the expression to search for the given string anywhere in the field.
 
  
==Real World Examples==
+
if(math(isequal([artist], Nat King Cole) | isequal([artist], John Lee Hooker)), Legend, Other)
Now, three 'real world' examples which for me, are always the best way to work out what is going on:
+
===OR===
+
if(isequal([artist], Nat King Cole), Legend,
 +
      if(isequal([artist], John Lee Hooker), Legend, Other))
  
    IF ([Keyword]="gangster" OR [Keyword]="criminal"), bad guy, good guy"
+
If the artist is either Nat King Cole or John Lee Hooker, output ''Legend'', otherwise output ''Other''.
  
The keywords field could possibly contain hundreds of keywords seperated by semi-colons. Here, we use the number 8 to force a case-insensitive substring search of the [keywords] field. First, we ask if the word gangster is there, and if yes, show "bad guy". If no, we then ask if the word criminal is there, and if yes, show "bad guy". For all others, show "good guy". We've asked two questions, and in this example, the two closing parenthesis come at the end of the string, the first of the two closing the second question, and the last one closing the first question.<br>
+
if(isequal(isempty([album gain]):isempty([replay gain]), 1, 7), at least one empty, all not empty)
'''if(isequal([Keywords],gangster,8),bad guy,if(isequal([keywords],criminal,8),bad guy,good guy))'''
 
  
 +
If any of album gain or replay gain is empty, output ''at least one empty'', otherwise output ''all not empty''.  This works by using the concatenated output of the two isempty() functions, and using the sub-string search mode of isequal() to look for any true (''1'') value.
  
===XOR / AND===
+
== AND ==
    IF ([Keyword]="demon" XOR [Keyword]="angel"), supernatural, IF ([Keyword]="demon" AND [Keyword]="angel"), invalid, non-supernatural
+
The AND connective can be emulated using any of the constructs below:
  
Pay attention to the comma that comes after "invalid". The evaluator seperates its commands using commas, so, to tell the evaluator to treat this comma as output text rather than command seperator, it must be escaped using a forward slash. Let's begin by breaking the expression into sections...<br>
+
  <tt>if(math(test1 & test2), TRUE, FALSE)</tt>
    is "demon" a keyword? if yes, is "angel" also a keyword? if yes, then [keywords] contains "demon" AND "angel" so display "invalid, non-supernatural"<br>
+
'''if(isequal([keywords],demon,8),if(isequal([keywords],angel,8),invalid/, non-supernatural,'''
+
  <tt>if(math(test1 * test2), TRUE, FALSE)</tt>
 +
 +
  <tt>if(test1,
 +
      if(test2, TRUE, FALSE),
 +
      FALSE)</tt>
 +
 +
  <tt>if(isequal(test1:test2, 1:1), TRUE, FALSE)</tt>
  
That takes care of AND. Next, we deal with what to do if "demon" is present, but "angel" is not, so we tell the evaluator to output "supernatural" and close this branch of the expression. This takes care of the first half of the XOR question.<br>
+
Examples
'''if(isequal([keywords],demon,8),if(isequal([keywords],angel,8),invalid/, non-supernatural,supernatural)'''
 
  
This branch of the expression ends here. There are no more questions to ask and a closing parenthesis is entered. Next, we deal with what to do if "demon" is not in the [keywords] field..<br>
+
if(math(isequal([artist], Elton John) & below([Year], 1976)), In Prime, Washed Up)
    The [keywords] field does not contain "demon", does it contain "angel"? if yes, output "supernatural"
 
''',if(isequal([keywords],angel,8),supernatural,'''<br>
 
and that takes care of the second part of the XOR question.
 
  
The only thing left now, is to tell the evaluator what to do about all the files that contain neither "demon" nor "angel"
+
if(isequal([artist], Elton John),
If you want it to return nothing for those files, all that's left to do is close the expression, as shown below. If you would like a label for those other files, place your text between the closing comma and parenthesis. The final expression looks like so:<br>
+
  if(compare([year], <, 1976), In Prime, Washed Up),
'''if(isequal([keywords],demon,8),if(isequal([keywords],angel,8),invalid/, non-supernatural,supernatural),if(isequal([keywords],angel,8),supernatural,)'''<br>
+
  Unknown)
  
There is one fly in the ointment here and I don't think it can be worked around. Note that we are doing "substring" searches here. This means that keywords like "demonstrate" or "angelic" would produce matches for "demon" and "angel", possibly skewing the results somewhat, as shown below:<br>
+
If the artist is Elton John and the year is prior to 1976, output ''In Prime'', otherwise output ''Washed Up''. If the artist is not Elton John, output ''Unknown''.
  
http://www.theganghut.co.uk/pics/ia/12/supernatural.jpg
+
  if(isequal(isempty([album gain]):isempty([replay gain]), 1:1), both empty, both not empty)
  
 +
If both album gain and replay gain are empty, output ''both empty'', otherwise output ''both not empty''.  This works by concatenating the output of the two isempty() functions into a single string and comparing it to the desired true/true values as a string ''1:1''.  This form can be used in a variety of ways.
  
===AND===
+
== XOR ==
    IF ([Keyword]="smart" AND [keyword]="hottie"), compatible, incompatible
+
The XOR connective can be emulated using any of the constructs below:
  
First ask if keywords contains smart, if the answer is yes, ask if it contains hottie, if the answer is yes, output compatible, otherwise, output incompatible. Output incompatible if keywords does not contain smart.<br>
+
  <tt>if(math(test1 - test2), TRUE, FALSE)</tt>
'''if(isequal([keywords],smart,8),if(isequal([keywords],hottie,8),compatible,incompatible),incompatible)'''
+
 +
  <tt>if(test1,
 +
      if(test2, FALSE, TRUE),
 +
      if(test2, TRUE, FALSE))</tt>
  
 +
Examples
  
==Q & A==
+
  if(math(isequal([artists], Michael Jackson, 8) - isequal([artists], Paul McCartney, 8)), Play It, Skip It)
    What if the there are other keywords... In other words, will [Keyword]=jim match if the keyword field is actually filled with "jim, bob, sue" (three different keywords)?
 
Expressions work with raw data, so, [keywords]=jim will match from "jim;bob;sue" if you use one of the substring compare types (7 or 8).
 
  
    Are quotes needed around search items that contain spaces? Does quoted text work?
+
  if(regex([artists], /#\bMichael Jackson\b#/),
again, expressions work with raw data, so if you use quotes, the evaluator will try to match on them too. spaces are fine. Spaces are interpreted literally in all areas, except immediately after a comma in a function. However, a space can be inserted immediately after a comma by precediing it with a / escape character.
+
    if(regex([artists], /#\bPaul McCartney\b#/), Skip It, Play It),
 +
    if(regex([artists], /#\bPaul McCartney\b#/), Play It, Skip It))
  
    These pages are hopeless, I still have unanswered questions, what to do?
+
If either artist, but not both, is Michael Jackson or Paul McCartney, output ''Play It'', otherwise output ''Skip It''. The example assumes a custom list field named Artists which has a list of relevant artists.
If you don't mind, you could ask your questions in [http://yabb.jriver.com/interact/index.php?topic=44945.0 this thread] in the MEDIA CENTER support forums, where you should get a quick response. If all the questions are contained in a single place, it should help these pages to develop by keeping references in a less fragmented manner.
 
  
[[User:Marko|marko]]
+
[[Category:Expression Language]]

Latest revision as of 00:58, 24 January 2016

The Expression Language does not have AND, OR and XOR logical connectives. However, these can be emulated using either the Math() expression or by using nested If() statements.

In the general forms shown below, test1 and test2 are Boolean expressions such as IsEmpty(...), IsEqual(...) or Regex(...). The TRUE and FALSE Booleans indicate the outcome of the connective test.

OR

The OR connective can be emulated using any of the constructs below:

  if(math(test1 | test2), TRUE, FALSE)

  if(math(test1 + test2), TRUE, FALSE)

  if(test1, TRUE,
     if(test2, TRUE, FALSE))

  if(isequal(isempty([album gain]):isempty([replay gain]), 1, 7), at least one empty, all not empty)

Examples

if(math(isequal([artist], Nat King Cole) | isequal([artist], John Lee Hooker)), Legend, Other)

if(isequal([artist], Nat King Cole), Legend,
     if(isequal([artist], John Lee Hooker), Legend, Other))

If the artist is either Nat King Cole or John Lee Hooker, output Legend, otherwise output Other.

if(isequal(isempty([album gain]):isempty([replay gain]), 1, 7), at least one empty, all not empty)

If any of album gain or replay gain is empty, output at least one empty, otherwise output all not empty. This works by using the concatenated output of the two isempty() functions, and using the sub-string search mode of isequal() to look for any true (1) value.

AND

The AND connective can be emulated using any of the constructs below:

  if(math(test1 & test2), TRUE, FALSE)

  if(math(test1 * test2), TRUE, FALSE)

  if(test1,
     if(test2, TRUE, FALSE),
     FALSE)

  if(isequal(test1:test2, 1:1), TRUE, FALSE)

Examples

if(math(isequal([artist], Elton John) & below([Year], 1976)), In Prime, Washed Up)
if(isequal([artist], Elton John),
  if(compare([year], <, 1976), In Prime, Washed Up),
  Unknown)

If the artist is Elton John and the year is prior to 1976, output In Prime, otherwise output Washed Up. If the artist is not Elton John, output Unknown.

 if(isequal(isempty([album gain]):isempty([replay gain]), 1:1), both empty, both not empty)

If both album gain and replay gain are empty, output both empty, otherwise output both not empty. This works by concatenating the output of the two isempty() functions into a single string and comparing it to the desired true/true values as a string 1:1. This form can be used in a variety of ways.

XOR

The XOR connective can be emulated using any of the constructs below:

  if(math(test1 - test2), TRUE, FALSE)

  if(test1,
     if(test2, FALSE, TRUE),
     if(test2, TRUE, FALSE))

Examples

 if(math(isequal([artists], Michael Jackson, 8) - isequal([artists], Paul McCartney, 8)), Play It, Skip It)
 if(regex([artists], /#\bMichael Jackson\b#/),
    if(regex([artists], /#\bPaul McCartney\b#/),  Skip It, Play It),
    if(regex([artists], /#\bPaul McCartney\b#/),  Play It, Skip It))

If either artist, but not both, is Michael Jackson or Paul McCartney, output Play It, otherwise output Skip It. The example assumes a custom list field named Artists which has a list of relevant artists.