The reason for this is twofold.
Firstly, you're indexing the dates as a field, not an attribute. So Sphinx is
just treating this as text, not dates. Ideally, you probably want this as an
attribute instead.
But even if you change it to an attribute, then the next challenge is that
you're sorting on an array of integers per record - and Sphinx isn't smart
enough to figure out what you want. What you'll need to do is create another
attribute for your sorting:
# MySQL
has "UNIX_TIMESTAMP(MAX(posts.created_at))", :as => :last_post_created_at,
:type => :datetime
# PostgreSQL
has "cast(extract(epoch from MAX(posts.created_at)) as int)", :as =>
:last_post_created_at, :type => :datetime
It's also worth noting that if you don't want the normal posts.created_at
values as an attribute, and you're not using the posts association anywhere
else in your index definition, then you'll need to force the join to that model:
join posts
Searching becomes:
User.search search,
:conditions => conditions,
:with => with,
:order => 'last_post_created_at DESC, created_at DESC',
:per_page => 9,
:page => page
Cheers
--
Pat
On 14/03/2011, at 9:17 AM, rtacconi wrote:
> Hi,
>
> I have a user model connected to post (has_many :posts).
>
> This is my index:
>
> define_index do
> indexes first_name, :sortable => true
> indexes last_name, :sortable => true
> indexes city
> indexes state
> indexes country
> indexes home_climate_zones
> indexes posts.created_at, :as => :posts, :sortable => true
>
> has created_at, updated_at
> has show, :type => :boolean
> has aid_work, :type => :boolean
> has consultant, :type => :boolean
> has pdc_teacher, :type => :boolean
> has "lat", :as => :lat, :type => :float
> has "lng", :as => :lng, :type => :float
>
> set_property :delta => true
> end
>
> I want to get the users, ordered by the last post they have maid, then
> by when the user has been created:
>
> self.search(search,
> :conditions => conditions,
> :with => with,
> :sort_mode => :extended,
> :order => "posts DESC, created_at DESC",
> :per_page => 9, :page => page)
>
> But they are not ordered by post.created_at. The following SQL query
> works as espected:
>
> self.paginate_by_sql("SELECT users.* FROM users, posts
> WHERE users.id = posts.user_id
> GROUP BY first_name, last_name
> ORDER BY posts.created_at DESC",
> :per_page => 9, :page => page)
>
> Do you have an idea why it is not ordering?
>
> --
> You received this message because you are subscribed to the Google Groups
> "Thinking Sphinx" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/thinking-sphinx?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Thinking Sphinx" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/thinking-sphinx?hl=en.
