CODEWITHSUNDEEP
neo4jcypher
JugalS
JugalS

Reputation: 33

Conditional Order By Clause In Cypher Query Neo4j

Hi I want to sort my graph results by two filters..

My Cypher Query looks like this..

MATCH (n:User{user_id:304020})-[r:know]->(m:User) with m MATCH (m)-[s:like|create|share]->(o{is_active:1})

with m, s, o, (toInt(timestamp()/1000)-toInt(o.created_on))/86400 as days,
(toInt(timestamp()/1000)-toInt(o.created_on))/3600 as hours, 
(1- round(o.impression_count_all/20)/50) as low_boost 

with m,s,o,days,low_boost,hours, 
                CASE 
                    WHEN days > 30 THEN 0.05 
                    WHEN days >=20 AND days <=30 THEN 0.1 
                    WHEN days >=10 AND days <=20 THEN 0.2 
                    WHEN days >=5 AND days <=10 THEN 0.4 
                    WHEN days >=2 AND days <=5 THEN 0.5 
                    WHEN days =1 THEN 0.6 
                    WHEN days < 1 THEN 
                    CASE 
                        WHEN hours <= 2 THEN 1 
                        WHEN hours > 2 AND hours <= 8 THEN 0.9 
                        WHEN hours > 8 AND hours <= 16 THEN 0.8 
                        WHEN hours > 16 AND hours < 23 THEN 0.75 
                        WHEN hours >= 23 AND hours <= 24 THEN 0.7 
                    END 
                END as rs,

                CASE 
                    WHEN low_boost > 0 THEN low_boost 
                    WHEN low_boost <= 0 THEN 0 
                END as lb 
where has(o.trending_score_all) and has(o.impression_count_all) and not(o.is_featured=2)

RETURN distinct o.story_id as story_id,
(o.trending_score_all*4) as ts, (o.trending_score_all + rs + lb) as final_score, 
count(s) as rel_count,max(s.activity_id) as id, toInt(o.created_on) as created_on

ORDER BY (CASE WHEN ts > 3 THEN final_score desc, rel_count desc ELSE ts) END) DESC
skip 0 limit 10;

Now If ts > 3 ,I want to sort results by both final_score and rel_count ELSE srt only by ts.. Please modify order by..

Upvotes: 1

Views: 802

Answers (2)

cybersam
cybersam

Reputation: 66947

Does this much simplified query (which uses a single argument for ORDER BY) work for you?

MATCH (u:User)-[r:like]->(s:Story)
WITH s, (s.trending_score_all*4) AS ts
RETURN DISTINCT s.story_id, ts, TOINT(s.impression_count_72)
ORDER BY (CASE WHEN ts > 3 THEN ts ELSE TOINT(s.impression_count_72) END) DESC
LIMIT 10;

[EDITED]

If you need to sort by a varying number of values (depending on the situation) you have to use a workaround, as Cypher does not support that directly.

For example, suppose when (ts > 3) you wanted to order by ts DESC and then by s.story_id ASC. In this situation, you could change the above ORDER BY clause to this:

ORDER BY
  (CASE WHEN ts > 3 THEN ts ELSE TOINT(s.impression_count_72) END) DESC,
  (CASE WHEN ts > 3 THEN s.story_id ELSE NULL END) ASC

By using NULL (or any literal value) in this way, you can have any of the sub-sorts effectively do nothing.

Upvotes: 4

stdob--
stdob--

Reputation: 29172

1) You use pagination (skip and limit)

2) If I understand what you need, then add "else" to sort:

UNWIND RANGE(1,100) as i
WITH i, rand()*5 as x, toInt(rand()*10) as y
RETURN i, x, y, CASE WHEN x>3 THEN 1 ELSE 0 END as for_sort 
    ORDER BY 
        CASE 
            WHEN for_sort=1 THEN x ELSE y END DESC,
        CASE 
            WHEN for_sort=1 THEN y ELSE x END DESC

Upvotes: 1

Related Questions