#musicbrainz-devel

hi guys

im trying to output artists with urls but cant get the correct join query

what do you have so far?

basically join artist id with entity0 on l_artist_url

then url id with entity1 of l_artist_url

they're gone :p

oops

People have surprisingly low patience in IRC

I think he found how quite quickly anyway

Well, they timed out, so it’s not necessarily due to lack of patience.

Perhaps they are still patiently waiting for a reply. :-)

which method is used on mb website to sort collections in the collection's list ?

probably database row id :/

zas: Yo. :)

(i.e. unsorted)

zas: I thought Sophist was using MB rel. dates for something in that comment earlier. I hadn't caught that it was a filesystem timestamp. :)

hmm, wait, on the website? those might actually be sorted...

nope, not sorted :)

.. or is it

time to create a new collection and see!

zas: case insensitive sort by collection name :)

hi guys. how can i select artists only with official homepages? im having trouble joining artist and url tables :-(

they are sorted � < a < A < b < B ... + mbid when name is the same

but i need to know where is the code doing it

it's probably sorted by musicbrainz_collate(name) coming out of the database

bobcat_: http://chatlogs.musicbrainz.org/musicbrainz-dev...

ianmcorvidae: according to https://github.com/metabrainz/postgresql-musicb... i shouldn't get the order i wrote ....

zas: https://github.com/metabrainz/musicbrainz-serve...

yes i got disconnected. wasnt lack of patience ;-)

it's consistent winth the 'older versions of ICU' order

er well, I don't see what the first character in your ordering is, but

It’s a with a grave accent.

anyway, yes, most things on the site are ordered by musicbrainz_collate(name), so that's probably the safest assumption

" ORDER BY musicbrainz_collate(name), editor_collection.id"

https://github.com/metabrainz/musicbrainz-serve... is likely the relevant one, you're quoting the one from the subscriptions bit

what is editor_collection.id ? mbid or internal ?

internal; gid is what mbids are stored as

huh, and L154 has the public list, which sorts only by musicbrainz_collate(name) and not id

I wonder why the subscribed one has also the sort by id

hmmm, so we can't ensure the same order in Picard, this id isn't returned by webservice

zas: I was mistaken, the id is only used in a fairly obscure place. the main collections list only uses the name

as mentioned, it's not actually sorted by that where you're likely to care, the line kepstin quoted was for subscriptions

ah ok

so when two collections have same name, they are randomly sorted

now, when the names are the same it's just sorted by whatever order it comes out of the DB, so that bit you won't be able to replicate

presumably the webservice will return them in the order they came out of the db

but then, naming two collections the same makes them nearly useless anyway :P

so if you use a stable sort, it should be ok

we could just make it so people can't do stupid things like that :P

we could let them suffer with their own mistake if they do, is more what I was suggesting :P

:)

well, if two or more collections can have the same name, we have to handle it ;) the best thing to do is to not allow it from start, stupidity solved ;)

to be specific, that I can't get xpaths to work on the xml returned from an xmlhttpresponse

hmm.

order by random() is even better if you ask me, CHAOS !

musicbrainz_collate() in python is ... ?

unsure, there's probably an ICU library you could replicate it with

yes, that's what i fear... dependency in sight

zas: that'll be a lot of fun ;) that function's written in C or C++, installed as a postgres plugin, and uses ICU to make collation strings with various options set

zas: if you can get a reasonable case-insensitive unicode sort, that's probably good enough.

well, it's nikki's fault ... PICARD-561

hmm. looks like it's just ICU's default collation key mode with only the numeric collation option added

(so e.g. "collection 10" sorts after "collection 2")

and ICU is just an implementation of the standard unicode collation algorithm

yes, but it will need an extra module for python imho, not sure it is worth

you want to get a proper unicode collation algorithm in there to deal reasonably with mixed scripts and stuff :/

http://stackoverflow.com/a/3413436 -> pyICU looks the way to go

but just to sort collection menu it looks a bit overkill

i.e. worse than sorting by unicode codepoint :/

I'm sure we could find some other places to use ICU once we have it :)

yes prolly

it seems to be fairly rare that things are an issue on linux, so that's not exactly unpredictable :P

;)

Hmm. Im still struggling. I want to add the external links to what I have so far: select artist.name,area.name from artist join area on (artist.area=area.id) where artist.name ~ 'Orchestra';

bobcat_: Links for the artist or the area?

chirlu`: for the artist

chirlu`: official homepage, twitter etc

Then you need to join the "url" table via "l_artist_url".

chirlu`: this is my first day with psql

… JOIN l_artist_url ON artist.id = l_artist_url.entity0 JOIN url ON l_artist_url.entity1 = url.id

or something like that.

(untested)

chirlu`: Thank you!

bobcat_: Oh, BTW, this will only return results that have at least one URL (and an area, for that matter).

If that’s not what you want, you will need to look into outer joins (LEFT JOIN etc.).

chirlu`: This almost works: select artist.name,area.name,url from artist join area on (artist.area=area.id) join l_artist_url on (artist.id=l_artist_url.entity0) join url on (l_artist_url.entity1=url.id) where artist.name ~ 'Orchestra';

chirlu`: but im getting each artist,name,area.name,url in a separate line when there are multiple urls

Yes, that’s how it usually works in SQL. :-)

bobcat_: Aggregate functions may help you: http://www.postgresql.org/docs/9.3/static/funct...

yikes

bobcat_: Btw, there will possibly soon be an artist type "orchestra".

:D

But to make use of it, you might need to join another table. ;-)