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?
over his set?
No, the database is not queried every time. The reason I was suggesting
to use an array is that it that that way you don’t need to handle that
specially. It works like any other ruby array, unlike the Mysql::Result,
which you need to reset before iterating again. So if you’re about to
iterate over the result multiple times, possibly in different places
it’s save to either
a) make your custom iterator, rewinding the result set afterwards
OR
b) use a standard ruby type, such as Array
a) might look like this (ugly monkey patch):
class Mysql::Result
def my_each_hash(&block)
each_hash(&block) ; data_seek(0)
end
end
Be warned though: this might give you trouble in a threaded environment.
One way of solving this would be to keep the results you want to re-use
within an array:
Hope this helps.
Niklas,
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.
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:
On Mon, 2010-11-15 at 18:05 +0900, Andy T. wrote:
over his set?
No, the database is not queried every time. The reason I was suggesting
to use an array is that it that that way you don’t need to handle that
specially. It works like any other ruby array, unlike the Mysql::Result,
which you need to reset before iterating again. So if you’re about to
iterate over the result multiple times, possibly in different places
it’s save to either
a) make your custom iterator, rewinding the result set afterwards
OR
b) use a standard ruby type, such as Array
a) might look like this (ugly monkey patch):
class Mysql::Result
def my_each_hash(&block)
each_hash(&block) ; data_seek(0)
end
end
Be warned though: this might give you trouble in a threaded environment.
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!
