How can I filter a record in same table with latest created date

[rahul100885]

Hi All,

I want to filter my result on table which have following structure,

id | name | dept_id | created_at
------------------------------------------------
1 | rahul | 2 | 2009-12-11 01:31:18.43
2 | rahul1 | 2 | 2009-10-11 12:24:18.50
3 | rahul3 | 2 | 2009-11-11 11:54:18.34
4 | demo1 | 3 | 2009-10-11 07:45:18.26
5 | demo2 | 3 | 2009-12-11 12:46:18.44
6 | demo3 | 3 | 2009-11-11 09:36:18.36
7 | deo1 | 4 | 2009-11-11 08:51:18.41
8 | deo3 | 4 | 2009-12-11 05:26:18.44

In this structure I want to index only those records from a groups of
dept_id which have latest created_at date

OR you can say

distinct dept_id with latest created_at date.

For above scenario how can I specify a where condition into
define_index part of model.

or scope or any think which I can specify after index done.

[Pat Allan]

There's #where and #group_by methods in the define_index block you can use to customise the generated SQL query - this may allow you to get the records you want. You can find the syntax in the second-last section of this page:
http://freelancing-god.github.com/ts/en/indexing.html

--
Pat

> --
>
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
>
>

[rahul100885]

What you suggested is not useful for me.

Since I expect following data only to be indexed from above data

id | name | dept_id | created_at
------------------------------------------------
1 | rahul | 2 | 2009-12-11 01:31:18.43

5 | demo2 | 3 | 2009-12-11 12:46:18.44

8 | deo3 | 4 | 2009-12-11 05:26:18.44

[Pat Allan]

To be honest, I'm not sure if such a configuration is possible - Thinking Sphinx is built to get every row back, not group rows of the model together.

You can manually edit the SQL query if you like, though - and then reindex using rake ts:reindex instead of the normal ts:index - this will avoid the conf file being overwritten.

--
Pat