Forum: Ruby on Rails A plea for help with an Open Source project.

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
80b3b3a2e46cc1418f92497a1e8f84ae?d=identicon&s=25 jonesieboy (Guest)
on 2005-11-27 09:50
(Received via mailing list)
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 Jones
6a68c22101c4292f82f0a10f08a0d247?d=identicon&s=25 stubbsd (Guest)
on 2005-11-27 13:29
(Received via mailing list)
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);
80b3b3a2e46cc1418f92497a1e8f84ae?d=identicon&s=25 jonesieboy (Guest)
on 2005-11-27 17:44
(Received via mailing list)
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
This topic is locked and can not be replied to.