Multiple-value fields

[Coises]

A couple thoughts about multiple-value fields, subsequent to writing this (http://forum.conceiva.com/showthread.php/7456-Active-Playlists-and-Genre-Filtering?p=29104#post29104):

I understand that the absence of order in multiple-value fields is a natural consequence of the obvious way of implementing them in a relational database. It probably doesn’t matter much for Genres. However, I just checked, and the same situation occurs with Actors. That’s not good: the order in which actors are billed has meaning, and shouldn’t be arbitrarily rearranged.

I think the current list of conditional criteria for multiple value fields is not very useful:

Contains
Does not contain
Ends with
Is
Is not
Starts with

As Elder-Sage thought, I suspect most people would expect Genre Is Comedy to select videos whose only Genre is Comedy; intuitively, we would think we should use Genre Contains Comedy to get videos where any Genre is Comedy (which is what Is actually does now, though Contains has the same effect in practice).

I suggest these conditions:

Has all of
Has any of
Has exactly
Has only
Does not have all of
Does not have any of
Does not have exactly
Does not have only

If we had videos with these Genres:
Comedy
Comedy, Drama
Comedy, Drama, Romance
Comedy, Romance
Drama
Drama, Romance
Romance
none (no Genres specified)

Genre Has all of Comedy, Romance would select:
Comedy, Drama, Romance
Comedy, Romance

Genre Has any of Comedy, Romance would select:
Comedy
Comedy, Drama
Comedy, Drama, Romance
Comedy, Romance
Drama, Romance
Romance

Genre Has exactly Comedy, Romance would select:
Comedy, Romance

Genre Has only Comedy, Romance would select:
Comedy
Comedy, Romance
Romance

Genre Does not have all of Comedy, Romance would select:
Comedy
Comedy, Drama
Drama
Drama, Romance
Romance
none

Genre Does not have any of Comedy, Romance would select:
Drama
none

Genre Does not have exactly Comedy, Romance would select:
Comedy
Comedy, Drama
Comedy, Drama, Romance
Drama
Drama, Romance
Romance
none

Genre Does not have only Comedy, Romance would select:
Comedy, Drama
Comedy, Drama, Romance
Drama
Drama, Romance
none

Note that with only a single value specified (e.g., Genre Has any of Comedy), Has all of and Has any of are the same (and are the same as what is now Is); Has exactly and Has only are the same; Does not have all of and Does not have any of are the same; and Does not have exactly and Does not have only are the same (and are the same as what is now Is not).

For compatibility, what is now Is could be translated to Has all of and Is not to Does not have exactly. Contains could stay as it is (perhaps with some attention to what happens when multiple values are specified in the conditional), but my guess is that most of the time people are using it because they think it’s needed to do what Is actually does. Does not contain is both confusing and useless as it stands.

[Paul]

Many thanks, Coises. All good thoughts and we'll reference them when we address the multi-value matching issue in an upcoming release.

[Coises]

I thought about this some more. It occurs to me that there is another approach. It’s not obvious to me which is more sensible.

Mezzmo could parse multiple-value fields as it does now (e.g., Genre into the mgofilegenre and mgofilegenrerelationship tables), but also keep the full value as a text string. Mezzmo would use the parsed version to build/maintain an Active Playlist hierarchy, but use the text value for Smart Playlists and Smart Filters.

The obvious downside is that it’s messy database design: the same data is being stored twice.

The upside is that it uses the parsed data where it’s needed—to do hierarchies properly—but it avoids introducing a new type of conditional into Smart Playlists and Filters. (Bugs aside, the current design does introduce a new type of conditional; it just uses the same names as the conditionals for simple text fields. They don’t, and can’t, really work the same.)

That might be easier for most users (who have never heard of a relational database) to understand. Is, Contains and so on would mean exactly the same thing for multiple-value fields as for simple text fields. On the programming side, conditionals for simple text fields already work; and retaining order (e.g., for the Actor list) wouldn’t require any special handling.

Compared to my last suggestion, the obvious upside is that this requires minimal explanation; the downside is a loss of flexibility. However, I would like to propose a remedy for that.

SQLite doesn’t include a regular expression engine, but it does provide a “hook” for defining an implementation of REGEXP in queries. (I’m assuming the conditionals for Smart Playlists and Filters get translated into components of SQL queries at some point.) Using a Perl-style regular expression implementation, if Matches were added as a Smart conditional for text fields, we could use conditions like:

Genre Matches Horror|Thriller
Genre Matches ^(?=.*Comedy).*Romance
Genre Matches ^Biography$|^(?=.*Documentary).*Biography
Keyword Matches ^(?!.*Adult).*Nature

Granted, only the first would make much sense to anyone unfamiliar with regular expressions; but most users would rarely want anything trickier than that, and the flexibility would be there when needed.

[Paul]

Thanks again, Coises. We're discussing this issue at the moment, so your thoughts are helpful.

[Paul]

Update: We have fixed the issues related to matching values for multi-value attributes in smart playlists and smart filters in active playlists in the latest version of Mezzmo (v4.1.0.0). We have also updated the FAQ regarding multi-value attributes - http://forum.conceiva.com/showthread.php/6225-Tutorial-Working-with-multiple-genres-actors-artists-etc