Rails transforming Query from Rails 3.2.12 to 4.0.0 Errors

Hi I've got some problems convertigs Querys from My 3.2.12 Rails app to the new converted 4.2.0 version.

I got this:

@data[:weekday] ||= Xyz.count(:include => [:membership], :conditions => ["xyz.done_at IS NOT NULL AND xyz.course_id = ?", @course_id], :group => "WEEKDAY(xyz.done_at)").collect {| val | [val[0].to_i, val[1]]}

I've made this new one, is this the same?

@data[:weekday] = Xyz.where("done_at IS NOT NULL AND memberships.course_id = ?", @course_id).joins(:membership).group("WEEKDAY(done_at)").count()

And the I got this one:

@data[:xyz_per_hours] ||= Xyz.count(:include => [:membership], :conditions => ["xyz.done_at IS NOT NULL AND memberships.course_id = ?", @course_id], :group => "TIMESTAMPDIFF(HOUR, xyz.created_at, xyz.done_at)").to_a.select do | val | val[0].to_i < 120 end

I've converted it to:

@data[:xyz_per_hours] = Xyz.where("done_at IS NOT NULL AND course_id = ?", @course_id).joins(:membership).group("TIMESTAMPDIFF(HOUR, created_at, done_at)").count().to_a.select do | val | val[0].to_i < 120 end

But I got this error:

Mysql2::Error: Column 'created_at' in field list is ambiguous: SELECT COUNT(*) AS count_all, TIMESTAMPDIFF(HOUR, created_at, done_at) AS timestampdiff_hour_created_at_done_at FROM `xyz` INNER JOIN `memberships` ON `memberships`.`id` = `xyz`.`membership_id` WHERE (done_at IS NOT NULL AND course_id = 44) GROUP BY TIMESTAMPDIFF(HOUR, created_at, done_at)

Whats wrong?

-------------Problems Reply------------

In group clause

"TIMESTAMPDIFF(HOUR, created_at, done_at)"

you need to specify a table for column created_at like it was in the original expression - xyz.created_at

And your first question

I've made this new one, is this the same?

There is only one difference between both expressions - in the first one you use :include => [:membership] what means LEFT OUTER JOINs operation (documentation) but in the second one you use joins(:membership) which means INNER JOIN operation

That last issue is occurring in your #group statement. Because youre joining on membership, and both membership and xyz have created_at attributes, the query doesnt know which created_at to order by.

Category:ruby on rails Views:0 Time:2018-05-02

Related post

  • Rails 3: Querying from associated tables 2011-05-31

    Im very new to Ruby on Rails 3 and ActiveRecord and seem to have been thrown in at the deep end at work. Im struggling to get to grips with querying data from multiple tables using joins. A lot of the examples Ive seen either seem to be based on much

  • Rails model query from sql 2012-03-03

    I am working on rails 3, I am writing a logic in rails to fetch the tags related to a tag based on a taggable type. How to write the same in rails way from the below sql? select count(t2.id), t2.id, t2.tag_id from taggings t1 inner join taggings t2 o

  • Execute SQL query from Rails when connecting to SQL Server 2005 through activerecord-sqlserver-adapter 2010-06-30

    So I'm connecting to SQL Server 2005 from my Rails app via the activerecord-sqlserver-adapter. I'm able to execute stored procs by doing Mymodel.execute_procedure("thisProcedure", param1, param2) But I have a stored proc that has a SQL INOUT variable

  • How can one terminate a rails runner process from rails? 2014-08-27

    I need to run a rails runner process then terminate it later when some action happens. What I did was: pid = fork { exec "rails runner \"file.rb\"" } and saved that pid somewhere then when I wanted to terminate, I did: system("kill #{pid}") The probl

  • Rails routes syntax - from rails 2 to rails 3 2010-12-09

    I'm following a tutorial (the learning rails podcast) and need to change the following route syntax so that it's compatible with Rails 3.0. can anyone help? map.view_page ':name', :controller => 'viewer', :action => 'show' thanks in advance. --

  • To Rails 3 Routing from Rails 2 2011-04-26

    I am following this tutorial on sending activation email: http://www.slideshare.net/JamesEdwardGrayII/sending-email-with-rails I am fairly certain this was done with rails 2 and the routing has been a little problematic for me. routes.rb: map.activat

  • Error in routes after switching to rails 3.1 from rails 3.0.9 2011-10-05

    I have a model "Company" that has :id, :name and sometimes :domain, and there're such routes: if a company has :domain then it's /companies/:domain, otherwise it's /companies/:id There're such routes in my routes.rb file: get "/companies/:id(.:format

  • What to Learn: Rails 1.2.4 -> Rails 3 2010-04-15

    I've recently convinced my management that our outdated version of Rails (1.2.4) is slowing us down enough to warrant an upgrade. The approach we're taking is to start a fresh project with current technology rather than a painful upgrade. Our require

  • Rails - Trying to query from a date range-everything from today 2010-04-26

    This question already has an answer here: Rails ActiveRecord date between 8 answers I'm trying to figure the best way to query a date range from rails...I looked around on Google but am unsure about how to use this syntax. I have a Model that has var

  • How to execute complex Arel query in Rails 3 from Model 2010-08-08

    I'm on Rails 3,and I have a SQL query composed of a few joins that I've built up in Arel. I want to run this query from a method in one of my models, but I'm not sure of how to do this. The arel object turns out to be of type Arel::InnerJoin, and I w

  • Query is fast in MySQL but slow when run from Rails ActiveRecord 2011-02-15

    I have a query that runs very fast in the MySQL console but very slow when I run it using Rails Active Record. This is the query that run against a table of 7 million records: select broker_id,count(abserror),avg(abserror) from fc_estimates where ( f

  • rails SQL query reported failing with postgres but succeeds from psql command-line tool 2011-11-03

    I am trying to use a Rails 2.3.5 app with Postgres9.1 on Ubuntu (deployed on Apache2 with Phusion Passenger). The app throws this error when I try to access it, complaining that table does not exist: PGError: ERROR: relation "users" does not exist LI

  • How Do I display information from the joins table with the new Rails 3 query interface 2012-01-18

    I have two models Horse and Race. class Horse < ActiveRecord::Base has_many :races end class Race < ActiveRecord::Base belongs_to :horse end Now I want to use the new rails 3 query interface to see the Horse name and all its races. Horse.joins(

  • Rails - explore data from a query 2012-04-12

    Simple question. Is there a way to see all your data that your receive from a query in Rails? Example: @persons = Person.find(:all) Is there a way to see all data from @person? --------------Solutions------------- require 'pp' pp @persons = Person.fi

  • Find postgresql query PID from Rails (Track a query's ID) 2013-08-05

    In Rails, I'm starting a regular query such as: MyTable.where("user_id = ?",5) [The acutual query I'm running takes a long time so I will have time to perform the next steps.] Then, in Postgres, I run the command: SELECT * FROM pg_stat_activity; This

  • How to query from a Rails variable? 2014-05-09

    I'm using Rails 3.2 then I have a query that uses find_by_sql: @posts ||= Post.all_post.paginate(:page => params[:page], :per_page => 5) How do I get all the post id from @posts using Rails? I have to get all the ids using activerecord not find

  • Ruby On Rails Querying From a model but returning another tables fields 2011-03-17

    active_courses_past_week = CourseEnrollment.select("courses.*"). joins(:course). where("date(course_enrollments.created_at) BETWEEN ? and ?", Date.parse(start_date.strftime('%Y-%m-%d')), Date.parse(end_date.strftime('%Y-%m-%d'))). group("courses.id")

  • Filter cached DB queries from Rails' logs? 2010-01-20

    Is it possible to filter out DB queries that hit Rails' query cache from Rails logs? The presence of these "queries" makes it harder to debug performance issues --------------Solutions------------- I was going to ask this question a few weeks ago, bu

  • InvalidAuthenticityToken from rails for POST request from openlaszlo app 2010-09-12

    InvalidAuthenticityToken from rails for POST request Hi All I have a rails server running to which I make a POST request. The dataset is defined as Now per rails documentation in order to make a POST a request I need to set the add "authenticity_toke

  • Is it possible to run a find_by_sql query in Rails / ActiveRecord without loading all results into memory? 2010-10-25

    I'm building a Rails plugin that extracts a lot of data from Rails apps. It builds queries dynamically that span multiple tables, and there will be a lot of results returned. Here's an example of one of the queries built for pulling purchase data out

  • Help constructing a complex DB query in rails 2011-06-07

    I’m hoping that some of you sql gurus can help me construct a relatively complex (for me anyway) query in rails. I have two relevant models, a Story model and a Post model. class Story < ActiveRecord::Base attr_accessible :title belongs_to :user h

  • How to Convert SQL Query to Rails Active Record Query? 2011-08-10

    My last post was on the best way to write a SQL query with conditions on a LEFT OUTER JOIN: LEFT OUTER JOIN with conditions (where, order by)? Now, I need to convert that good piece of SQL into a sweet Active Record Query (Rails 3). ;-) I have 2 Mode

  • Rails ActiveRecord query 2011-09-10

    My question is twofold... Primarily, I am trying to figure out how to ask > or < when filtering this query. You can see at the end I have .where(:created_at > 2.months.ago) and that is improper syntax, but I'm not sure the correct way to cal

  • translate SQL query into Rails 2011-10-14

    after searching for a while I found the SQL query that gives me the result I am looking for. select * from (select * from services WHERE user_id=1 ORDER BY created_at DESC) AS x GROUP BY servicename so a user has many services with different servicen

  • Rails model generator from existing database 2011-11-23

    I have configured two Servers: serverA:192.168.11.16 => running MySQLserver that contains many database with many tables. one of the databases is info one of the tables in this database is table1 that contains info about users (name, no, id, addrs

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.224 (s). 11 q(s)