For loop in DB bindings

Rhythmyx Technical Discussion
1

I 'm trying to get the individual records from the resultset i got from a db query

$sql = select * from PSX_OBJECTRELATIONSHIP t where t.dependent_id=104942 and t.slot_id is not null and owner_revision = (select max(owner_revision) from PSX_OBJECTRELATIONSHIP where dependent_id=104942 and slot_id is not null)

The above query returns more than one row and i have used the below for loop to g

$relation_table = $rx.db.get("", $sql)
$rows = $relation_table.size()
if($rows > 1){i=0;$rows=$rows-1;foreach($i in $rows ) { $id = $relation_table[i].get(“OWNER_ID”); $id;} $id;}

This is throwing error as below…
Error reported
Unexpected exception while assembling one or more items: Problem when evaluating expression “if($rows > 1){i=0;$rows=$rows-1;foreach($i in $rows ) { $id = $relation_table[i].get(“OWNER_ID”); $id;} $id;};” for variable “$row.CAR_TYPE”: Could not find iterator for object [1]

Any idea how to implement for loop in DB bindings. I 'am trying to get the the parent Id’s(if there are more than one) associated for this slot. For a single parent id it’s works perfect.

Any help is much appreciated.

Paddy

2

Hi Paddy,

Firstly, should that $realtion_table[i] be [$i]?
You could probably just make the foreach ($row in $relation_table) {$row.get(“OWNER_ID”);} to shorten your code.

The other problem would seem to be that you’re then setting the multiple variables returned from your loop as just one binding.

In what form are you wanting the multiple results to get published to your database? Some kind of array/list? Separate rows?

3

Thank you Junwin for your reply. foreach loop worked without any errors. But it does return the anticipated results…

I will explain my requirement with a bit more detail…

I have a sql statement which returns 0 to many results(rows) and sql statement is below.

select t.OWNER_ID, t.SLOT_ID from PSX_OBJECTRELATIONSHIP t where t.dependent_id=104942 and t.slot_id is not null and owner_revision = (select max(owner_revision) from PSX_OBJECTRELATIONSHIP where dependent_id=104942 and slot_id is not null)

My destination database table schema looks like below with composite primary key(CONTENT_ID and OWNER_ID)

CONTENT_ID NUMBER(10) Not Nullable
PARENT_CONTENT_ID NUMBER(10) Not Nullable
COLUMN NUMBER INTEGER Nullable

I’m not able to upload a snapshot image of the schema and trying to give details as much as i can.

If i run the above query, my results from sql query would be

DEPENDENT_ID OWNER_ID SLOT_ID
104942 102056 620
104942 102059 630

I can insert the DEPENDENT_ID and OWNER_ID as is but i need to parse the SLOT_ID values from 620 to “Column1” and 630 to “Column2”

If everything goes well, the destination table should be as below

CONTENT_ID PARENT_CONTENT_ID COLUMN_NUMBER
104942 102056 Column1
104942 102059 Column2

What is the best way to implement this… Any recommendations?

4

It looks like using $db.child[0] bindings might be the way to go here if you’re trying to publish to multiple rows from the one template. Have a look at page 382-385 of the 6.7 Implementation Guide and do something like the $child[0].SEQ example for your OWNER_ID.

5

My binding should look something like this…

Binding variable is $child[0].OWNER_ID and corresponding value or JEXL expression should be $rx.asmhelper.mapValues($relation_table,“OWNER_ID”)

Am i correct?

6

I think you’re just going to have to experiment with that a bit and see what’s getting published. That certainly looks along the right lines at least.

7

I tried to implement in the child table way, and my destination tables are CONTENT_REPORTING(primary table with CONTENT_ID as primary key) and CONTENT_RELATIONSHIP(child table with CONTENT_ID and PARENT_CONTENT_ID as keys).

If i don’t have any associated parent_content_id for a content_id(from the query), the child table should be having the content_id as parent_content_id also along with content_id in the child table.
I have the follwing binding variable for it

$sys_contentid ---->$sys.item.getProperty(“rx:sys_contentid”).string
$relation_table —> $rx.db.get("", $sql_end.concat($sys_contentid).concat(" and SLOT_ID is not null)"))
$resultsize —>$relation_table.size()
$child[0].CONTENT_ID --> $sys.item.getProperty(“rx:sys_contentid”)
$child[0].PARENT_CONTENT_ID --> $rx.cond.choose( $resultsize == 0 , $rx.string.extractNumber($sys_contentid), $rx.asmhelper.mapValues($relation_table,“OWNER_ID”))

$resultsize is returning 0 if no parent id is associated to a content_id, which technically satisfies the conditional choose above.

Any help is much appreciated?