Complex Select statement for View
Summary of what I need to do:
I have a table called Actions within which I am trying to create a view of all actions which are compatible with the current action.
Details:
An Action is undertaken by an Entity. The Entity performing the Action is considered the Actor of the Action, and the Entity the Action is being performed on is considered the Target of the Action. Entities are composed of different parts, and an Action needs access to a specific set of parts from the target Entity in order to perform the action.
Every Entity is always engaged in an action, either as an Actor or a Target.
An Actor can only perform one action at a time, but can be the target of another Actor's Action. So an Entity can be engaged in two actions at the same time, one as the Actor, and the other as the Target.
So, when comparing two Actions for compatibility, the target of the first Action could be either the Actor or the Target of the second Action.
Two Actions are considered compatible when the parts set the first action needs access to is available from the Target of the same action.
The parts sets are just that, sets in the mathematical sense in that some overlap. So, for example, there is a set that is comprised of all the parts, and there are others that are comprised of some of the parts. So for each set of parts, I need to know which other sets it's compatible with. There's only nine different sets, so what I did is I created a PartsSets table which holds the nine different sets of parts. I have a second table called PartsSetsCompatibility which contains a reference to the PartsSets table named PartsSet and a multiple choice(dynamic) field with a dynamic value of select PartsSets named PartsSetCompatibility. This lets me go through the nine sets one at a time and select which of the other eight sets it is compatible with. As an example, the set comprised of all parts is only compatible with the set comprised of no parts (there are a few actions which don't require any parts).
So, back to the Actions table. Within it have (among others) the following fields:
ActionPartsNeeded: This is the set of parts required to be available from the Target of the Action in order for the Actor to perform the action. It's a link to the PartsSets table.
Actor: ID of the entity performing the action
ActorPartsAvailable: Another link to the PartsSets table, this time showing the set of parts the actor leaves available while performing the action.
Target: ID of the entity the action is being performed on
TargetPartsAvailable: Another link to the PartsSets table, this time showing the set of parts the Target leaves available while the Action is performed on it.
Problem restated:
OK, so within each record of the actions table I need a view listing all the other actions which it is compatible with and I'm trying to figure out how to create the select statement for this view.
Select Actions where ActionPartsNeeded is Compatible with the PartsAvailable from either the Actor or the Target of the current Action, depending on which is the Target. This is where I hit a brick wall.
I thought of creating a user defined function as a field in the actions table that I could use in the select statement as follows, while the syntax is eluding me it would look something like this:
Select Actions where isActionCompatible(Actions.ActionPartsNeeded, Actions.Target)
function isActionCompatible(ActionPartsNeeded: number, ActionTarget: number) do
(I really struggle with Ninox's syntax so this is sort of pseudocode, hopefuly its actually doable).
if ActionTarget = Actor {
if ActorPartsAvailable = ActionPartsNeeded.PartsSetCompatibility (how do you compare against a multi-choice field?) then return true else return false
}
else /*ActionTarget = Target*/ {
if TargetPartsAvailable = ActionPartsNeeded.PartsSetCompatibility
then return true else return false
}
end;
Anyway, I know this is pretty complicated, so huge kudos in advance for anyone who offers to help (hopefully I've provided enough detail).
19 replies
-
I left something out and I couldn't figure out how to edit my post, so I'm appending this:
There are only three Entities, therefore, an Entity can't be engaged in more than two actions. So, when checking to see if an Action an Entity wants to start (as the Action's Actor) is compatible with another Action, the other two Entities are either the Target or the Actor of the Action I am comparing against.
-
Hi Mike
It all sounds so intriguing. I'd like to have a look at this but worry that I might spend an hour so two barking up the wrong tree. Can you give me a couple of examples? And maybe also show an expected output. If you could share the database, even better.
Thanks John
-
I put the example together a little too quickly and missed checking a couple compatible parts sets with the Parts Set Compatibility record.
Lower parts - Gimbal should also be compatible with Lower Parts + Head
and No Parts should be compatible with all others (I missed checking All lower parts for that one).
Things probably make a little more sense with those changes but it would just make the result sets a little different.
-
Hi Mike -
Can you give us an example the results of the found set?
For example if I'm in the Flare Gymbol 3 record in the Actions table, what records should I see if the view element has the proper select statement?
-
I probably could have done a better job picking my examples, but assuming you made the two changes I mentioned yesterday in Parts Set Compatibility table (Lower parts - Gimbal should also be compatible with Lower Parts + Head, and and No Parts should be compatible with all others, i.e also compatible with All lower parts). Also, I just realized I forgot a rule: Compatible actions must include all 3 entities. Said differently, if an entity is the target of an action, the targeted entity can't target the actor of the same action. A couple examples, these are not allowed:
Going through the examples marking which are compatible with which I just realized I need an extra field. The action parts needed field shows the parts needed from the target, but we also need parts from the actor and those two must be available. I apologize for not thinking this all the way through (it's been a bit of a challenge figuring out how to make it all work). Let me add that field and populate it with the correct data and repost the database.
-
OK, updated database attached.
restating the rules for compatible actions as methodically as I can:
1. Rule out all actions which have the same actor as the current action.
2. Rule out all actions which don't include the entity not in the current action. (if the action includes entities 2 and 3, a compatible action must include entity 1 (can be actor or target).
3. The parts needed by the actor and target from the compatible action must be available from the any entity in the current action. The Parts Set Compatibility table shows us which available parts sets work with the needed parts set. So, if we need all lower parts, and all parts are available, that works as can be seen in the all lower parts record in that table.
Expected outcome for a few records:
1 rework 2 compatible with: 3 hammer 1, 3 idle 1,
2 flare head 1 compatible with: 1 idle 2, 3 idle 1,
1 spin upper 3 compatible with: 3 spin lower 2
1 flare gymbol 3: 2 spin upper 1
-
Ok, so I've worked out what I think is how to do the first two steps.
let t := this; let selActions := (select Actions); let xAct := selActions[Actor = t.Actor]; let xTar := selActions[Target = t.Target]; let xSel := selActions[Actor != t.Actor and Target != t.Actor or Actor != t.Target and Target != t.Target]; let step1 := xSel[(var r := this; count(xAct[= r])) = 0]; let step2 := step1[(var r2 := this; count(xTar[= r2])) = 0]; concat(step2)
The variable selActions finds all records in the Actions table. Best practice says it is best not to do anymore select statements then necessary. Most people would have probably written a select statement for each of the variables on line 3 - 5. (Maybe I did too
).
Then the xAct variable finds all records where the Actor equals the current Actor.
The xTar variable does the same for Target.
The xSel variable finds all records where the Actor in the current record is not an Actor or Target in another record and does the same for the Target. This will be our base.
The variable step1 takes the array of records in xSel and subtracts any records found in xAct.
Variable step2 then takes the results of step1 and subtracts any records found in xTar.
The last line just displays the records left.
Now that just leaves me to your step 3. And it is what I can't wrap my wee brain around. If you can clarify it a bit more. Can you put in terms of the db. If this field in this table equals to this field in that table, etc.
I took your examples and I don't think the 2 Flarehead 1 gets the record 1 and 7. My formula shows that 1 doesn't show up. The other examples work, so I don't know if maybe there is some mistake there.
I've attached the DB with my new fields so you can see how I went about figuring it out. I create a few new fields to see what results I get so I can see if I'm on the correct path.
-
Why isn't there an ANSI standard SQL supported with an interface rather than this confusing proprietary "SELECT" approach?
Content aside
- Status Answered
- 2 yrs agoLast active
- 19Replies
- 734Views
-
5
Following