Boolean Operations: Difference between revisions

From wiki.jriver.com
Jump to navigation Jump to search
m (clarified space handling)
No edit summary
 
(17 intermediate revisions by 4 users not shown)
Line 1: Line 1:
{{see also|Expression Language}}
===Mimicking other database functions like ''AND'', ''OR'' and ''XOR''===


Media Center versions 27 and later now have [[And()]] and [[Or()]] functions included in the [[Expression Language]] but still does not have an XOR function.
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.
<br><br>Earlier versions do 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.


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.
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 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


== OR ==
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>
The OR connective can be emulated using any of the constructs below:
'''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>
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


<tt>if(math(test1 | test2), TRUE, FALSE)</tt>
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.
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.
<tt>if(math(test1 + test2), TRUE, FALSE)</tt>
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(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>


Examples
One final point when using the IsEqual expression...
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.


if(math(isequal([artist], Nat King Cole) | isequal([artist], John Lee Hooker)), Legend, Other)
==Real World Examples==
Now, three 'real world' examples which for me, are always the best way to work out what is going on:
if(isequal([artist], Nat King Cole), Legend,
===OR===
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 ([Keyword]="gangster" OR [Keyword]="criminal"), bad guy, good guy"


if(isequal(isempty([album gain]):isempty([replay gain]), 1, 7), at least one empty, all not empty)
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([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 ==
The AND connective can be emulated using any of the constructs below:
IF ([Keyword]="demon" XOR [Keyword]="angel"), supernatural, IF ([Keyword]="demon" AND [Keyword]="angel"), invalid, non-supernatural


<tt>if(math(test1 & test2), TRUE, FALSE)</tt>
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>
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>
<tt>if(math(test1 * test2), TRUE, FALSE)</tt>
'''if(isequal([keywords],demon,8),if(isequal([keywords],angel,8),invalid/, non-supernatural,'''
<tt>if(test1,
if(test2, TRUE, FALSE),
FALSE)</tt>
<tt>if(isequal(test1:test2, 1:1), TRUE, FALSE)</tt>


Examples
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>
'''if(isequal([keywords],demon,8),if(isequal([keywords],angel,8),invalid/, non-supernatural,supernatural)'''


if(math(isequal([artist], Elton John) & below([Year], 1976)), In Prime, Washed Up)
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>
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.


if(isequal([artist], Elton John),
The only thing left now, is to tell the evaluator what to do about all the files that contain neither "demon" nor "angel"
if(compare([year], <, 1976), In Prime, Washed Up),
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>
Unknown)
'''if(isequal([keywords],demon,8),if(isequal([keywords],angel,8),invalid/, non-supernatural,supernatural),if(isequal([keywords],angel,8),supernatural,)'''<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''.
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(isequal(isempty([album gain]):isempty([replay gain]), 1:1), both empty, both not empty)
http://www.theganghut.co.uk/pics/ia/12/supernatural.jpg


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 ==
The XOR connective can be emulated using any of the constructs below:
IF ([Keyword]="smart" AND [keyword]="hottie"), compatible, incompatible


<tt>if(math(test1 - test2), TRUE, FALSE)</tt>
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>
'''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


if(math(isequal([artists], Michael Jackson, 8) - isequal([artists], Paul McCartney, 8)), Play It, Skip It)
==Q & A==
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).


if(regex([artists], /#\bMichael Jackson\b#/),
Are quotes needed around search items that contain spaces? Does quoted text work?
if(regex([artists], /#\bPaul McCartney\b#/), Skip It, Play It),
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#/), 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.
These pages are hopeless, I still have unanswered questions, what to do?
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.


[[Category:Expression Language]]
[[User:Marko|marko]]

Latest revision as of 08:20, 12 December 2020

Media Center versions 27 and later now have And() and Or() functions included in the Expression Language but still does not have an XOR function.

Earlier versions do 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.