Publishing multiple rows to DB - many to many / one to many relationship

mbuchkovsky · November 14, 2008, 2:42pm

Hi all,

Preamble:
Rx 6.5.2 on Windows
PSOToolkit for 6.x.x installed
DB Publishing

We are trying to publish to several tables and generate multiple rows by publishing one content item - all using single template.
2 scenarios:

Publishing to relationship table of parent_id (single content_id) and child-id’s (multiple content_id’s)
where parent is an Article assembly and children being the images (or other related content) related to this article in slots.
We can’t use child table because the images (events, callouts etc.) may relate to other content. Need to publish this to relationship table as follows
parent_id ------- child_id
1235 ------- 7895
1235 ------- 7896
1235 ------- 7880
We tried using foreach and some slot content gettter PSO functions with no luck.
we have two EditLive controls in the same editor - body and history - need to publish the content from each field to a new row (not column)
so - in primitive view looks as follows:

content_id-------rel_text
1235-------bodytextcontent…
1235-------historytextcontent…

Please advice or point in the right direction on how to implement this without implementing dynapub custom publishing.
The content_id may be converted to string and include suffix as such 1235_b and 1235_h but how do we correlate those to the text ?

Appreciate your help in advance

Mike

dbenua · November 17, 2008, 10:24am

Mike,

First off, you must define the “child rows” in bindings, you cannot use Velocity in database publishing. The $rx.asmhelper.childValues() function assumes that the values are found an a Node, which is not the case if you are attempting to build child rows in from related content.

You need the bindings to be List Objects, which you can build using $user.psoListTool.asList(). For example:

$child[0].parent_id = $user.psoListTools.asList(1235,3)
$child[0].child_id[0] = 7895
$child[0].child_id[1] = 7896
$child[0].child_id[2] = 7880

Assuming your child table has 2 columns named “parent_id” and “child_id”, this will publish 3 rows in that table.

You can use similar tricks for publishing multiple fields into different rows of a child table.

There is one firm limitation of database publishing: you must always have exactly 1 parent row (in exactly one parent table). You can have multiple child tables (each with multiple rows) but each of them must share a foreign key relationship DIRECTLY with the parent table.

If you cannot meet this requirement (e.g. your table structure is too complex, you need multiple rows in the parent table, or you need children of children), then you cannot use database publishing: you’ll wind up with something like Dynapub.

I hope this helps.

Dave

mbuchkovsky · November 17, 2008, 3:56pm

Great - thanks very much Dave!!!

There are still couple of tricky things.
one of them - what if my array is dynamic? - I can’t specify nor restrict the number of items in array as it could be as much as user desire to add to the slot. In the following case (bindings represented as name/value pairs):
#Mon Nov 17 16:22:29 EST 2008

06.name=$allsize
06.value=$user.psoListTools.asList($user.psoSlotTools.getSlotContents($sys.assemblyItem,“bmAllRelatedContent”,$user.params)).size()

07.name=$bmAllRelatedContent
07.value=if ($user.psoListTools.asList($user.psoSlotTools.getSlotContents($sys.assemblyItem,“bmAllRelatedContent”,$user.params)).size() != 0) { $bmAllRelatedContent = $user.psoSlotTools.getSlotContents($sys.assemblyItem, “bmAllRelatedContent”, $user.params); foreach ($ancestorrow in $bmAllRelatedContent) { child[1].parent_id = $ancestorrow.getNode().getProperty(“sys_contentid”).String; } }

08.name=$slotcontent
08.value=$user.psoSlotTools.getSlotContents($sys.assemblyItem,“bmAllRelatedcontent”,$params)

09.name=$slotitem
09.value=$slotcontent[0].node.getProperty(“rx:sys_contentid”).String

10.name=$child[1].parent_id
10.value=$sys.item.getProperty(“rx:sys_contentid”)

11.name=$child[1].child_id
11.value=$user.psoListTools.asList(“rx:sys_contentid”, $allsize)

12.name=$child[1].child_id[0]
12.value=$slotcontent[0].node.getProperty(“rx:sys_contentid”).String

13.name=$child[1].child_id[1]
13.value=$slotcontent[1].node.getProperty(“rx:sys_contentid”).String

How can i have the above in RED to be dynamic (i.e. just defined as value from $sloitem) - I can’t really make a loop here since the bindings are hardcoded.

I’ll try to iterate the results based on the size(), but would appreciate a pointer.

Regards,
Mike

dbenua · November 17, 2008, 5:08pm

Mike,

I cannot tell exactly which parts are red, but you ought to be able to produce a foreach statement (in JEXL) that assembles an array


$related = $user.psoSlotTools.getSlotContents(...);
$allsize = $related.size(); 
$i = 0;
$child[0].parent_id = $user.psoListTools.asList($sys.item.getProperty(...),$allsize); 
foreach($item in $related)
{
   $child[0].child_id[$i] = $item.node.getProperty(...); 
   $i = $i + 1; 
}

I’ve not tested the syntax, but something close to this should work. Use the debug assembler to test it and see that it produces a list of the appropriate size.

mbuchkovsky · November 18, 2008, 2:43am

Thanks for suggestion and your input Dave.

Tried this per your tip:


$related = $user.psoSlotTools.getSlotContents($sys.assemblyItem, "bmAllRelatedContent", $params);

$allsize = $related.size();

$i=0;
 
$child[1].child_id = foreach($item in $related)  {
         $child[1].child_id[$i] = $related[$i].node.getProperty("sys_contentid");
         $i =$i + 1;
}

where $child[1].child_id[$i] suppose to build a custom variable for me with a value set to $item.node.getProperty(“sys_contentid”) of each child in the slot. after that we incrementing our variable $i by 1 and so on …

For some reason for my child_id in debug I get “3” which is the size of my array instead of 3 rows with separate child_id’s. If i remove $i=$i+1; , I get last in the array child_id, but still don’t get three rows, just one.

The code above suppose to do a dynamic build of something like this:


$child[1].child_id[0] = $related[0].node.getProperty(...)
$child[1].child_id[1] = $related[1].node.getProperty(...)
$child[1].child_id[2] = $related[2].node.getProperty(...)

Which if I try adding to my bindings manually works perfect and returns three rows for my child table. But since we may have any number of related items in slot we really have to have our variables in bindings to be set on the fly.

Please let me know if I’m missing something major here, really don’t want to even think about Dynapub (even with Firestorm help)at this point.
The finished product we are trying to get is this:

<childtable name="boost_content_parent_child">
   <row action="r">
      <column name="parent_id">885</column>
      <column name="child_id">1042</column>
   </row>
   <row action="r">
      <column name="parent_id">885</column>
      <column name="child_id">933</column>
   </row>
   <row action="r">
      <column name="parent_id">885</column>
      <column name="child_id">978</column>
   </row>
</childtable>

Where parent_id (885) is our Assembly Parent (Article) and child_id’s(1042,933,978) are our related images in slot.

Regards,
Mike B

dbenua · November 18, 2008, 5:33pm

Mike,

This is very strange. I spent some time playing with it today, and I could not get JEXL to assign a compound variable (e.g. an array or map) inside a script. If I write a script like this:

$x=1;$y[0]=2;$z=3;

The resulting bindings are:
$x=1
$z=3
and $y is nowhere to be found.

I think this is a flaw (I hesitate to call it a “bug”, because the documentation is not clear enough to establish intent) in JEXL, and not something that we (Percussion) can do anything about.

The only suggestion I can make is that you will need a Java function that returns a List of property values from a slot. I don’t know of any such function, but it’s probably not tremendously difficult to write.

Dave

mbuchkovsky · November 19, 2008, 1:42pm

Dave,

Thanks. You are correct - JEXL documentation is so scarce and the whole project looks like a dying off branch.

Would you point me in the right direction to get docs on how to implement custom function (if available), also - where do I get the source code to see how PSO functions implemented and packaged, or if possible to help with the actual function - that one would be very valuable.

I assume that deployment of such function will be similar to PSOToolkit deployment and will find much appreciation in ranks of those using DB Publishing.
Based on some reading in the forums -I believe there were several instances where this functionality was needed, but never a resolution.

Please let me know if “getSlotContents();” could be extended to address the task resolution.

Regards,
Mike

mbuchkovsky · November 25, 2008, 2:27pm

Has anyone reading this had a similar issue and came up with a solution implemented?
Thanks,
Mike

mbuchkovsky · December 5, 2008, 2:38pm

Courtesy of Dave Benua we were able to resolve the issue of building dynamic bindings for multiple-values coming from a slot. Dave developed and added to PSO Toolkit one additional function - getSlotPropertyValues() which in fact works as

user.psoListTools.asList($sys.item.getProperty("rx:myproperty).getValues())

for normal assembly.

The challenge was to get multiple values from each of the items in slot and bind it for incremental db publishing as child items (multiple rows per item). It was possible to do with set array, but did not work with dynamic array.
Long story short IT WORKS NOW!

Giving back sample imlementation:

$db.child[0] = “child_table”
$slotcontent = $user.psoSlotTools.getSlotContents($sys.assemblyItem,"mySlotName",$params);
$child[0].parent_id = $sys.item.getProperty("rx:sys_contentid");
$child[0].child_id  = $user.psoSlotTools.getSlotPropertyValues($slotcontent,"sys_contentid") ;

Once again - thanks Dave for the function and for the Custom JEXL Function Implementation Guide you posted in relation to this.

Regards,
Mike

Shane_Rancourt · December 10, 2009, 12:05pm

I’ve posted another thread about how to determine if the correct toolkit is installed, however, I thought I’d post the error I’m getting when using the above code.

Unexpected exception while assembling one or more items: Problem when evaluating expression “$user.psoSlotTools.getSlotContents($sys.assemblyItem,“cicaPollAnswerSlot”);” for variable “$slotcontent”: Could not find method getSlotContents for object [com.percussion.pso.jexl.PSOSlotTools@1a072c9] and arguments

Here’s my actual binding that is using this method.

$slotcontent = $user.psoSlotTools.getSlotContents($sys.assemblyItem,“cicaPollAnswerSlot”)

So question is it truly not finding the getslotContents or is it because i’m missing the $params (as per mike’s example)?

Shane

mbuchkovsky · December 10, 2009, 1:04pm

This could be a couple of things - as far as I can see from your code

Syntax error:
($sys.assemblyIt em,“cicaPollAnswerSlot”) - check your code

should be: ($sys.assemblyItem,“cicaPollAnswerSlot”, $params);

AND: Missing $params - as this is what you trying to get anyway

You are not using latest PSO Toolkit (missing functions) - easy, download and upgrade your PSO Toolkit

Shane_Rancourt · January 19, 2010, 2:49pm

I’ve used the above psotoolkit code and it seemed to work great. The issue I’ve run into however is with multiple items. The parent table gets filled correctly but the child table does not.

So here’s the scenario:
I have 4 items, each with several child items inserted into slots. During the previewing of the DB template on each item, everything looks good. If I run my DB publish on the individual items (i.e. item one with child items only), it publishes out fine. However, when all items are in public state and I run the DB publishing to publish all, the parent items all publish, but not all the child items are publishing out. I hope that makes sense.

Thoughts anyone??

Shane