working on: filter in music views reloaded (1 Viewer)

[JulianBuss]

I want to inform the community that I' working on the following:

As written in earlier posts I'm not satisfied with the filtering possibilities in music views: one can create custom views and create a filter for a view level, but the filtered level remains visisble.

Use case: I have music tagged with genres like "Pop slow", "Pop fast", "Pop party", "Pop rock", "Pop live" and so on.
No I want to have a view showing all pop songs, that means everything having the word "Pop" in it's genre.

Currently I can create a custom view with a first level showing "genre" and with thea filter "like %Pop%". But when using the view, it shows me "Pop slow", "Pop fast" etc. first instead of the albums, artist or whatever.

In Meedio there is the possibility to create a filter for an entire view, not only for a specific view level.

MP does not have that, so I started to implement it.

While digging around I found that in MusicViewHandler.cs there is different SQL used for album, genre, artist and other views. For example, the artist view only uses the artist table of the database, the album view uses a join of artist and album view.

So when I want to filter that by genre, it's not possible because the gerne information is not present in that situation.

After some thinking I decided to create a new, standard virtual SQL table used by *all* music views. This virtual table is the result of the inner join of the artist, album, genre, song and path tables. Because I only use certain columns (and not all colums) it's quite fast.

Depending on the view type (album, artist, genre...) this virtual table is grouped by some criteria, for example by album name.

Advantage: we have only one basic SQL statement for all view types, and that SQL is just complemented by a "group by" and "order by" clause, depending on the view we want to display.

That saves some lines of code and makes it easier to read.

AND: with that we have the chance to create any custom filter in any view. For example, I can add a filter by genre in the album, artist or any other view, just by extending the "where" clause.

I have to do some more tests and I have to check how to add a GUI element for entering a filter.

Because this modification might have some impact on other things, I have to coordinate it with SteveV, who is on vacation at the moment. So it might take some days before I can finish this modification and submit it as patch.

 

[rtv]

I do not see big problems with this at first glance..

I'd appreciate your work - especially if there can be configured more than one "group by" in the future

 

[JulianBuss]

Update: I' nearly done with the code in database.cs and MusicViewHandler.cs . I made a lot of code in MusicViewHandler.cs -> Execute() obsolete... less code is always better if you ask me

Now I have to modify the setup GUI so that one can enter a filter for a view.

What do you mean with "more than one 'group by'"?
What would the use case be?

P.S. the more I dive into MP code, the more I like it

 

[Lyxalig]

I'm hoping this is going to help me out with my "view"-problems.

Problems: I have a lot of singles that has album-tag because of it's origin, I have lots of compilation-albums, and I have uncomplete albums.

What I'm looking for is a way to completely set the requisites for the views. As I know sql, I would find it very useful to be able to write my own sql-statements.

Ex for config;

Albumview-> add new view: "Compilations"
Albumview-> add new view: "Single artist"

The code for the first one would be:

SELECT idAlbum
FROM album
WHERE inumArtists > 1

And the second one:

SELECT idAlbum
FROM album
WHERE inumArtists == 1

As I have "Albums" with only one artist (inumArtists == 1) some logic should be added to make sure this album has more than a few tracks.
That renders the "Single artist"-album view code:

SELECT idAlbum
FROM album
WHERE inumArtists == 1 
AND idArtist IN
	(SELECT idArtist
	FROM song
	GROUP BY idArtist
	HAVING COUNT(*) >=7)

The views should have a sortby handeler as well, but in my opinon; Beeing able to fully customize the views would be awsome, as well as people who know sql could make custom-views for any user, and it can alsom be posted in a seperate list or something...

BTW: I would love a list of views in the GUI to complement the changes, look something like this:

Views

-Album
+Single artist
+Compilation
-Artist
+ Favorites (lots of songs for example..)
+ Old-timers (Earlier than ???)
+ One-Hit-Wonders (One or two songs)
+ Newcomers


and so on.......

What needs to be done?

 

[Lyxalig]

One more thing: I would gladly create all the mentioned queries and more..

 

[JulianBuss]

donnow, maybe you can archive your goals with my modification.
What I do in the background: I have one common SQL select statement used for all views (including the custom views):

select album.idAlbum,album.strAlbum,artist.idArtist,artist.strArtist,song.idSong,song.strTitle,genre.idGenre,genre.strGenre,song.iTrack,song.iDuration,song.iYear,song.iTimesPlayed,song.iRating,song.favorite,path.strPath,song.strFileName from song,album,artist,genre,path

together with one common where clause:

where song.idAlbum = album.idAlbum and song.idArtist = artist.idArtist and song.idGenre = genre.idGenre and song.idPath = path.idPath

and with a group by and order by clause depending on the first view level, for example for a view showing the genre first:

group by strGenre order by strGenre

You can extend this automatically generated SQL by your own additions to the where clause, for example:

strGenre like '%Pop%'

Because the select statement includes all useful information from all tables, you can add where clauses as you like.

So as far as I can see your requirements should be doable. If not we have to talk again.
But we have to keep in mind that this is quite a power feature for very specific users. It must not interfere with the simple way to define custom views.
[/code]

 

[Lyxalig]

This looks good.. would it be possible to have a look at your current source, so that I can try it out? fredrik (ata) lyxalig.com

 

[JulianBuss]

not ready yet, I have a problems modifying the configuration program (see my other post here: https://forum.team-mediaportal.com/viewtopic.php?t=17751 )

 

[Lyxalig]

So i get that the "commonfilter" is supposed to hold the additional sql for the where-clause?

I' not sure if I get this right but:

What I would like is to have a filter at "top-level" that defines a list used by the following "levels". At the current artist-view that would be a single sql-sentence that would define what songs the concurrent levels gets?

 

[JulianBuss]

exactly that's what I want to archive.