search for: find_by_sql

Hello-- There must be a better way to do this. I have a class method in my model that finds averages and does a few calculations using find_by_sql. The problem I¹m encountering is that all computed columns from MySQL come back as type string. E.g., def self.find_averages(domain_id) if @@domain_average return @@domain_average else @@domain_average = MyDomain.find_by_sql(["select avg(latency) as latency, avg(datedi...

Hopefully an easy one, how do I merge two or more SQL query results? Example: result1 = find_by_sql(x) result2 = find_by_sql(y) What is the best way to merge result1 and result2? I want to be able to reference the objects as if they were obtained via one query. Cheers, Dan

I''m trying to get the following statement to work using variable substitution (it works if hard-code the where (med_type) param in the inner SQL statement): med_type = ''game'' @mediaformats = MediaFormat.find_by_sql("select * from media_formats where media_formats.media_type_id = (select media_type_id from media_types where media_type = ?, :med_type) order by description") The error is generated by MySQL because the query it receives is: select * from media_formats where media_formats.media_type_id...

I tried to use named parameters in my SQL query , using find_by_sql Order.find_by_sql ([select * from orders where amount > ? and quantity > ?", [ @amount, @quantity ] works;.. but Order.find_by_sql ([select * from orders where amount > :amount and quantity > :quantity ", [ :amount => @amt, :quantity => @qty ] is not working a I wron...

...39;'m trying to execute something like this method in a model: def self.sql_for_rankings() sql = "SET @counter:=0;" sql << " SET @counter:=0; SELECT *, @counter:=@counter+1 AS rank FROM testscores. " find_by_sql(sql) end Where the new rank column is a kind of autoincrementer inside of this query. Ignore for the moment that I can add this counter in ruby rather than in sql - but I am trying to do it in mysql. This executes in mysql with no problem and returns something like: |testscore; name, rank| |8...

Why would I get an undefined method `find_by_sql'' for #Example: 0x981a4e4> for error when trying to use the find_by_sql method in my model? def init_dictionaries tables = find_by_sql( "SHOW TABLES FROM thesaurus" ) @@tables = tables.collect{ |t| t.Tables_in_thesaurus } end -- You received this message because...

I can''t see how to use variables so I am using console to test things out... clients table - a column named first_name My very brief console session... >> clients = Client.find_by_sql("select * from clients where first_name = FN") ActiveRecord::StatementInvalid: RuntimeError: ERROR C42703 Mcolumn "fn" does not exist Fparse_expr.c L1034 RtransformColumnRef: select * from clients where first_name = FN from /usr/lib/ruby/gems/1.8/gems/activerecord...

I have a bunch of queries to compute some counters, like find_by_sql(<<-SQL SELECT p.*, c.nfavourites FROM people p, ( SELECT fp.person_id, COUNT(fp.user_id) as nfavourites FROM favourite_people fp, users u WHERE fp.user_id = u.id AND u.cluster = ''#{in_cluster_of_user.cluster}'' GROUP BY fp.person...

Hi, I''d like to do a "find_by_sql" without a model (e.g. <model name>.find_by_sql("...")) as the results I get back are a once off special, and I''m happy to handle them as an array. How do I do this? "ActiveRecord::Base.find_by_sql(...)" does not seem to work? Background - At the moment I&...

...rts of methods only to discover that Rails (after reading Agile Web Dev) already had something much simpler (I don''t mind since it''s helping me learn Ruby along the way). My question is this, does Rails have any methods that can do this .. def self.total_points @points = self.find_by_sql("select sum(score) from scores") points = @points[0] #assigns first row points = points.attributes #assigns as hash points = points.values # pulls out the value points = points.to_s #converts to string points = points.to_i # converts to fixnum end Or am I wanting to be too concise....

...a large number of records from the database and I cant afford to keep it in the memory due to performance issues. Therefore using the following would be ideal, User.find_each(:batch_size => 5000, :start => 2000) do |user| NewsLetter.weekly_deliver(user) end however, I would like to use find_by_sql since my query is user defined. Question is, is it possible to use find_by_sql with batch_size and iterate through all the record-sets? Thanks Bhavesh -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Tal...

Hello all. I am trying to do the equivalent of: @componentlogs = Componentlog.find(:all, :conditions => [ "cl_compname like ?", @criteria ], :offset => offset, :limit => items_per_page, :order => "cl_spr DESC" ) in a find_by_sql statement. I cannot use the build in because the adaptor isn''t quite right (OCI8) When I use it I get the following error OCIError: ORA-00907: missing right parenthesis: select * from (select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM componentlog WHERE (cl_compname like ''...

I abandoned ruby way for find_by_sql and still can''t get this... @myplacement = Placement.find_by_sql( "select * from placement where :intake_date >= beg_intake_date and :intake_date <= end_intake_date") just a simple date range...it''s killing me - If I knew how to load placement controller into...

Hi All, Is there a way to do join queries with find() or is it best to use find_by_sql() instead? I''m looking to do something like: SELECT winery_name, wine_name FROM winery, wine WHERE wine.winery_id = winery.winery_id AND winery.winery_name LIKE ''Borg%''; Thanks! : ) Jason

I was trying to do SUM based mySQL query simliar to the following: SELECT SUM(updated_on - created_on) AS total from signups If I were to run this command in the mySQL console I would get a result. However, if I were to run it using the following command in Rails: Signup.find_by_sql("SELECT SUM(updated_on - created_on) AS total from signups") The query that is written to the log is: SELECT SUM(updated_on - created_on) AS total from signups Like I said before, if I take that query to the mySQL console, I get the expected result, however the result to the view is si...

hi I amd tanizawa. I had problem. I can not get main.name value from below sql. "find_by_sql ''select main.id,main.name,sub.name from main left join sub on sub.id = main.id''" Please teach me how to get borth name value. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To view this discussion on the...

Is a find_by_sql query cached with memcached and cached_model (http://dev.robotcoop.com/Libraries/)? Looks like the answer is no. Is there a reason for this? I have a lot of very complicated querys with a lot of unions, subquerys and joins that would greatly benefit from the cache. Going through the code I can see...

...ers where name = "martin"; +----+-------+------------+ | id | count | company | +----+-------+------------+ | 1 | 0 | | +----+-------+------------+ def count(number) puts "count() reached!\n user is #{@username} " # this works userdata = User.find_by_sql("select id, count, company from users where name = ''@username'' ") account = userdata[0] puts "account-id: #{@account.id}" # empty puts "the name of the company is: #{@account.company}, count # {@account.count}\n" # empty end How do I...

I am a little stuck with getting an sql result into my page. I want to run a select distinct that returns all of the different sections available and displays this as a form select. In my controller I have: def edit @resource = Resource.find(params[:id]) @sections = Resource.find_by_sql "SELECT DISTINCT resources.section FROM resources" end And then in my view I have: <% for option in @sections %> <option value="<%= section.section %>" <%= '' selected'' if option.section == @resource.section %>> <%= o...

When you use find, you can include related objects with include so that both objects get instantiated in the results, e.g. Foo.find(:all, :include => :bar). But when you use find_by_sql, is there a way to do this? You could definitely write the find_by_sql SQL to join Foo and Bar tables: Foo.find_by_sql("SELECT foo.*, bar.* from foo, bar where foo.bar_id = bar.id") But is there a way to instantiate not just the Foo object but also the Bar object in the results that co...