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

[clem_c_rock]

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:

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!

[mattalexx]

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.

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:

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!

[clem_c_rock]

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:

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

}

[Fenrir2]

Please give more information about your tables.

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

[clem_c_rock]

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:

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:

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

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!

[Fenrir2]

Just use this 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.

[clem_c_rock]

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!