Mysql::Result .each_hash - unexpected result

Discussion in 'Ruby' started by Andy Tolle, Nov 14, 2010.

  1. Andy Tolle

    Andy Tolle Guest

    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

    --
    Posted via http://www.ruby-forum.com/.
     
    Andy Tolle, Nov 14, 2010
    #1
    1. Advertising

  2. Andy Tolle

    botp Guest

    On Sun, Nov 14, 2010 at 5:55 PM, Andy Tolle <> wr=
    ote:
    > 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
     
    botp, Nov 14, 2010
    #2
    1. Advertising

  3. Andy Tolle

    Andy Tolle Guest

    botp wrote in post #961345:
    > On Sun, Nov 14, 2010 at 5:55 PM, Andy Tolle <>
    > 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?

    --
    Posted via http://www.ruby-forum.com/.
     
    Andy Tolle, Nov 14, 2010
    #3
  4. Andy Tolle

    botp Guest

    On Mon, Nov 15, 2010 at 2:36 AM, Andy Tolle <> wrote:
    > 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
     
    botp, Nov 15, 2010
    #4
  5. Andy Tolle

    Andy Tolle Guest

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

    --
    Posted via http://www.ruby-forum.com/.
     
    Andy Tolle, Nov 15, 2010
    #5
  6. Andy Tolle

    Andy Tolle Guest

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

    --
    Posted via http://www.ruby-forum.com/.
     
    Andy Tolle, Nov 15, 2010
    #6
  7. Andy Tolle

    Andy Tolle Guest

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


    --
    Posted via http://www.ruby-forum.com/.
     
    Andy Tolle, Nov 15, 2010
    #7
  8. Andy Tolle

    Andy Tolle Guest

    Andy Tolle, Nov 15, 2010
    #8
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike Pemberton

    unexpected result using std::list

    Mike Pemberton, Oct 14, 2003, in forum: C++
    Replies:
    3
    Views:
    344
    Dan Cernat
    Oct 14, 2003
  2. John J

    Unexpected Result

    John J, May 12, 2004, in forum: C++
    Replies:
    8
    Views:
    384
    John J
    May 13, 2004
  3. japh
    Replies:
    4
    Views:
    740
  4. eagle eyes joe

    MySql::Result#each_hash deletes data

    eagle eyes joe, Sep 26, 2004, in forum: Ruby
    Replies:
    2
    Views:
    138
    eagle eyes joe
    Sep 28, 2004
  5. Michael Tan
    Replies:
    32
    Views:
    998
    Ara.T.Howard
    Jul 21, 2005
Loading...

Share This Page