sphinx - Sphinxsearch maintain sql ordering -

so have query similar https://stackoverflow.com/a/18462040/1768337 , can see, rows ordered according likes. let's create index using sql statement in link above. there way output sql statement gives? is, maintain ordering set in sql statement? tried rank rows following,

source popular {     type            = mysql      sql_host        = --------     sql_user        = --------     sql_pass        = --------     sql_db          = --------     sql_port        = 3306  # optional, default 3306      sql_query       = \         set @rank=0; \         select *, @rank := @rank + 1 rank \         \          ( \            select p.id id, p.search search, count(case when li.date > date_sub(curdate(), interval 1 day) li.id end) daily_likes, count(case when li.date > date_sub(curdate(), interval 7 day) li.id end) weekly_likes, count(li.id) total_likes \            `photo` p \            join `like` li \                on p.id = li.photo_id \            \              li.date > date_sub(curdate(), interval 30 day) \            group \              p.id \           ) dt \         order \            case when daily_likes > 3 daily_likes else 0 end desc, \            case when weekly_likes > 5 weekly_likes else 0 end desc, \            total_likes desc \         limit 30      sql_attr_uint       = daily_likes     sql_attr_uint       = weekly_likes     sql_attr_uint       = total_likes     sql_attr_uint       = rank      sql_query_info      = select * photo id=$id } 

i tried later use $s->setsortmode(sph_sort_extended, '@relevance desc, rank asc');. however, seems custom variable doesn't work in sphinx.

so issue index sql statement , output using sphinx (php api) - maintaining sql ordering.

okay barryhunter (the savior) helping out problem. mentions in comments set should in sql_query_pre. conf file like:

sql_query_pre   = set @rank=0 sql_query       = \     select *, @rank := @rank + 1 rank \     \      ( \