A plea for help with an Open Source project

Rails
1

Hi - this is probably a bit OT, but here goes anyway:

FreeMIS is an open source school MIS ( demo.freemis.net )

If anyone is interested in helping out with this project, either by
answering the particular question below or more generally, I’d be really
grateful. Here’s the problem:

I have got along fine so far with pupils, pupil_sets and a link table
for
the habtm relationship between them.

Now, though, I would like to be able to model more accurately the
reality
that pupils are assigned to more general groupings (registration classes
and practical groups, for example) and that the pupil_sets are sometimes
based on these more general groupings.

So, what I want to model is that pupils can be assigned to pupil_sets,
but
also groups can be assigned to pupil_sets. Ideally, this will work in
such
a way that when I do @pupil_set.pupils, I get all the pupils in the
pupil_set, irrespective of whether they are there via a group allocation
or
via an individual allocation.

Cheers,

–
Robert J.

2

Thanks David - a meticulous piece of work :slight_smile: Did you have this sitting
around already, or did you produce it off-the-cuff today? Impressive
either
way.

Your solution is along the lines of the one lurking in my head but which
I
was too lazy to implement - though yours is much better, of course :wink:

What I need is slightly different - registration groups are, for me, an
example of what you have called “groups”, and it would be a different
class, “pupil_sets”, that were constituted of individual allocations to
pupil_sets and group allocations to pupil_sets. But that’s by-the-by -
your technique will work for me.

As you say, though, it doesn’t feel very rail-ish. I wonder if there
isn’t
some way to do this without delving into writing our own SQL queries? I
bet there is!

My next challenge is that I currently refer to the pupil<->pupil_set
link
table rows as objects in their own right. In particular, a full report
to
parents exists for each pupil<->pupil_set row, and so has the id of a
row
in the link table as a foreign key. If pupils might be allocated to a
pupil_set via a group allocation, then I’ll need to rethink my strategy
there.

I’m rambling…thanks again for your help.

Robert J.

3

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);