From: Brian Candler <b.candler@...>
Date: 2009-01-20T06:11:02+09:00
Subject: Re: comparing date problem

Sam Ginko wrote:
> I'm using thinking sphinx search engine.

Sphinx is a new one on me - but looking at the project page, it looks 
like it needs either a MySQL or PostgreSQL database sitting under it.

> an sql syntax in thinking 
> sphinx looks like this
> 
> @allRooms = Room.search :conditions => {:created_at => here is where I 
> should have a statement}

I think you mean Room.find here, if this is ActiveRecord,  in order to 
do a SQL "select" on a table in the underlying database. If I'm right, 
this is nothing to do with Sphinx's free-text searching. So it would be 
something like

Room.find(:all, :conditions => { ... })

The SQL you need is at the link I gave before: see "The Row Holding the 
Maximum of a Certain Column". All you need to do is to look at your 
ActiveRecord docs to formulate a suitable query. Untested:

--SELECT article, dealer, price
--FROM   shop
--WHERE  price=(SELECT MAX(price) FROM shop);

Shop.find(:first, :conditions => "price = SELECT MAX(price) FROM shop")

--SELECT article, dealer, price
--FROM shop
--ORDER BY price DESC
--LIMIT 1;

Shop.find(:first, :order => "price desc")

ActiveRecord docs are at http://ar.rubyonrails.com/ and there are also 
some very good Rails books you can buy with good ActiveRecord coverage.

For further help on ActiveRecord you'd probably get best results going 
to a Rails-specific mailing list.

Regards,

Brian.

P.S. I came across a blog post on Sphinx at 
http://kpumuk.info/ror-plugins/using-sphinx-search-engine-in-ruby-on-rails/ 
which may be helpful for when you get to the free-text searching.
-- 
Posted via http://www.ruby-forum.com/.