Cypher query with ORDER BY on translation with fallback

Question

I have the following data model:

• node entity BusinessObject
• node entity Translation containing a language and a text property
• relationship TRANSLATES from translation to the business object, potentially multiple

The translations allow me to present texts appropriate to the users language. For example, a user is able to read German, a business object is translated into English and German, then the UI displays German. In general, there's a fallback from the user's language to a default language (English).

Now, I would like to query for all BusinessObjects and order them by the translation which should include the fallback. Is that even possible with cypher?

EDIT: Example

BusinessObject A with Translations ("en", "XYZ English translation for A") and ("de", "Deutsche Übersetzung für A").

BusinessObject B with Translations ("en", "Some English translation for B") and ("fr", "Traduction français pour B").

A user U1 has language German ("de") and the fallback should be English ("en"). So, querying for business objects should result in list [A, B] because translation "Deutsch... für A" is before "Some En...for B" lexicographically when ordering ascending. On the other hand, when a second user U2 has language French, the result should be [B, A] because "Traduc... pour B" is before "XYZ Eng... for A". A third English user U3 should get result [B, A] because translation "Some... for B" is before "XYZ... for A".

So I really need the ordering done by the database if that is possible.

EDIT: Solution

With the great ideas from the answers, I came up with the following query:

MATCH (r:BusinessObject)
WITH r
OPTIONAL MATCH (r)<-[TRANSLATES]-(t:Translation)
WHERE t.language = 'de' // user language
WITH r, t.text as t1
OPTIONAL MATCH (r)<-[TRANSLATES]-(t:Translation)
WHERE t.language = 'en' // fallback
WITH r, coalesce(t1, t.text) as sort
RETURN DISTINCT r ORDER BY sort;

Surely, this hasn't best performance but that's ok for me. Thanks to all contributers!

Answer 1

You can use OPTIONAL MATCH with coalesce to do what you want.

MATCH (b:BusinessObject)
OPTIONAL MATCH (b)-[:TRANSLATES]-(t:Translation)
RETURN coalesce(t.language,'English')

Answer 2

I don't know a way to set the ORDER BY like that. But short of finding an answer for this if it exists, another option might be to just return you fallback separately.

WITH "English" as fallback 
MATCH (b: BusinessObject)-[:TRANSLATES]-(FBTr: Translation)
WHERE Translation.language = fallback
WITH fallback, FBTr, b
MATCH (b)-[:TRANSLATES]-(tr: Translation)
WHERE Translation.language <> fallback
RETURN collect(tr) as Translations, FBTr as Fallback

EDIT
Here's a simpler idea that sorts by language, putting the fallback last:

WITH 'English' as Fallback
MATCH (b:BusinessObject)-[:TRANSLATES]-(t)
WITH t, CASE t.language 
   WHEN Fallback THEN -1
   ELSE 0
END as sort
RETURN t ORDER by sort DESC, t.language

Maybe this is closer to what you're looking for.