Mysql::Result .each_hash - unexpected result

[Andy Tolle]

Consider the following code:

require "mysql"
db = Mysql.connect('localhost', 'root', '', 'test')

items = db.query('SELECT * FROM items')
tags = db.query('SELECT * FROM tags')

items.each_hash do |item|
puts item["title"]
tags.each_hash do |tag|
puts tag["name"]
end
end

puts tags.num_rows
puts items.num_rows

----
If the table "items" would contain:
id | title
1 | item01
2 | item02
3 | item03

and the table "tags" would contain:
id | name
1 | tag01
2 | tag02

The I would expect the output to be:
----expected output---
item01
tag01
tag02
item02
tag01
tag02
item03
tag01
tag02
2
3

However the output is as follows:
----actual output---
item01
tag01
tag02
item02
item03
2
3

--
--
Note how in the actual output the inner itterator isn't executed... and
yet as you can see from the "2, 3" in the end (which is from "puts
tags.num_rows" and "puts items.num_rows") the variable "tags" does
contain multiple elements.

Can anyone explain my why this output is behaving as it does? Could you
suggest a sensible way to get to the expected output?

Thanks in advance,
Andy

 

[botp]

items.each_hash do |item|
=A0 puts item["title"]
=A0 tags.each_hash do |tag|

at this point, i'd be wary. the iterator may not "rewind"

many ways: data_seek or fetch_row and save the selct results on
separate arrays .. or use activerecord..

kind regards -botp

 

[Andy Tolle]

botp wrote in post #961345:

items.each_hash do |item|
puts item["title"]
tags.each_hash do |tag|

at this point, i'd be wary. the iterator may not "rewind"

Can you say something about how an iterator can not "rewind"? Do you
mean I can't do nested iterators in ruby?

 

[botp]

botp wrote in post #961345:
Can you say something about how an iterator can not "rewind"? Do you
mean I can't do nested iterators in ruby?

nothing to do w ruby. just understanding file/db handling...

eg, try,

items.each{|item| p item}

["1", "item01"]
["2", "item02"]
["3", "item03"]
#=> #<Mysql::Result:0x8d11930>

ok, let's try it again

items.each{|item|p item}

#=> #<Mysql::Result:0x8d11930>

see. it outputs nothing. the record pointer does not rewind.
so let us rewind the pointer,

items.data_seek 0

#=> #<Mysql::Result:0x8d11930>

and run it again

items.each{|item|p item}

["1", "item01"]
["2", "item02"]
["3", "item03"]
#=> #<Mysql::Result:0x8d11930>

btw, you can also try Sequel rubygem for very easy installing &
handling of sql (and you wont encounter problem above).

hth.
kind regards -botp

 

[Andy Tolle]

items.data_seek 0

#=> #<Mysql::Result:0x8d11930>

and run it again

items.each{|item|p item}

["1", "item01"]
["2", "item02"]
["3", "item03"]
#=> #<Mysql::Result:0x8d11930>

btw, you can also try Sequel rubygem for very easy installing &
handling of sql (and you wont encounter problem above).

hth.
kind regards -botp

-botp,

I see now... db handling is like filehandling: if I fetch a row, I need
to remember where I am in that result set in order to be able to fetch
the next in line.
So it's like resetting a file pointer before reading the file again,
only this time it's a recordset... makes perfect sense now.

I appreciate the help and the extra explanation: it allows me to have
insight in stead of just a solution. Many thanks!

Andy

 

[Andy Tolle]

One way of solving this would be to keep the results you want to re-use

within an array:

Hope this helps.

Niklas,

I'm not too fond using multiple variables to store one thing unless
there is a good reson for it... so I'm wondering: if I question the
Mysql::Result-set, does it then query the database each time I iterate
over his set?

Asked differently: you happen to know if storing the resultset in an
array in stead of questioning the resultset over and over, does this
have a positive influence on serverloads?

Is there any way I can find out myself when exactly the database is
querried? Seems kinda crucial to optimizing.

 

[Andy Tolle]

Niklas,

Thanks for opening my eyes man. Saving it to an array makes the code
dryer in less lines and makes it more proof to threaded environments.
Seems well worth the extra line of code if you ask me.

Thanks for the help.

Andy


Niklas Cathor wrote in post #961493:

 

[Andy Tolle]

Don't be too sure too soon http://coderrr.wordpress.com/2009/01/08/activerecord-threading-issues-and-resolutions/

hehe... gotta love coding

Anyway, seems specific to AR and MySQL. MySQL alone should be fine or am
I misinterpreting this article?