SQL-query: Select item from table field of list of objects of another table

Question

Question

Yana Rchks · Oct 30, 2019

I am trying to make architecture for my project. And for it, it needs to make list property in which there is list of objects of another table, the data must be unique, therefore I make sql-query to check objects of another table if it is used or not, but I can't make sql-query to check elements in list in property with elements of another table. Can you help me? In property ID of object is used.

Discussion (12)0

Log in or sign up to continue

Vitaliy Serdtsev · Nov 4, 2019

There are a couple of comments:

Instead of

Class packet.Actor Extends (%Persistent, %Populate)
{
  Property Name As %String [ Required ];

  Index NameIndex On Name [ Unique ];

  Property Age As %Integer (MAXVAL=100, MINVAL=10) [ Required ];

  Index AgeIndex On Age [ Unique ];

  Property Sex As %String (DISPLAYLIST=",Woman,Man") [ Required ];
}

better

Class packet.Actor Extends (%Persistent, %Populate)
{

Index NameIndex On (Name, Age) [ Unique ];

Property Name As %Name [ Required ];

Property Age As %Integer(MAXVAL = 100, MINVAL = 10) [ Required ];

Property Sex As %String(VALUELIST = ",Woman,Man") [ Required ];

}

Otherwise it will not be possible to insert two or more persons with the same age. Or do you specifically want it?

Property Actor As list of packet.Actor (POPSPEC=".ActorFilter()") [Required];
This code contradicts the documentation (Specifying the POPSPEC Parameter for List Properties):

Leave basicspec empty if the property is a list of objects.

However, in this case, ActorFilter() should return strictly one ID, not a collection.
The data in the Actor property is stored as $lb($lb(ID1),$lb(ID2),..,$lb(IDN)), therefore, to select all records that have ID=6 in this field, you need to perform
select * from packet.Movie where $listbuild('6') %inlist Actor

It seems to me that in your case it would be easier to do so:

Property Actor As list Of packet.Actor(STORAGEDEFAULT = "array") [ Required ];

Then the query will be simplified:

select * from packet.Movie_Actor where Actor=6

Or even so

select * from packet.Movie where Movie_Actor->Actor=6

0 0

Michael Cohen · Nov 13, 2019

Sorry, but the problem sill is not clear to me.

First, I don't think that you want a SQL query that returns all movies with unique Man Actors.

I think you want to prevent save of a Movie if all Actors are not Man or list of Actors is not unique for this Movie.

Assuming that, are Movies being saved via SQL Insert/Update, via object Save, or both?

Once you confirm, we should have some suggestions to confirm that the Movie's list of Actors is acceptable.

0 0

Michael Cohen · Nov 14, 2019

I think I now understand your question and why you asked.

You want to control the list of Actors stored for each Movie by Populate().

The problem is that, for each new Movie, your ActorFilter method is called by Populate a number of times to create the list of Actors for the Movie.

ActorFilter needs to return a random Actor ID that is for a Man, and that was not already generated for this Movie.

Checking that the random Actor ID exists and is a Man is straightforward.

The trick is to check the interim list of Actors for this Movie to ensure that a duplicate ID is not returned.

Since Populate and ActorFilter are instance methods in Movie, the ..Actor syntax accesses the current/partial Actor %Collection.ListOfObj in the Movie currently being Populated.

Here are my example Movie and Actor classes to accomplish this.

Class Packet.Actor Extends (%Persistent, %Populate)
{
Property Name As %String [ Required ];

Index NameIndex On Name [ Unique ];

Property Age As %Integer(MAXVAL = 100, MINVAL = 10) [ Required ];

Property Sex As %String(DISPLAYLIST = ",Woman,Man", VALUELIST = ",F,M") [ Required ];
}

Class Packet.Movie Extends (%Persistent, %Populate)
{
Property Title As %String(POPORDER = 0) [ Required ];

Index TitleIndex On Title [ Unique ];

Property Date As %Date(POPORDER = 2) [ Required ];

Property Actor As list Of Packet.Actor(POPSPEC = ".ActorFilter()") [ Required ];

Method ActorFilter()
{
// Returns a Packet.Actor ID, where Actor is a Man not already in this Movie's current Actors list
// Assumes Actors was just populated, so <IdLocation>^Packet.ActorD</IdLocation> identifies location of last ID
s maxID=^Packet.ActorD
// loop until we find an ID or tried 100 times
s tried=0
s ID=""
while ID="" {
s tried=tried+1
if tried>100 quit
s ID=$random(maxID)+1
// ..Actor is this Movie's list of Actor IDs so far
s found=0
if ..Actor.Size>0 {
   //check whether this ID was already used
   s key=""
   s used=..Actor.GetObjectIdNext(.key)
   while key'="" {
       if ID=used {s found=1 quit } //this ID was already used
   s used=..Actor.GetObjectIdNext(.key)
   }
}
if found s ID="" CONTINUE //already used
s actor=##class(Packet.Actor).%OpenId(ID)
if actor="" CONTINUE //not found
if actor.Sex'="M" s ID="" CONTINUE //wrong Sex
}
quit ID
}

}

0 0

score 0 · Answer 1 · 2019-10-31T08:03:03-04:00

Vitaliy Serdtsev · Oct 31, 2019

Hi, Yana!

Give the class code, the source data and an example of what you want to get in the end.

0 0

score 0 · Answer 2 · 2019-11-02T01:09:18-04:00

main class is packet.Movie

Class packet.Movie Extends (%Persistent, %Populate)
{
Property Title As %String (POPORDER = 0) [ Required ];

Index TitleIndex On Title [ Unique ];

Property Date As %Date (POPORDER=2) [ Required ];

Index DateIndex On Date [ Unique ];

Property Actor As list of packet.Actor (POPSPEC=".ActorFilter()") [Required];

The second class is packet.Actor:

Class packet.Actor Extends (%Persistent, %Populate)
{
Property Name As %String [ Required ];

Index NameIndex On Name [ Unique ];

Property Age As %Integer (MAXVAL=100, MINVAL=10) [ Required ];

Index AgeIndex On Age [ Unique ];

Property Sex As %String (DISPLAYLIST=",Woman,Man") [ Required ];

ActorFilter() is method to filter packet.Actor objects

score 0 · Answer 3 · 2019-11-04T06:07:52-05:00

That would work storagetime but not at runtime.

I think runtime hook disallowing duplicate inserts would be better.

To OP. Age seems like a strange property to store in your use case. It's better to store DoB I think.

score 0 · Answer 4 · 2019-11-04T06:51:33-05:00

That would work storagetime but not at runtime.

It was just a matter of ease of accessing data using SQL. No more than.

Who knows, maybe the author just decided to use SQL to identify duplicates?

I think runtime hook disallowing duplicate inserts would be better.

I agree. But this is a completely different task and is solved in a different way.
Like this:

Property Actor As list Of packet.Actor(POPSPEC = ".ActorFilter():10") [ Required ];

Method ActorFilter() As %Integer
{
  set actorID=$random(10)+1

  for i=1:1:$order(i%Actor(""),-1) return:actorID=$list(i%Actor(i),1) ""

  quit actorID
}

The author after all asked about SQL therefore I above and asked to specify that it is necessary actually.

score 0 · Answer 5 · 2019-11-10T10:56:54-05:00

Unfortunately, you didn't understand my idea

I don't care how many elements will be in Actor fields in packet.Movie

in .ActorFilter() method it is filtered element from packet.Actor in the way: an element has to have "Man" value in the "Sex" field and doesn't repeat in the next strings of packet.Movie table. And it requires to get elements from field "Actor" (packet.Movie) and compare with ID from packet.Actor

Nevermind how it will be implemented: sql or object syntax because I couldn't find information or documentation about the case

score 0 · Answer 6 · 2019-11-10T16:09:24-05:00

Do you want for each Movie object (row) to guarantee that it's Actors property would be unique (containing only distinct Actors)?

Or is it something else?

Can you show us examples of correct and incorrect Movie records?

score 0 · Answer 7 · 2019-11-12T04:59:56-05:00

Yes? my example:

Movie table:

Id Actor

1 3, 4, 5 (ID of packet.Actor objects)

2 6,7,8

packet.Actor objects have to have the "Man" value in the "Sex" field and in every object of packet.Movie there are unique value if the "Actor" field

score 0 · Answer 8 · 2019-11-13T16:01:49-05:00

Michael Cohen · Nov 13, 2019

OR, is the goal to be able to Populate Actor and Movie data that satisfies the conditions?

0 0

score 0 · Answer 9 · 2019-10-31T14:06:15-04:00

Is there a reason it has to be an sql query? Using object syntax, opening the object whose list you want to check, and then checking it with something like myObj.myListProperty is a very good option if possible.