Stupid question, but... How can I get some number of random objects from the DB? images = Image.find ( something magic here ) # where images now contains three random Images. Thanks!
And actually, I only really need the ID of three random images. On Tue, 15 Mar 2005 20:44:07 -0800, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Stupid question, but... > > How can I get some number of random objects from the DB? > > images = Image.find ( something magic here ) > # where images now contains three random Images. > > Thanks! >
Joe Van Dyk wrote:> How can I get some number of random objects from the DB? > images = Image.find ( something magic here ) > # where images now contains three random Images.Image.find_all(nil, ''rand'', 3) translates to select * from images order by rand limit 3 jeremy
Thanks! I assumed it was like that, but was getting hung up on the syntax for the condition. (I was doing rand() ). Now, this will create three Image objects, right? So, if each image is 500KB, that''s at least a 1.5MB database transfer (right?). I really only need each image id. On Tue, 15 Mar 2005 20:52:50 -0800, Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote:> Joe Van Dyk wrote: > > How can I get some number of random objects from the DB? > > images = Image.find ( something magic here ) > > # where images now contains three random Images. > > Image.find_all(nil, ''rand'', 3) > translates to > select * from images order by rand limit 3 > > jeremy >
Hm. I''m using sqlite3, and it''s not liking the ''rand'' bit. I''m going to need to check if ruby-sqlite3 even supports rand(). On Tue, 15 Mar 2005 20:57:52 -0800, Joe Van Dyk <joevandyk-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thanks! I assumed it was like that, but was getting hung up on the > syntax for the condition. (I was doing rand() ). > > Now, this will create three Image objects, right? So, if each image > is 500KB, that''s at least a 1.5MB database transfer (right?). I > really only need each image id. > > > On Tue, 15 Mar 2005 20:52:50 -0800, Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote: > > Joe Van Dyk wrote: > > > How can I get some number of random objects from the DB? > > > images = Image.find ( something magic here ) > > > # where images now contains three random Images. > > > > Image.find_all(nil, ''rand'', 3) > > translates to > > select * from images order by rand limit 3 > > > > jeremy > > >
Joe Van Dyk wrote:> Now, this will create three Image objects, right? So, if each image > is 500KB, that''s at least a 1.5MB database transfer (right?). I > really only need each image id.Yes. The Active Record finder methods don''t let you specify which fields you want per-query so you''ll have to go directly to SQL (Image.find_by_sql or Image.connection.select_all). How about tossing them photos onto the filesystem? jeremy
Joe Van Dyk wrote:> I''m using sqlite3, and it''s not liking the ''rand'' bit. I''m going to > need to check if ruby-sqlite3 even supports rand().Looks like SQLite has random(). MySQL and PostgreSQL support rand(). jeremy
On Tue, 15 Mar 2005 21:21:54 -0800, Jeremy Kemper <jeremy-w7CzD/W5Ocjk1uMJSBkQmQ@public.gmane.org> wrote:> Joe Van Dyk wrote: > > Now, this will create three Image objects, right? So, if each image > > is 500KB, that''s at least a 1.5MB database transfer (right?). I > > really only need each image id. > > Yes. The Active Record finder methods don''t let you specify which > fields you want per-query so you''ll have to go directly to SQL > (Image.find_by_sql or Image.connection.select_all). How about tossing > them photos onto the filesystem?Doesn''t Image.find_by_sql also return full Image objects?
Hi! On Tue, 15 Mar 2005, Joe Van Dyk wrote the following:> Doesn''t Image.find_by_sql also return full Image objects?It depends. You have to specify a full SQL query and therefore can select what you need (= only the ID); bye Wolfgang
Remember, tho, that using random in the db query can be very expensive when the result set grows large because it forces the db to use sequential scan to sort the results. This is especially dangerous when you have some other, index-using constraints in your query. I would probably get all the picture id''s that match your constraints (if you have any) and then pick the random ones from them using ruby rand function. //jarkko On 16.3.2005, at 09:01, Wolfgang Klinger wrote:> > Hi! > > On Tue, 15 Mar 2005, Joe Van Dyk wrote the following: >> Doesn''t Image.find_by_sql also return full Image objects? > > It depends. You have to specify a full SQL query > and therefore can select what you need (= only the ID); > > > bye > Wolfgang > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Hello,
I''m doing it like this:
>questions = self.find_by_sql(
["select id from questions where area_id = ? and score = ?",
area_id, score])
>questions.sort! {rand(3) - 1}[1..count].collect do |q|
Question.find(q.id)
>end
So if you need only ids that collect thing is not necessary. If anyone
have any comments on this code please mail me cause it''s one of the
bottleneck for my app, and maybe I''m doing something wrong.
Thanks a lot.
Pepe
On 16.3.2005, at 10:12, Jarkko Laine wrote:
> Remember, tho, that using random in the db query can be very expensive
> when the result set grows large because it forces the db to use
> sequential scan to sort the results. This is especially dangerous when
> you have some other, index-using constraints in your query.
>
> I would probably get all the picture id''s that match your
constraints
> (if you have any) and then pick the random ones from them using ruby
> rand function.
>
> //jarkko
>
> On 16.3.2005, at 09:01, Wolfgang Klinger wrote:
>
>>
>> Hi!
>>
>> On Tue, 15 Mar 2005, Joe Van Dyk wrote the following:
>>> Doesn''t Image.find_by_sql also return full Image objects?
>>
>> It depends. You have to specify a full SQL query
>> and therefore can select what you need (= only the ID);
>>
>>
>> bye
>> Wolfgang
>>
>>
>> _______________________________________________
>> Rails mailing list
>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
> --
> Jarkko Laine
> http://jlaine.net
> http://odesign.fi
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
Josef Pospisil wrote:> I''m doing it like this: > >>questions = self.find_by_sql( > ["select id from questions where area_id = ? and score = ?", > area_id, score]) >>questions.sort! {rand(3) - 1}[1..count].collect do |q| > Question.find(q.id) >>end > > So if you need only ids that collect thing is not necessary. If anyone > have any comments on this code please mail me cause it''s one of the > bottleneck for my app, and maybe I''m doing something wrong.You can cut down the number of queries from N+1 to 2: module Enumerable def random_sample(n) sort_by { rand }.slice(0, n) end end class Question < ActiveRecord::Base SELECT_IDS_BY_AREA_AND_SCORE = "select #{primary_key} from #{table_name} where area_id=? and score=?".freeze def self.random_sample(area_id, score, n = 3) area_id = area_id.id if area_id.is_a?(Area) find(find_by_sql([SELECT_IDS_BY_AREA_AND_SCORE, area_id, score]).random_sample(n).map { |q| q.id }) end end Calling find with an array of ids will generate 1 "select * from questions where id in (?)" intead of N "select * from questions where id = ?". jeremy
Perfect!!! That''s what I was looking for. If you come to Prague I''ll buy you some beers :-) Pepe On 16.3.2005, at 22:29, Jeremy Kemper wrote:> Josef Pospisil wrote: >> I''m doing it like this: >> >>> questions = self.find_by_sql( >> ["select id from questions where area_id = ? and score = ?", >> area_id, score]) >>> questions.sort! {rand(3) - 1}[1..count].collect do |q| >> Question.find(q.id) >>> end >> >> So if you need only ids that collect thing is not necessary. If anyone >> have any comments on this code please mail me cause it''s one of the >> bottleneck for my app, and maybe I''m doing something wrong. > > You can cut down the number of queries from N+1 to 2: > > module Enumerable > def random_sample(n) > sort_by { rand }.slice(0, n) > end > end > > class Question < ActiveRecord::Base > SELECT_IDS_BY_AREA_AND_SCORE = "select #{primary_key} from > #{table_name} where area_id=? and score=?".freeze > > def self.random_sample(area_id, score, n = 3) > area_id = area_id.id if area_id.is_a?(Area) > find(find_by_sql([SELECT_IDS_BY_AREA_AND_SCORE, area_id, > score]).random_sample(n).map { |q| q.id }) > end > end > > Calling find with an array of ids will generate 1 "select * from > questions where id in (?)" intead of N "select * from questions where > id > = ?". > > jeremy > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
> Now, this will create three Image objects, right? So, if each image > is 500KB, that''s at least a 1.5MB database transfer (right?). I > really only need each image id.I store my images in the database too but have a File model which has a belongs_to :binary The binaries tables has the actual data, the File table has just the information about it ( format, mime, imagesize, filename etc ) This lets you use File as normal active record object and only recieve the blob when needed over the binary relation. -- Tobi http://www.snowdevil.ca - Snowboards that don''t suck http://www.hieraki.org - Open source book authoring http://blog.leetsoft.com - Technical weblog
On Wed, 16 Mar 2005 20:16:34 -0500, Tobias Luetke <tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Now, this will create three Image objects, right? So, if each image > > is 500KB, that''s at least a 1.5MB database transfer (right?). I > > really only need each image id. > > I store my images in the database too but have a File model which has > a belongs_to :binary > > The binaries tables has the actual data, the File table has just the > information about it ( format, mime, imagesize, filename etc ) > > This lets you use File as normal active record object and only recieve > the blob when needed over the binary relation.That''s a great idea...
On Thursday, March 17, 2005, 12:16:34 PM, Tobias wrote:>> Now, this will create three Image objects, right? So, if each image >> is 500KB, that''s at least a 1.5MB database transfer (right?). I >> really only need each image id.> I store my images in the database too but have a File model which has > a belongs_to :binary> The binaries tables has the actual data, the File table has just the > information about it ( format, mime, imagesize, filename etc )> This lets you use File as normal active record object and only recieve > the blob when needed over the binary relation.It sounds to me like File should "have_one" Binary, rather than "belong_to" Binary. Does it make any difference? Gavin
On Wednesday 16 March 2005 20:56, Gavin Sinclair wrote:> It sounds to me like File should "have_one" Binary, rather than > "belong_to" Binary. Does it make any difference?If you want to use Binary objects for other models that File, using belongs_to (or another join method) is required. A has_one ... :key_location => :self syntax might be useful, especially since belongs_to doesn''t provide methods such as #create_binary. -- Nicholas Seckar aka. Ulysses