saving a "found_by_sql" AR

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 :wink:

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?
:slight_smile:

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?
:slight_smile:

Nope! :slight_smile: :slight_smile:

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