具有多对多关系的级联值列表 [英] Cascading List of Values with many to many relationship

问题描述

我正在开发一个应用程序，该应用程序可以跟踪Apex学校中学生的上课情况. 我想创建一个包含三个级联选择列表的页面，以便老师可以先选择学期，然后选择学科，然后选择该学科的特定班级，以便应用程序返回已注册该班级的学生.

I am developing an application which tracks class attendance of students in a school, in Apex. I want to create a page with three level cascading select lists, so the teacher can first select the Semester, then the Subject and then the specific Class of that Subject, so the application returns the Students who are enrolled in that Class.

我的问题是这三个表之间有多对多的关系，因此我在键上使用了额外的表. 每个学期都有许多科目，并且一个科目可以在许多学期中教授. 每个科目每学期都有许多课程. 学生必须每学期注册一门课程，然后老师可以将其分配给班级.

My problem is that these three tables have a many-to-many relationship between them, so I use extra tables with their keys. Every Semester has many Subjects and a Subject can be taught in many Semesters. Every Subject has many classes in every Semester. The students must enroll in a subject every semester and then the teacher can assign them to a class.

表看起来像这样:

create table semester(
  id number not null,
  name varchar2(20) not null,
  primary key(id)
);

create table subject(
  id number not null,
  subject_name varchar2(50) not null,
  primary key(id)
);

create table student(
  id number not null,
  name varchar2(20),
  primary key(id)
);

create table semester_subject(
  id number not null,
  semester_id number not null,
  subject_id number not null,
  primary key(id),
  foreign key(semester_id) references semester(id),
  foreign key(subject_id) references subject(id),
  constraint unique sem_sub_uq unique(semester_id, subject_id)
);

create table class(
  id number not null,
  name number not null,
  semester_subject_id number not null,
  primary key(id),
  foreign key(semester_subject_id) references semester_subject(id)
);

create table class_enrollment(
  id number not null,
  student_id number not null,
  semester_subject_id number not null,
  class_id number,
  primary_key(id),
  foreign key(student_id) references student(id),
  foreign key(semester_subject_id) references semester_subject(id),
  foreign key(class_id) references class(id)
);

学期"选择列表的值列表如下所示:

The list of value for the Semester select list looks like this:

select name, id
from semester
order by 1;

主题选择列表应包括以上所选学期中所有可用主题的名称，但我无法确定查询内容，即使可能也不行.我现在所拥有的:

The the subject select list should include the names of all the Subjects available in the semester selected above, but I can't figure the query or even if it's possible. What I have right now:

select s.name, s.id
from subject s, semester_subject ss
where ss.semester_id = :PX_SEMESTER //value from above select list
and ss.subject_id = s.id;

但是在LoV中不能有两个表，无论如何查询可能是错误的... 我什至没有开始考虑该类的查询是什么样的.

But you can't have two tables in a LoV and the query is probably wrong anyway... I didn't even begin to think about what the query for the class would look like.

我非常感谢您的帮助，或者您可以为我指明正确的方向，以便我自己解决问题.

I appreciate any help or if you can point me in the right direction so I can figure it out myself.

推荐答案

使用项参数化的值列表(LOV)开发Apex输入表单

您的初始架构设计看起来不错.在以较小的规模开发并测试了解决方案后，一项建议是将触发器添加到ID(主键)列，该触发器可以通过一个序列自动填充其值.您也可以跳过触发器，而仅在sql insert DML命令中引用序列.它只是使事情变得简单.使用内置向导在APEX环境中创建表可提供创建自动递增"键列的机会.

Developing an Apex Input Form Using Item-Parametrized Lists of Values (LOVs)

Your initial schema design looks good. One recommendation once you've developed and tested your solution on a smaller scale, append to the ID (primary key) columns a trigger that can auto-populate its values through a sequence. You could also skip the trigger and just reference the sequence in your sql insert DML commands. It just makes things simpler. Creating tables in the APEX environment with their built-in wizards offer the opportunity to make an "auto-incrementing" key column.

在SEMESTER表中还添加了一个名为SORT_KEY的附加列.当您存储具有逻辑排序序列(本质上不完全是字母数字)的字符串类型的值时，这很有用.

There is also an additional column added to the SEMESTER table called SORT_KEY. This helps when you are storing string typed values which have logical sorting sequences that aren't exactly alphanumeric in nature.

这是我生成的测试数据，用于演示适用于该示例的 级联值列表 设计.

Here is the test data I generated to demonstrate the cascading list of values design that will work with the example.

下一步是创建前三个相互依赖的值列表定义.如您所知，您可以在LOV中引用页面参数，这些参数可能来自多种来源.在这种情况下，我们LOV中的选择项将分配给 顶点页面项 .

The next step is to make the first three inter-dependent List of Values definitions. As you have discovered, you can reference page parameters in your LOVs which may come from a variety of sources. In this case, the choice selection from our LOVs will be assigned to Apex Page Items.

我还认为在单个LOV查询中只能引用一个表. 这是不正确的.该页面文档建议限制因素是SQL查询语法.以下LOV查询引用多个表，并且它们起作用:

I also thought only one table could be referenced in a single LOV query. This is incorrect. The page documentation suggests that it is the SQL query syntax that is the limiting factor. The following LOV queries reference more than one table, and they work:

 -- SEMESTER LOV Query
 -- name: CHOOSE_SEMESTER

 select a.name d, a.id r
   from semester a
  where a.id in ( 
        select b.semester_id
          from semester_subject b
         where b.subject_id = nvl(:P5_SUBJECT, b.subject_id))
  order by a.sort_id


 -- SUBJECT LOV Query
 -- name: CHOOSE_SUBJECT     

 select a.subject_name d, a.id r
   from   subject a
  where  a.id in ( 
         select b.subject_id
           from semester_subject b
          where b.semester_id = nvl(:P5_SEMESTER, b.semester_id))
  order by 1


 -- CLASS LOV Query
 -- name: CHOOSE_CLASS

 select a.name d, a.id r
   from class a, semester_subject b
  where a.semester_subject_id = b.id
    and b.subject_id = :P5_SUBJECT
    and b.semester_id = :P5_SEMESTER
  order by 1

一些设计注意事项:

• 不要介意P5_ITEM表示法.我的示例应用程序中的页面恰好位于第5页"上，因此约定生效.
• 我选择为每个LOV查询分配一个名称作为提示.不要只是将查询嵌入到项目中.通过使LOV成为可移植对象，为自己作为开发人员增加一些喘息的空间，如果需要，可以在其他地方引用该对象.
• 通过应用程序设计器的SHARED OBJECTS菜单选项为每个查询命名LOV.
• 与CHOOSE_SEMESTER LOV的nvl(:P5_SUBJECT, b.subject_id)一样，涉及NVL命令的额外运算符也是在CHOOSE_SUBJECT查询中镜像的表达式.如果进入页面时P5_SUBJECT和P5_SEMESTER的默认值为空，那么如何帮助处理级联关系?
• 表SEMESTER_SUBJECT表示键关系. 为什么不需要此表的LOV?

• Don't mind the P5_ITEM notation. The page in my sample app happened to be on "page 5" and so the convention goes.
• I chose to assign a name for each LOV query as a hint. Don't just embed the query in an item. Add some breathing room for yourself as a developer by making the LOV a portable object that can be referenced elsewhere if needed.
• MAKE a named LOV for each query through the SHARED OBJECTS menu option of your application designer.
• The extra operator involving the NVL command, as in nvl(:P5_SUBJECT, b.subject_id) for the CHOOSE_SEMESTER LOV is an expression mirrored on the CHOOSE_SUBJECT query as well. If the default value of P5_SUBJECT and P5_SEMESTER are null when entering the page, how does that assist with the handling of the cascading relationships?
• The table SEMESTER_SUBJECT represents a key relationship. Why is a LOV for this table not needed?

设置一个页面以测试模式设计和LOV查询需要创建三个页面项:

Setting up the a page for testing the schema design and LOV queries requires the creation of three page items:

每个页面项都应定义为SELECT LIST，最初应保留所有默认设置，直到您了解基本设计的工作原理为止.每个选择列表项都应与其对应的LOV相关联，例如:

Each page item should be defined as a SELECT LIST leave all the defaults initially until you understand how the basic design works. Each select list item should be associated with their corresponding LOV, such as:

关键设计要素是为CHOOSE_CLASS LOV创建的 Select List ，它表示对多个数据源的级联依赖性.

The key design twist is the Select List made for the CHOOSE_CLASS LOV, which represents a cascading dependency on more than one data source.

我们将使用级联父级"(Cascading Parent)选项，以便该项目将一直等待，直到同时选择了CHOOSE_SEMESTER和CHOOSE_SUBJECT.如果两者中的任何一个被更改，它也会刷新.

We will use the "Cascading Parent" option so that this item will wait until both CHOOSE_SEMESTER and CHOOSE_SUBJECT are selected. It will also refresh if either of the two are changed.

是！:级联的父项可以包含多个页面项/元素.只需在逗号分隔的列表中声明它们即可.

YES! The cascading parent item can consist of multiple page items/elements. They just have to be declared in a comma separated list.

从在线帮助信息中，这是如何在APEX设计中使用级联LOV的一般介绍:

From the online help info, this is a general introduction to how cascading LOVs can be used in APEX designs:

来自Oracle Apex帮助文档::级联LOV表示，如果此页面上另一个项目的值发生更改，则应刷新当前项目的值列表.

From Oracle Apex Help Docs: A cascading LOV means that the current item's list of values should be refreshed if the value of another item on this page gets changed.

指定用逗号分隔的页面项列表，以用于触发刷新.然后，您可以在值列表" SQL语句的where子句中使用这些页面项.

Specify a comma separated list of page items to be used to trigger the refresh. You can then use those page items in the where clause of your "List of Values" SQL statement.

具有级联LOV的APEX应用项目的演示

这些示例基于此解决方案开始时给出的示例数据.所选示例案例的路径为:

Demonstration of APEX Application Items with Cascading LOVs

These examples are based on the sample data given at the beginning of this solution. The path of the chosen example case is:

SEMESTER:2014年春季 + 主题:PHYS ED + 验证有效的课程选项:

 Fitness for Life
 General Flexibility
 Presidential Fitness Challenge
 Running for Fun
 Volleyball Basics

上面的选择将分配给页面项目P5_CLASS.

The choice from above will be assigned to page item P5_CLASS.

P5_SEMESTER的选择选项:

Selection Choices for P5_SEMESTER:

P5_SUBJECT的选择选项:

Selection Choices for P5_SUBJECT:

P5_CLASS的选择选项:

Selection Choices for P5_CLASS:

在处理这个设计项目时，我想到了一些封闭的想法:

Some closing thoughts that occurred to me while working with this design project:

• 关于主键:对于主键，使用通用的ID命名列的概念是一个不错的设计选择.虽然APEX可以处理复合业务密钥，但它笨拙且难以解决.

• About the Primary Keys: The notion of a generic, ID named column for a primary key was a good design choice. While APEX can handle composite business keys, it gets clumsy and difficult to work around.

使架构设计难以使用的一件事是，"id"的概念在引用它的其他表中转换了. (例如SEMESTER表中的ID列变为SEMESTER_SUBJECT表中的SEMESTER_ID.只需通过较大的查询来留意这些名称更改.有时我实际上确实无法准确地知道自己是什么ID

One thing that made the schema design challenging to work with was that the notion of "id" transformed in the other tables that referenced it. (Such as the ID column in the SEMESTER table became SEMESTER_ID in the SEMESTER_SUBJECT table. Just keep an eye on these name changes with larger queries. At times I actually lost track exactly what ID I was working with.

理智的话:在可能的情况下，您决定通过数据库序列对象分配ID值，默认值通常是从1开始.如果您的架构中有几个具有相同列名的不同表:ID和一些关联表(例如CLASS_ENROLLMENT，这些表将一个主键ID和三个其他外键ID's的值连接在一起)很难分辨数据值从何而来.

A Word for Sanity: In the likely event you decide to assign ID values through a database sequence object, the default is usually to begin at one. If you have several different tables in your schema with the same column name: ID and some associating tables such as CLASS_ENROLLMENT which connects the values of one primary key ID and three additional foreign key ID's, it may get difficult to discern where the data values are coming from.

考虑偏移序列或任意选择不同的增量和起始值.如果您主要在查询中使用ID，那么如果两个不同的ID集之间相隔两个或三个数量级，则很容易知道您是否提取了正确的数据值.

Consider offsetting your sequences or arbitrarily choosing different increments and starting values. If you're mainly pushing ID's around in your queries, if two different ID sets are separated by two or three orders of magnitude, it will be easy to know if you've pulled the right data values.

还有更多的级联关系吗??如果父级"项关系表明某个依赖项导致某个页面项LOV等待或根据另一个值的变化而变化，那么是否可能存在另一个级联关系来定义?在CHOOSE_SEMESTER和CHOOSE_SUBJECT的情况下可以吗?有必要吗?

Are There MORE Cascading Relationships? If a "parent" item relationship indicates a dependency that makes a page item LOV wait or change depending on the value of another, could there be another cascading relationship to define? In the case of CHOOSE_SEMESTER and CHOOSE_SUBJECT is it possible? Is it necessary?

我能够弄清楚如何使这两个项目具有可选级联依赖性，但它需要设置另一个外部页项目引用. (如果不是可选的，则一旦两个值之一发生更改，您就会陷入闭环中.)花哨的但不是解决当前问题的必要方法.

I was able to figure out how to make these two items hold an optional cascading dependency, but it required setting up another outside page item reference. (If it isn't optional, you get stuck in a closed loop as soon as one of the two values changes.) Fancy, but not really necessary to solve the problem at hand.

剩下要做的事情?我遗漏了一些其他任务供您继续，例如在选择有效的STUDENT之后将DML管理到ENROLLMENT表中.

What's Left to Do? I left out some additional tasks for you to continue with, such as managing the DML into the ENROLLMENT table after selecting a valid STUDENT.

总体而言，您已经有了可行的方案设计.有一种方法可以通过APEX应用程序设计模式来表示数据关系.编码愉快，看起来像是一个具有挑战性的项目！

Overall, you've got a workable schema design. There is a way to represent the data relationships through an APEX application design pattern. Happy coding, it looks like a challenging project!

这篇关于具有多对多关系的级联值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！