Thanks David - a meticulous piece of work :) 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 ;)
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 Jones
David Stubbs wrote:
> 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);
>
>
>
> Robert Jones wrote:
>> 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,
>>
>>