Select more fields in a a db annotation and unique count

Hi I am adding a db annotation to a type of mine.
I want a new filed that gets data from another type (external), it filters and counts unique values of a specific field.

Lets call my type Pod and lets call the type from which I need to get data FullPod. This is what I get:

 @db(order='descending(endDateRefM1), descending(beginDateRefM1)', filter = '(endDateRefM1 == "9999-12-31T23:59:59.000") && (endDateRefM2 == "9999-12-31T23:59:59.000")', include: 'podM2')
  podFullPod: [FullPod](id)

 numChildren: int stored calc "count(unique(podFullPod.podM2))"

So I created a field podFullPod that, using the annotation, does the “join” between Pod and FullPod. I would expect that what it gets there is an object of ids and of a field called podM2 (since there is an include in the annotation), but It returns only an array of ids.
In the other field (numChildren) I want to do a count of unique values of podM2, but since podFullPod does not contain podM2, the unique count that I get back is wrong.

Did I miss something? How do I force the include?
The version of the platform I am using is 7.8

Sorry for the confusion, but there was some escape character. Now the my code it is fixed and maybe makes more sense: I want to furthermore add that the type FullPod mixes External
Hopefully it makes more sense.

@marcosordi The annotations you used are only applicable for fkey fields and are used when we fetch from the target type to return the results for the fkey fields when requested. For a simple reference, which you have here, the reference “is” the value of the field and references a single FullPod (so ordering doesn’t even make sense). When you query, you can include whatever you want (with dot queries to filter children, etc.).

I’m not sure how the filter would relate to the stored calc because, as I said, the reference is the value for ‘podFullPod’. I believe it would count all of the unique ‘podM2’ values within ‘podFullPod’, which would only make sense if ‘podM2’ is an array. Otherwise I have no idea what you are trying to do here.

Hi @trothwein. Thanks for your answer. There was a small mistake in the code posted, due to a typo. Now it is edited and correcteted. I hope it makes more sense now.

@mmichela Yes that makes more sense. I believe your issue is that ‘FullPod’ is an external types. We don’t support automatically refreshing stored calc fields that depend on external types (we assume that updates happen externally to C3). Could that explain what you are seeing? If so, then this is simply not supported.

Actually, the store calc is not the problem because easy stored calcs are well done. Of course, being an external type, the refresh is not automatic but needs to be triggered manually. But this is not a problem. I give a “data” example of what I have and what I would like to have. The filtering and ordering from the annotation I should get something like this for id = 1:


|        id        |     endDateRefM1   |    beginDateRefM1   |    endDateRefM2  |      podM2     |
__________ ________________ _________________ _______________ ___________
|         1        |     "9999-12-31"       |      "9999-12-31"       |     "9999-12-31"    |         1A        |
__________| _______________  | ________________ | ______________ | __________|
|         1        |     "9999-12-31"       |     "9999-12-31"        |     "9999-12-31"    |         2A        |
__________| ________________|_________________|_______________|__________ |
|         1        |     "9999-12-31"       |     "9999-12-31"        |     "9999-12-31"    |         2A        |
__________| ________________|_________________|_______________|__________ |

What I expect from numChildren is 2 because we have 2 podM2 (1A e 2A)
My expctation was that podFullPod.podM2 gets a list like [1A,2A,2A] and count(unique()) would give me 2, but probably is not this the case.

@mmichela In your data example, shouldn’t each row have a different id?

No, the data example is for one id (1), In FullPod each id can have more lines, depending on how many podM2 and RefDates it has. So if in type pod I do a fetch over id==1, In field podFullPod I would get something similar to what you see in the example. Then I expect that numChildren does the unique count.

@mmichela If ‘’ isn’t the primary key, then we don’t support that. We require the id field to be the primary key. If it is the primary key then I don’t understand your example and am missing something.