Mysql::Result .each_hash - unexpected result

A

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
 
B

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
 
A

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?
 
B

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
 
A

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
 
A

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.
 
A

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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top