Hi Douglas A cleaner approach could be to force the join using your current attribute setup, and then add a second one that makes use of that:
define_index do has picks.weekly_list.from, :as => :all_dates_added has "MAX(UNIX_TIMESTAMP(weekly_lists.from))", :as => :date_added, :type => :datetime end The table/alias might be wrong - depends on how ActiveRecord generates the joins. Give it a shot, and the manual statement as needed depending on what's in development.sphinx.conf Cheers -- Pat On 08/01/2009, at 2:31 PM, Douglas wrote: > > Hey, > > I've read that Sphinx doesn't use MVAs for sorting results. So having > a define_index block like below doesn't work for sorting by > "date_added" where "from" is a datetime type on WeeklyList > > class FlightDeal > define_index do > has picks.weekly_list.from, :as => :date_added > end > end > > What I really want to use as the value for sorting is the latest date > from the list. I thought of using a SQL fragment like this: > > has "(select wl.from from weekly_lists as wl left outer join picks > as p on (wl.id = p.weekly_list_id) left outer join flight_deals as fd > on (p.deal_type = 'FlightDeal' and p.deal_id = fd.id) order by wl.from > where fd.id = $id limit 1)", :as => :date_added, :type => :datetime > > How can I run the query so that its getting the latest date value for > each FlightDeal rather than for the entire flight_deals table? > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
