sphinx: sorting by range of an association

Let's say I have a 1:many relation:

Tree:Apples

Tree/Apple each have an primary key ID column and apple has some date attribute/column (created_at).

Using sphinx, I want to retrieve all trees, sorted by the number app开发者_高级运维les created during a given period of time. So, for example:

All trees, sorted by the total number of apples created between 1/1/2010 and 1/1/2011.

Is that possible?

---

So you have two tables

create table tree ( tree_id int unsigned primary key,...);

and

create table apple ( apple_id int unsigned primary key, tree_id int unsigned, created_at timestamp default current_timestamp,...);

So can then just build an index on apples

sql_query = select apple_id,tree_id,unix_timestamp(created_at) as created_at from apple

then run group by queries on it

$cl->setGroupBy('tree_id',SPH_GROUPBY_ATTR,'@count DESC');

The @count virtual attribute will give you the number of apples on that tree.

to set the filter

$cl->setFilterRange('created_at',strtotime('2010-01-01'),strtotime('2011-01-01'));

Also because you not using it, can set ranking to none

$cl->setRankingMode(SPH_RANK_NONE);

To be clear you just use a blank query

$res = $cl->Query('',$index);