If an active record is returned as a result of a find_by_sql() query,
what must be true in order for it to be save-able? Here''s an example
of
it not working:
====require ''rubygems''
require ''active_record''
ActiveRecord::Schema.define do
create_table(:blurgs, :force => true) {|t| t.string :name }
end
class Blurg < ActiveRecord::Base
end
a = Blurg.create(:name => "tic")
p "a.name = #{a.name}"
p "Blurg.count => #{Blurg.count}"
b = Blurg.find_by_sql(''SELECT \''tac\'' as
name'')
p "b = #{b}"
p "b.first.name = #{b.first.name}"
p "b.first.save! => #{b.first.save!}"
p "Blurg.count => #{Blurg.count}"
====which produces
===="a.name = tic"
"Blurg.count => 1"
"b = [#<Blurg name: \"tac\">]"
"b.first.name = tac"
"b.first.save! => true"
"Blurg.count => 1"
====So even though b.first has a .name value, calling b.first.save! silently
fails to store it in the database.
Is there a way to detect whether a record can be saved or not? Or at
least give an error when you try to save it?
- ff
--
Posted via http://www.ruby-forum.com/.
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
On 9 May 2010 10:02, Fearless Fool <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> If an active record is returned as a result of a find_by_sql() query, > what must be true in order for it to be save-able? Here''s an example of > it not working:The clue is in the question... You''re not selecting an "active record" (record), you''re selecting a string value - your query doesn''t return a record from a table, it just returns a string from SQL. AR has no idea what to update, so the SQL generated when you save will be along the lines of "Update blurgs set name = ''tac'' where id = ''''". That will return "true" as it doesn''t "fail" - there are just no records to update. Coming back to my first thought when I read your code: WTF? What are you *trying* to do?! Why are you hoping to achieve with that find_by_sql? Are you trying to find the first record you created? If so, you need to learn some SQL: b = Blurg.find_by_sql("SELECT * from blurgs where name = ''tic''") If that''s not what you''re trying to do, please explain. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On May 9, 10:02 am, Fearless Fool <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> If an active record is returned as a result of a find_by_sql() query, > what must be true in order for it to be save-able? Here''s an example of > it not working: > ====> require ''rubygems'' > require ''active_record'' > ActiveRecord::Schema.define do > create_table(:blurgs, :force => true) {|t| t.string :name } > end > class Blurg < ActiveRecord::Base > end > > a = Blurg.create(:name => "tic") > p "a.name = #{a.name}" > p "Blurg.count => #{Blurg.count}" > > b = Blurg.find_by_sql(''SELECT \''tac\'' as name'') > p "b = #{b}" > p "b.first.name = #{b.first.name}" > p "b.first.save! => #{b.first.save!}" > p "Blurg.count => #{Blurg.count}" > ====> which produces > ====> "a.name = tic" > "Blurg.count => 1" > "b = [#<Blurg name: \"tac\">]" > "b.first.name = tac" > "b.first.save! => true" > "Blurg.count => 1" > ====> So even though b.first has a .name value, calling b.first.save! silently > fails to store it in the database. > > Is there a way to detect whether a record can be saved or not? Or at > least give an error when you try to save it? >You didn''t select the primary key value so rails will have done update blugs set name = ''tac'' where id is null Whenever you use a customer select clause you have to be a little careful about what you select. Fred> - ff > -- > Posted viahttp://www.ruby-forum.com/. > > -- > You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. > For more options, visit this group athttp://groups.google.com/group/rubyonrails-talk?hl=en.-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Michael Pavling wrote:> Coming back to my first thought when I read your code: WTF? What are > you *trying* to do?! Why are you hoping to achieve with that > find_by_sql? Are you trying to find the first record you created? If > so, you need to learn some SQL: > > b = Blurg.find_by_sql("SELECT * from blurgs where name = ''tic''") > > If that''s not what you''re trying to do, please explain.Michael: The point of my question is that the save! silently fails, which surprises me. save! returns true yet doesn''t save the record: how is this not a bug? But since you asked (and since you''ve impugned my knowledge of SQL :), what I''m really trying to do is marshal a bunch of fields from other tables through a join. The real query is more like: class AnalysisDatum < ActiveRecord::Base ... snip ... def self.daily_metered_service(analysis_set_id, *metered_service_ids) return nil unless metered_service_ids service_ids = Utils.commify(metered_service_ids) query1 = <<QUERY1 ... snip ... QUERY1 query2 = <<QUERY2 SELECT td.id as start_time_id, DATE(td.datetime) AS date, bills.premise_id AS premise_id, #{analysis_set_id} AS analysis_set_id, bills.resource AS resource, SUM(bills.monthly_quantity/bills.billing_days) AS quantity, SUM(bills.monthly_cost/bills.billing_days) AS cost FROM time_dimensions AS td INNER JOIN (#{query1}) AS bills WHERE td.datetime BETWEEN bills.start_incl AND bills.end_incl GROUP BY date, premise_id, resource QUERY2 AnalysisDatum.find_by_sql(query2) end end Each of the fields named in the query2 SELECT are names of field in a "natural" AnalysisDatum record. I understand that the records returned by this query are not stored in the database, but I expected that this would work: records = AnalysisDatum.daily_metered_service(as_id, ms_ids) records.map {|r| r.save!} ... but as I said, this neither saves the records nor signals any error. If that''s not a bug, then it''s at least unexpected. (r.readonly? returns nil as well...) I''m certain that I can create a new, empty AnalysisDatum records and copy the fields from the records returned by the query, but I still want to be convinced this is not a bug. Thanks. -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Fearless Fool wrote:> Michael Pavling wrote: >> Coming back to my first thought when I read your code: WTF? What are >> you *trying* to do?! Why are you hoping to achieve with that >> find_by_sql? Are you trying to find the first record you created? If >> so, you need to learn some SQL: >> >> b = Blurg.find_by_sql("SELECT * from blurgs where name = ''tic''") >> >> If that''s not what you''re trying to do, please explain. > > Michael: > > The point of my question is that the save! silently fails, which > surprises me. > save! returns true yet doesn''t save the record: how is this not a bug?Your find_by_sql call doesn''t select a database record, so there is nothing to save. Look again at your query.> > But since you asked (and since you''ve impugned my knowledge of SQL :),Um, don''t get offended. Your query *is* improperly constructed, and that''s probably the root of your problem. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On 9 May 2010 16:06, Fearless Fool <lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Michael Pavling wrote: > The point of my question is that the save! silently fails, which > surprises me. > save! returns true yet doesn''t save the record: how is this not a bug?It doesn''t fail... the SQL works perfectly. Go into your favourite SQL query editor and execute: UPDATE table SET field = ''value'' WHERE id = '''' ... what does it say? "zero rows affected"? That''s a "success" according to the DB (okay - it may not be what you *want* but it''s what *is* going to happen).> But since you asked (and since you''ve impugned my knowledge of SQL :),Well... I hate to be mean, but I can only judge by the evidence I get given ;-)> what I''m really trying to do is marshal a bunch of fields from other > tables through a join. The real query is more like: > > <snip big fat queries> > > I understand that the records returned by this query are not stored in > the database, but I expected that this would work: > > records = AnalysisDatum.daily_metered_service(as_id, ms_ids) > records.map {|r| r.save!}Why do you *expect* that? The ''records'' array doesn''t really contain AnalysisDatum records, it contains AR:Base-derived class objects with a bunch of fields of your choosing (AR is just being nice to you and mapping those fields to attributes. You could just as easily do "User.find_by_sql(BIG_FAT_QUERY)" and run exactly the same query.... you wouldn''t *expect* to be able to save your calculations as User records would you?> ... but as I said, this neither saves the records nor signals any error. > If that''s not a bug, then it''s at least unexpected.It''s exactly what I''d expect - you''ve not requested a "record", you''ve requested an aggregation of fields, bypassing the AR model - why on Earth (and how?) would AR be able to take over again without you explaining to it what the fields you selected represent? AR doesn''t know about any error, because the fubar query that it generates *does* run and get reported as successful by the DB.> r.readonly? returns nil as well...Again, why would it not? Has anything you''ve done made a call to "r.readonly!" ?> I''m certain that I can create a new, empty AnalysisDatum records and > copy the fields from the records returned by the query,If you want a *new* AnalysisDatum record saved, you''re gonna have to do something to populate a new record. You might be able to use the Marshal.load(Marshal.dump(foo)) method, or by cloning, but it may just come down to a new AnalysisDatum.create(:foo => :bar)> but I still want to be convinced this is not a bug.Convinced yet? :-) -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Michael Pavling wrote:>> But since you asked (and since you''ve impugned my knowledge of SQL :),> Um, don''t get offended...No offense taken - that''s what the smiley face was for. The first case was designed as a simple case to demonstrate that there''s something fishy about how SELECT columns are mapped back to AR records -- whether or not it actually touches the database is (I believe) a red herring.> It''s exactly what I''d expect - you''ve not requested a "record", you''ve > requested an aggregation of fields, bypassing the AR model - why on > Earth (and how?) would AR be able to take over again without you > explaining to it what the fields you selected represent? AR doesn''t > know about any error, because the fubar query that it generates *does* > run and get reported as successful by the DB.You ask why I think this might work. Let me put it this way: I hand you an object. You call record.class and see that it''s an AnalysisDatum object. You call record[:cost] and see that there''s a value stored there. You call record.save! and see that it returns true. Based on the above, what would lead you to believe that the record is NOT stored? And (most importantly) what other queries could you make (to the object, not to me!) that would predict this mis-behavior?> Convinced yet? > :-)Nope! :) :) -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
By the way, to avert any confusion, I should strike the word "query" from my last question and ask it this was: And (most importantly) what other methods could you call on the object that would predict this mis-behavior? - ff -- Posted via http://www.ruby-forum.com/. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On May 9, 7:41 pm, Fearless Fool <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Michael Pavling wrote: > >> But since you asked (and since you''ve impugned my knowledge of SQL :), > > Um, don''t get offended... > > No offense taken - that''s what the smiley face was for. The first case > was designed as a simple case to demonstrate that there''s something > fishy about how SELECT columns are mapped back to AR records -- whether > or not it actually touches the database is (I believe) a red herring. >There isn''t really - there''s just a hash of attributes and methods that read/write values in that hash. But active record can''t save rows without a primary key and you''re not giving it one.> and see that it returns true. Based on the above, what would lead you > to believe that the record is NOT stored? And (most importantly) what > other queries could you make (to the object, not to me!) that would > predict this mis-behavior?record.id being nil despite new_record? returning false would be a good clue. Ruby being ruby it''s easy enough to bend things into looking sane when they are actually quite hairy: Active Record assumes that the result of a find is a row that exists, whereas in your case it isn''t Fred -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.