Hi Robert,
I’m sure that this isn’t the “right way” but it should work ok if I
understand what you are after:
registration controller
class Registration < ActiveRecord::Base
def find_all_pupils
my_sql = "
select * from pupils where id in (
select p.id
from pupils p, groups_pupils gp, groups_registrations gr
where gr.registration_id = ? and gr.group_id = gp.group_id and
gp.pupil_id = p.id
union
select p.id
from pupils p, pupils_registrations pr
where pr.registration_id = ? and pr.pupil_id = p.id
)"
Pupils.find_by_sql([my_sql, self.id,self.id])
end
end
– pupils
drop table if exists pupils;
create table pupils (
id bigint not null auto_increment,
name varchar(50) not null,
primary key (id)
) ENGINE=InnoDB CHARSET=utf8 ROW_FORMAT=DYNAMIC;
– Groups
drop table if exists groups;
create table groups (
id bigint not null auto_increment,
name varchar(50) not null,
primary key (id)
) ENGINE=InnoDB CHARSET=utf8 ROW_FORMAT=DYNAMIC;
– Groups to Pupils
drop table if exists groups_pupils;
create table groups_pupils (
pupil_id bigint not null,
group_id bigint not null,
primary key (pupil_id, group_id)
) ENGINE=InnoDB CHARSET=utf8 ROW_FORMAT=DYNAMIC;
– Registrations
drop table if exists registrations;
create table registrations (
id bigint not null auto_increment,
name varchar(50) not null,
primary key (id)
) ENGINE=InnoDB CHARSET=utf8 ROW_FORMAT=DYNAMIC;
– Groups to Registrations
drop table if exists groups_registrations;
create table groups_registrations (
registration_id bigint not null,
group_id bigint not null,
primary key (registration_id, group_id)
) ENGINE=InnoDB CHARSET=utf8 ROW_FORMAT=DYNAMIC;
– Pupils to Registrations
drop table if exists pupils_registrations;
create table pupils_registrations (
pupil_id bigint not null,
registration_id bigint not null,
primary key (pupil_id, registration_id)
) ENGINE=InnoDB CHARSET=utf8 ROW_FORMAT=DYNAMIC;
insert into pupils (id,name) values (1,“David”);
insert into pupils (id,name) values (2,“John”);
insert into pupils (id,name) values (3,“Tony”);
insert into pupils (id,name) values (4,“Mike”);
insert into groups (id,name) values (1,“Test group 1”);
insert into groups_pupils (group_id, pupil_id) values (1,3);
insert into groups_pupils (group_id, pupil_id) values (1,4);
insert into registrations (id, name) values (1,“Registration Group”);
insert into groups_registrations (group_id, registration_id) values
(1,1);
insert into pupils_registrations (pupil_id,registration_id) values
(1,1);
insert into pupils_registrations (pupil_id,registration_id) values
(2,1);