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.
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
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.
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,
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?
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!
By the way, to avert any confusion, I should strike the word "query"
And (most importantly) what other methods could you call on the object that would predict this mis-behavior?
- ff
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
