SQL joins question


#1

I want to link up 3 tables to list all members of a particular group. My
code (which is wrong) looks like this:

@group_members = Membership.find( :all,
:joins => [‘FROM memberships INNER
JOIN groups ON memberships.group_id= group.id’ + ‘INNER JOIN users ON
memberships.user_id=users.id’])

This gives me an “association name not found” error. From my log file
the sql string is as follows:

‘FROM memberships INNER JOIN groups ON memberships.group_id=
group.idINNER JOIN users ON memberships.user_id=users.id’

Can anyone please let me know where I’m going wrong - SQL is not my
forté :@/

Thanks

Steve


#2

I don’t know the answer but something that jumped out at me was that you
don’t have a space in between “group.idINNER”. You may want to try it
with something like

:joins => [‘FROM memberships INNER JOIN groups ON memberships.group_id=
group.id’ + ’ INNER JOIN users ON memberships.user_id=users.id’])

Note the single space before second INNER

-S

Stephen F. wrote:

I want to link up 3 tables to list all members of a particular group. My
code (which is wrong) looks like this:

@group_members = Membership.find( :all,
:joins => [‘FROM memberships INNER
JOIN groups ON memberships.group_id= group.id’ + ‘INNER JOIN users ON
memberships.user_id=users.id’])

This gives me an “association name not found” error. From my log file
the sql string is as follows:

‘FROM memberships INNER JOIN groups ON memberships.group_id=
group.idINNER JOIN users ON memberships.user_id=users.id’

Can anyone please let me know where I’m going wrong - SQL is not my
forté :@/

Thanks

Steve


#3

Do you have a space between group.idINNER JOIN users?
In your code and log file, it looks like the space is missing.

On May 1, 9:55 am, Stephen F. removed_email_address@domain.invalid


#4

On May 1, 5:55 pm, Stephen F. removed_email_address@domain.invalid
wrote:

I want to link up 3 tables to list all members of a particular group. My
code (which is wrong) looks like this:

@group_members = Membership.find( :all,
:joins => [‘FROM memberships INNER
JOIN groups ON memberships.group_id= group.id’ + ‘INNER JOIN users ON
memberships.user_id=users.id’])

this form of sql assumes that you are giving it a list of associaton
names, ie

Membership.find(:all, :joins => [:groups, :users]) looks like it would
do what you are trying to do.

The other form involves sql fragments, in which case you must provide
one such fragment, not an array ie

@group_members = Membership.find( :all,
:joins => ‘INNER JOIN groups ON
memberships.group_id= group.id INNER JOIN users ON
memberships.user_id=users.id’)

Fred