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?
One way of solving this would be to keep the results you want to re-use
within an array:
require "mysql"
db = Mysql.connect('localhost', 'root', '', 'test')
items = db.query('SELECT * FROM items')
tag_result = db.query('SELECT * FROM tags')
tags =
tag_result.each_hash { |t| tags << t }
items.each_hash do |item|
puts item["title"]
tags.each do |tag|
puts tag["name"]
end
end
puts tag_result.num_rows
puts items.num_rows
Hope this helps.
···
On Mon, 2010-11-15 at 03:36 +0900, Andy Tolle wrote:
botp wrote in post #961345:
> On Sun, Nov 14, 2010 at 5:55 PM, Andy Tolle <durexlw.register@gmail.com> > > wrote:
>> 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?
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!
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.
> 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?
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.
···
On Mon, 2010-11-15 at 18:05 +0900, Andy Tolle wrote:
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 Tolle 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.
On Mon, 2010-11-15 at 18:30 +0900, Andy Tolle wrote:
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 Tolle 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.
