SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    NC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help w/ pulling data from 2 tables and displaying the resu

    Hello,

    I'm brand new to ruby and I'm having trouble pulling data from one
    table (table_1), looping through the results and pulling data from
    another table (table_2) and displaying the results of table_2 in one
    field.

    the pseudo code would be as follows:

    Code:
    result_1 = select first_name from table_1
    loop results_1 
    {
          puts "<tr><td> results_1.first_name<td>" 
    
          results_2 = select table_2.info from table_2 where table_1.user_id = table_2.user_id; 
            puts "<td>"
            loop results_2 
            { 
                 puts "<li>table_2.info 
            } 
            puts "</td>" 
    }

    The display would look like this for 1 row:

    joe | -info 1
    -info 2
    -info 3

    Help - I'm desparate!

  2. #2
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
       table_1.first_name,
       table_2.info
    FROM table_1
       JOIN table_2
          USING (user_id)
    Then populate an array or something with the results.

    Quote Originally Posted by clem_c_rock View Post
    Hello,

    I'm brand new to ruby and I'm having trouble pulling data from one
    table (table_1), looping through the results and pulling data from
    another table (table_2) and displaying the results of table_2 in one
    field.

    the pseudo code would be as follows:

    Code:
    result_1 = select first_name from table_1
    loop results_1 
    {
          puts "<tr><td> results_1.first_name<td>" 
    
          results_2 = select table_2.info from table_2 where table_1.user_id = table_2.user_id; 
            puts "<td>"
            loop results_2 
            { 
                 puts "<li>table_2.info 
            } 
            puts "</td>" 
    }

    The display would look like this for 1 row:

    joe | -info 1
    -info 2
    -info 3

    Help - I'm desparate!
    Matt Alexander
    Alexander Site Design

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    NC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello - I tried that call but it wasn't what I needed

    Really I need 2 arrays - 1 for the main table row and then 1 w/in a table cell to display the results of the second table.

    If I was to write this in PHP it would look like this:
    Code:
     $result_1 = mysql_query( "SELECT last_name, user_id FROM accounts LIMIT 0, 10", $conID );
    
    while( $row_1 = msql_fetch_array( $result_1, ASSOC ) )
    {
          echo "<tr>
                   <td>$row_1[last_name]</td>
                   <td>";
    
            $result_2= mysql_query( "SELECT title FROM spaces WHERE user_id=$row_1[user_id]", $conID );
          while( $row_2 = msql_fetch_array( $result_2, ASSOC ) )
          {
                 echo "<li>$row_2[title]</li>
          }
          echo "</td></tr>"
    
    }

  4. #4
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please give more information about your tables.

    Code:
    <&#37; for user in User.find :all, :limit => 10 %>
      <td><%= user.last_name %></td>
      <% for space in user.spaces %>
      <td><%= space.title %></td>
      <% end %>
    <% end %>
    No SQL necessary. You can use preloading if this isn't fast enough.

    We can give more specific code if you give us more information about your tables.

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    NC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for getting back to me on this. I've been reading about this
    aproach and I've tried to implemented it in my project.

    The 2 tables I have are accounts and spaces. The accounts table will
    have many spaces connecting the 2 tables by the foreign key user_id.

    so I tried my 2 model declarations as follow:

    Code:
    class Account < ActiveRecord::Base
        has_many :spaces
    end
    
    class Space < ActiveRecord::Base
      belongs_to :account
    end
    Then I try to call the 2 tables w/ this:

    Code:
    @accounts = Account.find(:all, :include => :spaces )
    Then I get this error:

    Unknown column 'spaces.account_id' in 'on clause':

    Somehow - it was getting the idea that account_id was the foreign key so
    I did this:

    Code:
    class Account < ActiveRecord::Base
         has_many :spaces, :class_name => 'Account', :foreign_key => 
    "user_id"
    end
    
    class Space < ActiveRecord::Base
        belongs_to :account, :class_name => 'Space', :foreign_key => 
    "user_id"
    end

    and now I get this error: undefined method `loaded'

    Most frustrating!

  6. #6
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just use this code:

    Code:
    class Account < ActiveRecord::Base
        has_many :spaces
    end
    
    class Space < ActiveRecord::Base
      belongs_to :account
    end
    and change your tables. Every table should have an id column. That means that the accounts table has an `id` column and not `account_id`.

    The spaces table should have an `id` column and `account_id` for the relationship.

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2004
    Location
    NC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem solved!

    Ok - I finally buckled down and asked a couple of questions and there
    was a method in the accounts model that was killing this whole concept:
    Problem solved!

    The project I'm building a little addition to is huge and extremely complex so there's a lot of code that could overide or clash w/ code I'm trying to develop.

    That was the case here. In the Account model there was this method that was overiding my join attempts.

    def spaces
    spaces = Space.find :all, :conditions => ['owner_id = ?',
    self.user_id]
    end

    Now, once I got rid of that method - this works perfectly!
    @accounts = Account.findall, :include => :spaces, :limit => 20)

    I got dumped right into the fire w/ this new project - it pays to ask a few simple questions. I tore a lot of hair out trying to figure it out, but also learned a great deal trying to find the answer.

    posts!

    Cheers!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •