Rails - May 2005 - has_and_belongs_to_many question

mysql> select id,name from billingproviders;
+----+----------------+
| id | name           |
+----+----------------+
|  1 | CCBill         |
|  2 | otherbill      |
|  3 | nopatiencebill |
+----+----------------+

mysql> select id,username from webmasters where id=''1'';
+----+----------+
| id | username |
+----+----------+
|  1 | search   |
+----+----------+

mysql> select * from billingproviders_webmasters;
+--------------+--------------------+--------+
| webmaster_id | billingprovider_id | wmcode |
+--------------+--------------------+--------+
|            1 |                  1 | 1234   |
|            1 |                  2 | 5678   |
+--------------+--------------------+--------+

class Webmaster < ActiveRecord::Base
  has_and_belongs_to_many :billingproviders
end

class Billingprovider < ActiveRecord::Base
  has_and_belongs_to_many :webmasters
end


i would like to get the result:

CCBill -> 1234
otherbill -> 5678
nopatiencebill ->

i tried:

webmaster = Webmaster.find(1)
@billingproviders_from_webmaster = webmaster.billingproviders

i only get the result:

CCBill -> 1234
otherbill -> 5678

then i tried:

@billingproviders_from_webmaster = Billingprovider.find(:all, :include =>
:webmasters)

but the billingproviders_webmasters data is not included. only the
webmasters data.

can somebody help me if there is a way to do this or if my concept of
tables is wrong in eyes of ruby?

do i need an additional model for billingproviders_webmasters?

i bought the book, i searched on the net but there doesn''t seem to be
any
documentation on how to handle this.

Thanks
Chris

Yes i try to create following sql query in ruby on rails:

mysql> select a.name,b.wmcode from billingproviders as a left join
billingproviders_webmasters as b on a.id=b.billingprovider_id;
+----------------+--------+
| name           | wmcode |
+----------------+--------+
| CCBill         | 1234   |
| otherbill      | 5678   |
| nopatiencebill | NULL   |
+----------------+--------+

But nobody can give me a real solution on how i can extract this
information from the objects. It''s even not covered by the book. I
tried
what Corey wrote but didn''t have any success up to know.

I''m really lost with this issue. Is find_by_sql really my last
solution?

I appriciate any help.

Thanks
Chris

> In SQL, what you look like you want is a left-outer join...
>
> select bp.name, wm.username
> from billingproviders as bp left outer join webmasters as wm on bp.id >
wm.id;
>
> I think there is some magic method you can invoke from the
> BillingProvider''s controller to get all of the bp.names, and any
> associated username... Then, you get @billingprovider.name and
> @billingprovider.webmaster.username or something like that...
>
>
> On 5/31/05, Chris Armstrong
<carmstrong-af0KbPB/cpmsTnJN9+BGXg@public.gmane.org> wrote:
>> mysql> select id,name from billingproviders;
>> +----+----------------+
>> | id | name           |
>> +----+----------------+
>> |  1 | CCBill         |
>> |  2 | otherbill      |
>> |  3 | nopatiencebill |
>> +----+----------------+
>>
>> mysql> select id,username from webmasters where
id=''1'';
>> +----+----------+
>> | id | username |
>> +----+----------+
>> |  1 | search   |
>> +----+----------+
>>
>> mysql> select * from billingproviders_webmasters;
>> +--------------+--------------------+--------+
>> | webmaster_id | billingprovider_id | wmcode |
>> +--------------+--------------------+--------+
>> |            1 |                  1 | 1234   |
>> |            1 |                  2 | 5678   |
>> +--------------+--------------------+--------+
>>
>> class Webmaster < ActiveRecord::Base
>>   has_and_belongs_to_many :billingproviders
>> end
>>
>> class Billingprovider < ActiveRecord::Base
>>   has_and_belongs_to_many :webmasters
>> end
>>
>>
>> i would like to get the result:
>>
>> CCBill -> 1234
>> otherbill -> 5678
>> nopatiencebill ->
>>
>> i tried:
>>
>> webmaster = Webmaster.find(1)
>> @billingproviders_from_webmaster = webmaster.billingproviders
>>
>> i only get the result:
>>
>> CCBill -> 1234
>> otherbill -> 5678
>>
>> then i tried:
>>
>> @billingproviders_from_webmaster = Billingprovider.find(:all, :include
>> =>
>> :webmasters)
>>
>> but the billingproviders_webmasters data is not included. only the
>> webmasters data.
>>
>> can somebody help me if there is a way to do this or if my concept of
>> tables is wrong in eyes of ruby?
>>
>> do i need an additional model for billingproviders_webmasters?
>>
>> i bought the book, i searched on the net but there doesn''t
seem to be
>> any
>> documentation on how to handle this.
>>
>> Thanks
>> Chris
>>
>> _______________________________________________
>> Rails mailing list
>> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>