# Saving a "found\_by\_sql" AR

**URL:** <https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284>\
**Category:** Rails\
**Created:** [May 9, 2010, 11:02am UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284 "2010-05-09T11:02:47Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [May 9, 2010, 11:02am UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284/1 "2010-05-09T11:02:47Z")

</div>

# 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

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [May 9, 2010, 11:23am UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284/2 "2010-05-09T11:23:41Z")

</div>

On 9 May 2010 10:02, Fearless F. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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.

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [May 9, 2010, 5:06pm UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284/3 "2010-05-09T17:06:33Z")

</div>

Michael P. 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.

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [May 9, 2010, 5:16pm UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284/4 "2010-05-09T17:16:22Z")

</div>

Fearless F. wrote:

> Michael P. 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](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [May 9, 2010, 12:14pm UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284/5 "2010-05-09T12:14:37Z")

</div>

On May 9, 10:02 am, Fearless F. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> # end
> 
> 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

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [May 9, 2010, 5:38pm UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284/6 "2010-05-09T17:38:18Z")

</div>

On 9 May 2010 16:06, Fearless F. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Michael P. 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:
> 
> 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?  
🙂

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [May 9, 2010, 8:41pm UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284/7 "2010-05-09T20:41:24Z")

</div>

Michael P. 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! 🙂 🙂

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [May 10, 2010, 12:21am UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284/8 "2010-05-10T00:21:33Z")

</div>

On May 9, 7:41 pm, Fearless F. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Michael P. 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

---

<div class="post-metadata">

**Author:** ![fearless\_fool](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/5e9695/32.png) [@fearless\_fool](https://www.ruby-forum.com/u/fearless_fool)\
**Post date:** [May 9, 2010, 9:03pm UTC](https://www.ruby-forum.com/t/saving-a-found-by-sql-ar/188284/9 "2010-05-09T21:03:33Z")

</div>

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