Ordering of category option names in the resource tables

Dear Devs,

I have run up against a strange issue. We are currently in the process of transforming a lot of data elements from non-dimensional data elements to ones with categories combos. We have previously done a large matching exercise for this. I could not figure out why the names were not matching, as we were pretty scrupulous. But, as it turns out, DHIS2 changes the names when the resource tables are regenerated. The order which is present in the category combo, does not seem to be respected.

This is the result of two SQL queries.

  1. Before running the resource tables.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*(‘FP Site’)

448577;"(20-24, FP Site, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

  1. Immediately after resource table regeneration.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*(‘FP Site’)

448577;"(FP Site, 20-24, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

Note that the category options are the same, but they order is different.

Is this a bug, or are the category names ephemeral?

Best regards,

Jason

Don't know if its just me, but I've always felt the
_categoryoptioncomboname table is itself a bit of a bug. I don't
really understand why the 'name' field isn't just part of the
categoryoption. Which is generated when the categoryoptioncombo is
created. But no doubt there is some legacy rationale why it was done
this way.

The name in itself is an unreliable thing to match against. I think
the only reliable matching would be against the underlying categories
and their options. Of course the name can be useful to unravel
these, but probably "easier" to look at the relations
(categoryoptioncombos_categoryoptions).

···

On 18 February 2013 15:01, Jason Pickering <jason.p.pickering@gmail.com> wrote:

Dear Devs,

I have run up against a strange issue. We are currently in the process of
transforming a lot of data elements from non-dimensional data elements to
ones with categories combos. We have previously done a large matching
exercise for this. I could not figure out why the names were not matching,
as we were pretty scrupulous. But, as it turns out, DHIS2 changes the names
when the resource tables are regenerated. The order which is present in the
category combo, does not seem to be respected.

This is the result of two SQL queries.

1) Before running the resource tables.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*('FP
Site')
448577;"(20-24, FP Site, Male)"
448588;"(FP Site, Female, 25-49)"
448596;"(FP Site, 50+, Female)"
448607;"(FP Site)"
...

2) Immediately after resource table regeneration.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*('FP
Site')
448577;"(FP Site, 20-24, Male)"
448588;"(FP Site, Female, 25-49)"
448596;"(FP Site, 50+, Female)"
448607;"(FP Site)"
...

Note that the category options are the same, but they order is different.

Is this a bug, or are the category names ephemeral?

Best regards,
Jason

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

I guess I would have expected the sort order of the category options and categories to do something, but it appears they do not. Will try and unwind the data model and see if I can get out what I want, but I think in general, the ordering of the categories (within a category combo) and the ordering of the options (within a category) would seem to make sense, otherwise, there is really no need for people to try and order them as they want.

Regards,

Jason

···

On Mon, Feb 18, 2013 at 5:32 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Don’t know if its just me, but I’ve always felt the

_categoryoptioncomboname table is itself a bit of a bug. I don’t

really understand why the ‘name’ field isn’t just part of the

categoryoption. Which is generated when the categoryoptioncombo is

created. But no doubt there is some legacy rationale why it was done

this way.

The name in itself is an unreliable thing to match against. I think

the only reliable matching would be against the underlying categories

and their options. Of course the name can be useful to unravel

these, but probably “easier” to look at the relations

(categoryoptioncombos_categoryoptions).

On 18 February 2013 15:01, Jason Pickering jason.p.pickering@gmail.com wrote:

Dear Devs,

I have run up against a strange issue. We are currently in the process of

transforming a lot of data elements from non-dimensional data elements to

ones with categories combos. We have previously done a large matching

exercise for this. I could not figure out why the names were not matching,

as we were pretty scrupulous. But, as it turns out, DHIS2 changes the names

when the resource tables are regenerated. The order which is present in the

category combo, does not seem to be respected.

This is the result of two SQL queries.

  1. Before running the resource tables.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*('FP

Site’)

448577;"(20-24, FP Site, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

  1. Immediately after resource table regeneration.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*('FP

Site’)

448577;"(FP Site, 20-24, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

Note that the category options are the same, but they order is different.

Is this a bug, or are the category names ephemeral?

Best regards,

Jason


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

The order is used in data entry.

Ola

···

On Mon, Feb 18, 2013 at 5:32 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Don’t know if its just me, but I’ve always felt the

_categoryoptioncomboname table is itself a bit of a bug. I don’t

really understand why the ‘name’ field isn’t just part of the

categoryoption. Which is generated when the categoryoptioncombo is

created. But no doubt there is some legacy rationale why it was done

this way.

The name in itself is an unreliable thing to match against. I think

the only reliable matching would be against the underlying categories

and their options. Of course the name can be useful to unravel

these, but probably “easier” to look at the relations

(categoryoptioncombos_categoryoptions).

On 18 February 2013 15:01, Jason Pickering jason.p.pickering@gmail.com wrote:

Dear Devs,

I have run up against a strange issue. We are currently in the process of

transforming a lot of data elements from non-dimensional data elements to

ones with categories combos. We have previously done a large matching

exercise for this. I could not figure out why the names were not matching,

as we were pretty scrupulous. But, as it turns out, DHIS2 changes the names

when the resource tables are regenerated. The order which is present in the

category combo, does not seem to be respected.

This is the result of two SQL queries.

  1. Before running the resource tables.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*('FP

Site’)

448577;"(20-24, FP Site, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

  1. Immediately after resource table regeneration.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*('FP

Site’)

448577;"(FP Site, 20-24, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

Note that the category options are the same, but they order is different.

Is this a bug, or are the category names ephemeral?

Best regards,

Jason


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Yes, that I know. That is not the issue at hand. The issue is how they actually come out in the resource tables, and whether or not the order name in the resource table should match that of the ordering of 1) Categories and then 2) Category options within the categories.

I was able to reproduce the random ordering in the resource table several times, and considering this may be used in other things (reports), sometimes you may get (Male ,<1) and another time (<1, Male) . Of course this is the same thing to some people., but it seems it is not the same thing to the M&E people who brought my attention to it.

Dunno about this one, but I think the random ordering of them is not the right behavior, as it is unpredictable.

Not really sure if I understand Bob’s comment, but that sure would make it much much easier to attempt to unwind this part of the data model through SQL (if it is even possible).

Regards,

Jason

···

On Tue, Feb 19, 2013 at 2:55 AM, Ola Hodne Titlestad olati@ifi.uio.no wrote:

The order is used in data entry.

Ola


On 18 Feb 2013 15:25, “Jason Pickering” jason.p.pickering@gmail.com wrote:

I guess I would have expected the sort order of the category options and categories to do something, but it appears they do not. Will try and unwind the data model and see if I can get out what I want, but I think in general, the ordering of the categories (within a category combo) and the ordering of the options (within a category) would seem to make sense, otherwise, there is really no need for people to try and order them as they want.

Regards,

Jason


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

On Mon, Feb 18, 2013 at 5:32 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Don’t know if its just me, but I’ve always felt the

_categoryoptioncomboname table is itself a bit of a bug. I don’t

really understand why the ‘name’ field isn’t just part of the

categoryoption. Which is generated when the categoryoptioncombo is

created. But no doubt there is some legacy rationale why it was done

this way.

The name in itself is an unreliable thing to match against. I think

the only reliable matching would be against the underlying categories

and their options. Of course the name can be useful to unravel

these, but probably “easier” to look at the relations

(categoryoptioncombos_categoryoptions).

On 18 February 2013 15:01, Jason Pickering jason.p.pickering@gmail.com wrote:

Dear Devs,

I have run up against a strange issue. We are currently in the process of

transforming a lot of data elements from non-dimensional data elements to

ones with categories combos. We have previously done a large matching

exercise for this. I could not figure out why the names were not matching,

as we were pretty scrupulous. But, as it turns out, DHIS2 changes the names

when the resource tables are regenerated. The order which is present in the

category combo, does not seem to be respected.

This is the result of two SQL queries.

  1. Before running the resource tables.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*('FP

Site’)

448577;"(20-24, FP Site, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

  1. Immediately after resource table regeneration.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname ~*('FP

Site’)

448577;"(FP Site, 20-24, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

Note that the category options are the same, but they order is different.

Is this a bug, or are the category names ephemeral?

Best regards,

Jason


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Reading back through my comment I don't understand it either. If it
helps, when I said "I don't really understand why the 'name' field
isn't just part of the categoryoption" I meant the
categoryoptioncombo, not the categoryoption. I think our model jars a
bit here where the categoryoptioncombo table doesn't have a name
field. I guess because the whole model was geared towards form input,
and there was no need to name the categoryoptioncombo on a form. But
people found it useful in reporting, so it was added as an
afterthought via the _categoryoptioncomboname table. (still don't see
why a name field wasn't just added instead but I wasn't there).

But this doesn't sound like it should be difficult to fix in its
present form. The code for DataElementCategoryOptionCombo.getName()
is used to generate the name for the resource tables. And as far as I
can see this code doesn't take the ordering of categories into
account. In fact the code for equalsOnName() in the same class is
made quite complicated as a result as it explicitly assumes that the
name is effectively an unordered Set of option names.

So what we'd like to see is that for a given categorycombo with a
given sortorder, the getName() method of all categoryoptioncombos
belonging to that categorycombo will always compose the name according
to the sort order of the categorycombo?

(Bit of a mouthful that. And can I have it with chips please.)

Bob

···

On 19 February 2013 05:08, Jason Pickering <jason.p.pickering@gmail.com> wrote:

Yes, that I know. That is not the issue at hand. The issue is how they
actually come out in the resource tables, and whether or not the order name
in the resource table should match that of the ordering of 1) Categories and
then 2) Category options within the categories.

I was able to reproduce the random ordering in the resource table several
times, and considering this may be used in other things (reports), sometimes
you may get (Male ,<1) and another time (<1, Male) . Of course this is the
same thing to some people., but it seems it is not the same thing to the M&E
people who brought my attention to it.

Dunno about this one, but I think the random ordering of them is not the
right behavior, as it is unpredictable.

Not really sure if I understand Bob's comment, but that sure would make it
much much easier to attempt to unwind this part of the data model through
SQL (if it is even possible).

Regards,
Jason

On Tue, Feb 19, 2013 at 2:55 AM, Ola Hodne Titlestad <olati@ifi.uio.no> > wrote:

The order is used in data entry.

Ola
-------

On 18 Feb 2013 15:25, "Jason Pickering" <jason.p.pickering@gmail.com> >> wrote:

I guess I would have expected the sort order of the category options and
categories to do something, but it appears they do not. Will try and unwind
the data model and see if I can get out what I want, but I think in general,
the ordering of the categories (within a category combo) and the ordering of
the options (within a category) would seem to make sense, otherwise, there
is really no need for people to try and order them as they want.

Regards,
Jason

On Mon, Feb 18, 2013 at 5:32 PM, Bob Jolliffe <bobjolliffe@gmail.com> >>> wrote:

Don't know if its just me, but I've always felt the
_categoryoptioncomboname table is itself a bit of a bug. I don't
really understand why the 'name' field isn't just part of the
categoryoption. Which is generated when the categoryoptioncombo is
created. But no doubt there is some legacy rationale why it was done
this way.

The name in itself is an unreliable thing to match against. I think
the only reliable matching would be against the underlying categories
and their options. Of course the name can be useful to unravel
these, but probably "easier" to look at the relations
(categoryoptioncombos_categoryoptions).

On 18 February 2013 15:01, Jason Pickering <jason.p.pickering@gmail.com> >>>> wrote:
> Dear Devs,
>
> I have run up against a strange issue. We are currently in the process
> of
> transforming a lot of data elements from non-dimensional data elements
> to
> ones with categories combos. We have previously done a large matching
> exercise for this. I could not figure out why the names were not
> matching,
> as we were pretty scrupulous. But, as it turns out, DHIS2 changes the
> names
> when the resource tables are regenerated. The order which is present
> in the
> category combo, does not seem to be respected.
>
> This is the result of two SQL queries.
>
> 1) Before running the resource tables.
>
> SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname
> ~*('FP
> Site')
> 448577;"(20-24, FP Site, Male)"
> 448588;"(FP Site, Female, 25-49)"
> 448596;"(FP Site, 50+, Female)"
> 448607;"(FP Site)"
> ...
>
> 2) Immediately after resource table regeneration.
>
> SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname
> ~*('FP
> Site')
> 448577;"(FP Site, 20-24, Male)"
> 448588;"(FP Site, Female, 25-49)"
> 448596;"(FP Site, 50+, Female)"
> 448607;"(FP Site)"
> ...
>
> Note that the category options are the same, but they order is
> different.
>
> Is this a bug, or are the category names ephemeral?
>
> Best regards,
> Jason
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to : dhis2-devs@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help : https://help.launchpad.net/ListHelp
>

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

Hi Bob,

I am still not sure I get what you mean about the category option combo name. What would be the point of defining it? I though the whole purpose was to ease this process, so we did not have to define a name for each and every category option combo?

Otherwise, we could go back to 1.4 style default data elements, and simply put the name there. I guess I may still be missing your point, but the relevant observation from Nigeria is below.

I think what you suggest is what we would like to see.

For the M&E people,

(Male, <1 year) and (<1 year, Male) does not necessarily seem to be how they want to see it.

So, ordering of the categories within the combo, and then the options in the categories should be respected when it comes to naming them. So if the order in the cat combo is (Gender, Age) then the gender should always come before age in the cat option combo.

I tried but failed to unwind this to reconstruct the names through SQL, so may have to have a look in that class to see how it is done. Does not really matter though, as there is no way to predict which order the names will come out anyway currently, if we want to attempt to match something on them.

Regards,

Jason

···

On Tue, Feb 19, 2013 at 1:23 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Reading back through my comment I don’t understand it either. If it

helps, when I said "I don’t really understand why the ‘name’ field

isn’t just part of the categoryoption" I meant the

categoryoptioncombo, not the categoryoption. I think our model jars a

bit here where the categoryoptioncombo table doesn’t have a name

field. I guess because the whole model was geared towards form input,

and there was no need to name the categoryoptioncombo on a form. But

people found it useful in reporting, so it was added as an

afterthought via the _categoryoptioncomboname table. (still don’t see

why a name field wasn’t just added instead but I wasn’t there).

But this doesn’t sound like it should be difficult to fix in its

present form. The code for DataElementCategoryOptionCombo.getName()

is used to generate the name for the resource tables. And as far as I

can see this code doesn’t take the ordering of categories into

account. In fact the code for equalsOnName() in the same class is

made quite complicated as a result as it explicitly assumes that the

name is effectively an unordered Set of option names.

So what we’d like to see is that for a given categorycombo with a

given sortorder, the getName() method of all categoryoptioncombos

belonging to that categorycombo will always compose the name according

to the sort order of the categorycombo?

(Bit of a mouthful that. And can I have it with chips please.)

Bob

On 19 February 2013 05:08, Jason Pickering jason.p.pickering@gmail.com wrote:

Yes, that I know. That is not the issue at hand. The issue is how they

actually come out in the resource tables, and whether or not the order name

in the resource table should match that of the ordering of 1) Categories and

then 2) Category options within the categories.

I was able to reproduce the random ordering in the resource table several

times, and considering this may be used in other things (reports), sometimes

you may get (Male ,<1) and another time (<1, Male) . Of course this is the

same thing to some people., but it seems it is not the same thing to the M&E

people who brought my attention to it.

Dunno about this one, but I think the random ordering of them is not the

right behavior, as it is unpredictable.

Not really sure if I understand Bob’s comment, but that sure would make it

much much easier to attempt to unwind this part of the data model through

SQL (if it is even possible).

Regards,

Jason

On Tue, Feb 19, 2013 at 2:55 AM, Ola Hodne Titlestad olati@ifi.uio.no

wrote:

The order is used in data entry.

Ola


On 18 Feb 2013 15:25, “Jason Pickering” jason.p.pickering@gmail.com

wrote:

I guess I would have expected the sort order of the category options and

categories to do something, but it appears they do not. Will try and unwind

the data model and see if I can get out what I want, but I think in general,

the ordering of the categories (within a category combo) and the ordering of

the options (within a category) would seem to make sense, otherwise, there

is really no need for people to try and order them as they want.

Regards,

Jason

On Mon, Feb 18, 2013 at 5:32 PM, Bob Jolliffe bobjolliffe@gmail.com

wrote:

Don’t know if its just me, but I’ve always felt the

_categoryoptioncomboname table is itself a bit of a bug. I don’t

really understand why the ‘name’ field isn’t just part of the

categoryoption. Which is generated when the categoryoptioncombo is

created. But no doubt there is some legacy rationale why it was done

this way.

The name in itself is an unreliable thing to match against. I think

the only reliable matching would be against the underlying categories

and their options. Of course the name can be useful to unravel

these, but probably “easier” to look at the relations

(categoryoptioncombos_categoryoptions).

On 18 February 2013 15:01, Jason Pickering jason.p.pickering@gmail.com

wrote:

Dear Devs,

I have run up against a strange issue. We are currently in the process

of

transforming a lot of data elements from non-dimensional data elements

to

ones with categories combos. We have previously done a large matching

exercise for this. I could not figure out why the names were not

matching,

as we were pretty scrupulous. But, as it turns out, DHIS2 changes the

names

when the resource tables are regenerated. The order which is present

in the

category combo, does not seem to be respected.

This is the result of two SQL queries.

  1. Before running the resource tables.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname

~*('FP

Site’)

448577;"(20-24, FP Site, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

  1. Immediately after resource table regeneration.

SELECT * FROM _categoryoptioncomboname where categoryoptioncomboname

~*('FP

Site’)

448577;"(FP Site, 20-24, Male)"

448588;"(FP Site, Female, 25-49)"

448596;"(FP Site, 50+, Female)"

448607;"(FP Site)"

Note that the category options are the same, but they order is

different.

Is this a bug, or are the category names ephemeral?

Best regards,

Jason


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Hi Bob,

I am still not sure I get what you mean about the category option combo
name. What would be the point of defining it? I though the whole purpose was
to ease this process, so we did not have to define a name for each and every
category option combo?

My point was about the table structures. The designers obviously
thought as you are thinking now. No need for a persisted name. Its
just going to be auto-generated from the categoryoptions anyway
(albeit currently in random order).

BUT ...

It was later discovered, probably when making pivot table views for
Excel would be my guess, that in fact a persisted name is quite handy
- many times easier than doing the sql equivalent of what is is done
in java to produce "(Male,<1)" (or "(<1,Male)" ). So we created the
_catoptcomboname table. That's the bit I never understood ie. why not
just add a name field to the catoptcombo table and be done? And
populate that (automatically) at the time that the catoptcombo is
being created/persisted. Creating a whole new resource table just for
this doesn't make much sense. If we don't want to store redundant
stuff on the object, maybe adding name to the _categorystructure table
would have sufficed. Anyway that is all aesthetics and a bit removed
from your actual problem.

Otherwise, we could go back to 1.4 style default data elements, and simply
put the name there. I guess I may still be missing your point, but the
relevant observation from Nigeria is below.

I think what you suggest is what we would like to see.

For the M&E people,

(Male, <1 year) and (<1 year, Male) does not necessarily seem to be how they
want to see it.

So, ordering of the categories within the combo, and then the options in the
categories should be respected when it comes to naming them. So if the order
in the cat combo is (Gender, Age) then the gender should always come before
age in the cat option combo.

Agree.

I tried but failed to unwind this to reconstruct the names through SQL, so
may have to have a look in that class to see how it is done. Does not really
matter though, as there is no way to predict which order the names will come
out anyway currently, if we want to attempt to match something on them.

Currently the class just concatenates the relevant catopts together in
whatever order they come out. And is conceivable they can come out
differently. Particularly if you done something structurally
significant which *might* reverse the order, like perhaps exporting
and importing into a new database. I think this bit could be fixed
easy enough to make it deterministic based on the category sort order.
I'll check later.

Though you also have another requirement which is similar sounding but
not really related to this one ... you also want a sort order on the
categoryoptioncombos (autogenerated from the underlying sort orders).
I can see the sense in that, but also suspecting its a bit more work
than the other thing related just to the order within the names. In
fact this one does strike me as maybe a good candidate for something
like the _categorystructure table.

Bob

···

On 19 February 2013 19:54, Jason Pickering <jason.p.pickering@gmail.com> wrote:

Regards,
Jason

On Tue, Feb 19, 2013 at 1:23 PM, Bob Jolliffe <bobjolliffe@gmail.com> wrote:

Reading back through my comment I don't understand it either. If it
helps, when I said "I don't really understand why the 'name' field
isn't just part of the categoryoption" I meant the
categoryoptioncombo, not the categoryoption. I think our model jars a
bit here where the categoryoptioncombo table doesn't have a name
field. I guess because the whole model was geared towards form input,
and there was no need to name the categoryoptioncombo on a form. But
people found it useful in reporting, so it was added as an
afterthought via the _categoryoptioncomboname table. (still don't see
why a name field wasn't just added instead but I wasn't there).

But this doesn't sound like it should be difficult to fix in its
present form. The code for DataElementCategoryOptionCombo.getName()
is used to generate the name for the resource tables. And as far as I
can see this code doesn't take the ordering of categories into
account. In fact the code for equalsOnName() in the same class is
made quite complicated as a result as it explicitly assumes that the
name is effectively an unordered Set of option names.

So what we'd like to see is that for a given categorycombo with a
given sortorder, the getName() method of all categoryoptioncombos
belonging to that categorycombo will always compose the name according
to the sort order of the categorycombo?

(Bit of a mouthful that. And can I have it with chips please.)

Bob

On 19 February 2013 05:08, Jason Pickering <jason.p.pickering@gmail.com> >> wrote:
> Yes, that I know. That is not the issue at hand. The issue is how they
> actually come out in the resource tables, and whether or not the order
> name
> in the resource table should match that of the ordering of 1) Categories
> and
> then 2) Category options within the categories.
>
> I was able to reproduce the random ordering in the resource table
> several
> times, and considering this may be used in other things (reports),
> sometimes
> you may get (Male ,<1) and another time (<1, Male) . Of course this is
> the
> same thing to some people., but it seems it is not the same thing to the
> M&E
> people who brought my attention to it.
>
> Dunno about this one, but I think the random ordering of them is not the
> right behavior, as it is unpredictable.
>
> Not really sure if I understand Bob's comment, but that sure would make
> it
> much much easier to attempt to unwind this part of the data model
> through
> SQL (if it is even possible).
>
> Regards,
> Jason
>
>
>
> On Tue, Feb 19, 2013 at 2:55 AM, Ola Hodne Titlestad <olati@ifi.uio.no> >> > wrote:
>>
>> The order is used in data entry.
>>
>> Ola
>> -------
>>
>> On 18 Feb 2013 15:25, "Jason Pickering" <jason.p.pickering@gmail.com> >> >> wrote:
>>>
>>> I guess I would have expected the sort order of the category options
>>> and
>>> categories to do something, but it appears they do not. Will try and
>>> unwind
>>> the data model and see if I can get out what I want, but I think in
>>> general,
>>> the ordering of the categories (within a category combo) and the
>>> ordering of
>>> the options (within a category) would seem to make sense, otherwise,
>>> there
>>> is really no need for people to try and order them as they want.
>>>
>>> Regards,
>>> Jason
>>>
>>>
>>>
>>> On Mon, Feb 18, 2013 at 5:32 PM, Bob Jolliffe <bobjolliffe@gmail.com> >> >>> wrote:
>>>>
>>>> Don't know if its just me, but I've always felt the
>>>> _categoryoptioncomboname table is itself a bit of a bug. I don't
>>>> really understand why the 'name' field isn't just part of the
>>>> categoryoption. Which is generated when the categoryoptioncombo is
>>>> created. But no doubt there is some legacy rationale why it was done
>>>> this way.
>>>>
>>>> The name in itself is an unreliable thing to match against. I think
>>>> the only reliable matching would be against the underlying categories
>>>> and their options. Of course the name can be useful to unravel
>>>> these, but probably "easier" to look at the relations
>>>> (categoryoptioncombos_categoryoptions).
>>>>
>>>> On 18 February 2013 15:01, Jason Pickering >> >>>> <jason.p.pickering@gmail.com> >> >>>> wrote:
>>>> > Dear Devs,
>>>> >
>>>> > I have run up against a strange issue. We are currently in the
>>>> > process
>>>> > of
>>>> > transforming a lot of data elements from non-dimensional data
>>>> > elements
>>>> > to
>>>> > ones with categories combos. We have previously done a large
>>>> > matching
>>>> > exercise for this. I could not figure out why the names were not
>>>> > matching,
>>>> > as we were pretty scrupulous. But, as it turns out, DHIS2 changes
>>>> > the
>>>> > names
>>>> > when the resource tables are regenerated. The order which is
>>>> > present
>>>> > in the
>>>> > category combo, does not seem to be respected.
>>>> >
>>>> > This is the result of two SQL queries.
>>>> >
>>>> > 1) Before running the resource tables.
>>>> >
>>>> > SELECT * FROM _categoryoptioncomboname where
>>>> > categoryoptioncomboname
>>>> > ~*('FP
>>>> > Site')
>>>> > 448577;"(20-24, FP Site, Male)"
>>>> > 448588;"(FP Site, Female, 25-49)"
>>>> > 448596;"(FP Site, 50+, Female)"
>>>> > 448607;"(FP Site)"
>>>> > ...
>>>> >
>>>> > 2) Immediately after resource table regeneration.
>>>> >
>>>> > SELECT * FROM _categoryoptioncomboname where
>>>> > categoryoptioncomboname
>>>> > ~*('FP
>>>> > Site')
>>>> > 448577;"(FP Site, 20-24, Male)"
>>>> > 448588;"(FP Site, Female, 25-49)"
>>>> > 448596;"(FP Site, 50+, Female)"
>>>> > 448607;"(FP Site)"
>>>> > ...
>>>> >
>>>> > Note that the category options are the same, but they order is
>>>> > different.
>>>> >
>>>> > Is this a bug, or are the category names ephemeral?
>>>> >
>>>> > Best regards,
>>>> > Jason
>>>> >
>>>> >
>>>> >
>>>> > _______________________________________________
>>>> > Mailing list: https://launchpad.net/~dhis2-devs
>>>> > Post to : dhis2-devs@lists.launchpad.net
>>>> > Unsubscribe : https://launchpad.net/~dhis2-devs
>>>> > More help : https://help.launchpad.net/ListHelp
>>>> >
>>>
>>>
>>>
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~dhis2-devs
>>> Post to : dhis2-devs@lists.launchpad.net
>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>> More help : https://help.launchpad.net/ListHelp
>>>
>

Hi,

sorry for the late reply.

It was later discovered, probably when making pivot table views for

Excel would be my guess, that in fact a persisted name is quite handy

  • many times easier than doing the sql equivalent of what is is done

in java to produce “(Male,<1)” (or “(<1,Male)” ). So we created the

_catoptcomboname table.

This is right. The names when used inside the application are generated on the fly based on the category options and category combination. When creating SQL reports, however, it is tricky to generate this (for anyone except Jason that is;), so we decided to have a resource table for it. The reason for not putting it on the categoryoptioncombo table is that this table is generated by Hibernate and is part of the domain model - it is by many people including me considered bad practice to tamper with entity database tables. Having a separate table is cleaner and not really a problem. One might also want to expand this with more information later.

The reason for the sort order flaw is a bit boring. The association between category option and option combination is bi-directional. Category option - > option combination is represented as a Java List, which is an ordered collection. Option combination -> category option is represented as a Java Set, which is an unordered collection. The reason for this is that maintaining a bidirectional association with Lists in both ends does not make sense - you might get conflicts when you have overlapping objects.

So for the resource table I can see we use the Set association from option combination -> category option to render the names. We should ideally use the association from the other end to maintain sort order. Will see if we can fix this shortly.

regards,

Lars

Hi,

sorry for the late reply.

It was later discovered, probably when making pivot table views for

Excel would be my guess, that in fact a persisted name is quite handy

  • many times easier than doing the sql equivalent of what is is done

in java to produce “(Male,<1)” (or “(<1,Male)” ). So we created the

_catoptcomboname table.

This is right. The names when used inside the application are generated on the fly based on the category options and category combination. When creating SQL reports, however, it is tricky to generate this (for anyone except Jason that is;), so we decided to have a resource table for it. The reason for not putting it on the categoryoptioncombo table is that this table is generated by Hibernate and is part of the domain model - it is by many people including me considered bad practice to tamper with entity database tables. Having a separate table is cleaner and not really a problem. One might also want to expand this with more information later.

My suggestion was that the name should be part of the model, not that we just add columns to the entity tables, which I agree would be bad. The categoryoptioncombo name is part of the java implementation of the model. In fact it currently even extends BaseNameableObject. And there is a working assumption in the system that these things have names. Everything else in our model with a name has a field added to the model rather than a separate table. So it would have been good practice (I think) to maintain the pattern.

Still this is far from the most pressing of our concerns …

···

On 20 February 2013 07:59, Lars Helge Øverland larshelge@gmail.com wrote:

The reason for the sort order flaw is a bit boring. The association between category option and option combination is bi-directional. Category option - > option combination is represented as a Java List, which is an ordered collection. Option combination -> category option is represented as a Java Set, which is an unordered collection. The reason for this is that maintaining a bidirectional association with Lists in both ends does not make sense - you might get conflicts when you have overlapping objects.

So for the resource table I can see we use the Set association from option combination -> category option to render the names. We should ideally use the association from the other end to maintain sort order. Will see if we can fix this shortly.

regards,

Lars

This is right. The names when used inside the application are generated on the fly based on the category options and category combination. When creating SQL reports, however, it is tricky to generate this (for anyone except Jason that is;), so we decided to have a resource table for it. The reason for not putting it on the categoryoptioncombo table is that this table is generated by Hibernate and is part of the domain model - it is by many people including me considered bad practice to tamper with entity database tables. Having a separate table is cleaner and not really a problem. One might also want to expand this with more information later.

My suggestion was that the name should be part of the model, not that we just add columns to the entity tables, which I agree would be bad. The categoryoptioncombo name is part of the java implementation of the model. In fact it currently even extends BaseNameableObject. And there is a working assumption in the system that these things have names. Everything else in our model with a name has a field added to the model rather than a separate table. So it would have been good practice (I think) to maintain the pattern.

Yup agree that could have some benefits. But its different in the way that we would never set the name explicitly like we do with all other objects. So since we need to auto-generate the name anyway there is not much point in persisting it… Anyway like you say, not the most pressing issue.

Hi Jason,

this issue has now been fixed in trunk.

Lars