DB Publish simple children

Rhythmyx Technical Discussion
1

Need to try to illicit a response or some ideas for my final database publish issue.

Client has a 4-level hierarchical checkboxtree control. In order to make this control do what we wanted we needed to save, for each selected node, any parent nodes. Also, we wanted to allow the save of only one single selection at any level of the hierarchy, again saving each parent node. I did this through the use of triggers on database insert/update.

So imagine I have a content item, contentid 1111, revision 1. The table that saves the checkboxtree selection has a column called codeitem. Then from the selected codeitem I derive values for columns itemlevel1, itemlevel2, itemlevel3, itemlevel4 - here there is potentially more than one row per content item. In another table I save one single row per content item, storing itemlevel1, itemlevel2, itemlevel3, itemlevel4 (generally the row stored is the last one selected in the CBT).

So if I have a hierarchy like:

A1
- B1
- C1
- C2
- C3
-D3
-D4

The multiple row table for my content item looks like this (assuming C1 and D3 have been selected for the content item):

And the single row table looks like this:

So I want to bind each single (itemlevel*) value from the second table to my db publish template as a single value and write these to the database.

And I want to bind each multiple row itemlevel* from the first table to my db publish template and publish these to a child table.

For the single values I’m playing with a $rx.db.get to bind to a single variable but don’t see how to get my contentid and revision into the query.

For the multiple values I’m wondering on the best way forward - adding a sys_addChildInfo exit to the content type to append these fields and then processing them as an array in the template bind (which is proving difficult for me to fathom, especially since as reported in another post the debug mode errors when it tries to display a simple child), or do another $rx.db.get into an array? But the mapper seems to handle proper child fields but not simple children…

Any advice or ideas hugely appreciated!

Andrew

2

Allan,

I’m not sure I follow what you are trying to do, but it sounds complicated.

I’m not sure what sys_addChildInfo and $rx.db.get() have to do with it, but I’m sure you know that sys_addChildInfo is used in Rx 5.x style assemblers (not content types) and $rx.db.get() only works in Rx 6+, so I don’t understand why you would mix them.

Generally speaking, I would store the selected Nodes in the Content Editor (more or less as the Check Box Tree control generates them), and then “flatten out” the table rows that you need for in the Database publishing XML in the Template/Variant for database publishing.

You’ll have to write some sort of a “script” for this. Depending on what version you’re working in (and how comfortable you are with the technologies involved), you can do this in JEXL, Java or XSLT. I don’t know that there’s any particular advantage of one over the others.

Dave

3

Well, if we ignore the checkboxtree stuff completely (perhaps I should have done that) in essence what I have is some data in the database related to a content item, but the data doesn’t exist as a field or fields - rather it’s generated from a selection in another field (but that’s irrelevant).

In one table there’s a one-to-one relationship between the content item and a row that holds some data for it, with that row having a contentid and revisionid identifier.

In the other there’s a one-to-many relationship between the content item and rows that hold some data, with those rows each having a contentid and revisionid identifier.

I wasn’t suggesting I mix those methods. In a content type there is the option to add a sys_casAddChildInfo in the Post-processing properties. I thought I could do this to append my child data and perhaps that would be available to the template, in essence I would fool the bindings into thinking this was a real child field.

If that didn’t work, I was hoping I could do a rx.db.get in the template bindings to append the data, but I would have needed some way of parameterising the query to only look up rows for that content item based on contentid and revision. I was hoping I might be able to wrap that up somehow in an array handler and thus populate the child table.

All this is complicated for me since I’m trying to do this while handling the jump from 5.7 to 6.5 so I apologise if I’m mixing it up a little. What I really want is a 5.7-type assembler where I add child data using a query like “select itemlevel1, itemlevel2, itemlevel3, itemlevel4 from mytable where contentid = rx:sys_contentid and revisionid = rx:sys_revision”. In each of my cases either one row would be returned, or more than one, with the same query.

4

Andrew,

If what you want is a 5.7 style assembler, use sys_addChildInfo on the Assembler resource. The Content Id and Revision should be available to you as “Single HTML Parameters” (in the “selector” of the child query resource).

Note that XML of the child query is returned directly (there is no XSLT stylesheet applied), so if you want to modify the XML, you have to use a “post Exit” on the child query.

This technique works equally in both 5.x and 6.x.

Dave

5

Ok, I managed to create my queries by doing this:

Bind $query1 to "select SITECODEITEM, PROPCODEITEM, TENANTCODEITEM, DEMISECODEITEM from LS_CT_SHARED_SITECODES where CONTENTID = " +
$sys.item.getProperty(“rx:sys_contentid”).String + " AND REVISIONID = " + $sys.item.getProperty(“rx:sys_revision”).String

Bind $query2 to "select SITECODEITEM, PROPCODEITEM, TENANTCODEITEM, DEMISECODEITEM from LS_CT_SHARED_MULTISITECODES where CONTENTID = "

  • $sys.item.getProperty(“rx:sys_contentid”).String + " AND REVISIONID = " +
    $sys.item.getProperty(“rx:sys_revision”).String

and then I did:

Bind $singlesitecodes to $rx.db.get(“RhythmyxData”,$query1) and
Bind $multisitecodes to $rx.db.get(“RhythmyxData”,$query2)

which gives me the following output in debug mode:

$query2 [java.lang.String] select SITECODEITEM, PROPCODEITEM, TENANTCODEITEM, DEMISECODEITEM from LS_CT_SHARED_MULTISITECODES where CONTENTID = 3947 AND REVISIONID = 5

$query1 [java.lang.String] select SITECODEITEM, PROPCODEITEM, TENANTCODEITEM, DEMISECODEITEM from LS_CT_SHARED_SITECODES where CONTENTID = 3947 AND REVISIONID = 5

$multisitecodes [java.util.List]
[{PROPCODEITEM=P4502, TENANTCODEITEM=T4502CLB4, SITECODEITEM=S108, DEMISECODEITEM=D45021}, {PROPCODEITEM=P1256, TENANTCODEITEM=T1256LLY8, SITECODEITEM=S343, DEMISECODEITEM=null}]

$singlesitecodes [java.util.List] []

which is understandable since this content item has multi codes rather than a single.

Which means I have an array to play with?!?!?

6

The documentation on $rx.asmhelper.mapValues(maplist,key) is scarce, missing some information that appears standard on other bind descriptions.

I assume maplist is the $rx.db.get query, what does the key take?

7

Unbelievable, I managed to get my template to produce a set of child information using the following mappings (sorry, image):

Which outputs the following in my DB publish render:

<childtable name=“RETAIL_SINGLESITECODES”/> (Expected this to be empty)
<childtable name=“RETAIL_MULTISITECODES”>

<row action=“r”>
<column name=“DEMISECODE”>D45021</column>
<column name=“PROPERTYCODE”>P4502</column>
<column name=“TENANTCODE”>T4502CLB4</column>
<column name=“SEQ”>1</column>
<column name=“SITECODE”>S108</column>
<column name=“CONTENTID”>3947</column>
</row>
<row action=“r”>
<column name=“DEMISECODE”/>
<column name=“PROPERTYCODE”>P1256</column>
<column name=“TENANTCODE”>T1256LLY8</column>
<column name=“SEQ”>2</column>
<column name=“SITECODE”>S343</column>
<column name=“CONTENTID”>3947</column>
</row>
</childtable>

I’m amazed! Brilliant!!! :smiley:

8

Allan,

The $rx.asmhelper.mapValues() method takes a “List of Map Objects” and returns a List of Values for a specific key in each map.

You’re correct that the “List of Maps” is what is returned by $rx.db.get() (BTW, if you specify a null or empty DataSource, it will use the Rhythmyx repository by default)

In your example, let’s say you specified the key as “TENANTITEMCODE”, you would get back a List of Strings like this:

[ T4502CLB4 , T1256LLY8 ]

Of course, if you specified a Date or a Number in the query, the value object might not be a String, but hopefully you get the idea.

Dave

9

I’m leaving it now, it works perfectly…:wink: