Auto Indexes and Child Tables

Rhythmyx Technical Discussion
1

How can I reference a child table in a JSR-170 Auto Index Query?

2

The JCR Query support does not allow Child Tables in the where clause, so your only option for auto index queries is to use raw SQL.

This can be done in 2 different approaches:

  1. Build an XML Query Resource and use the sys_LegacyAutoSlotContentFinder. This the way we did it in 5.x, and the old manuals should show you how to set up the query.

  2. Use the PSOSqlContentFinder from the PSO Toolkit.

3

Thanks for this.

4

I am needing to get the contents of a child table value, however I cannot find the sys_LegacyAutoSlotContentFinder documentation in the 5.7 reference manuals as suggested in the previous response. I do see the slot referenced in the 6.5.2 documentation but not an example of how to use it, can someone post a sample of the sys_LegacyAutoSlotContentFinder in action?

5

Content Finders were added in 6.0, so you won’t find them in 5.7.

In 5.7, you have to make a XML application that returns the correct XML. The LegacyAutoSlot content finder is intended for customers who have these applications and wish to use them with Velocity templates.

Dave

6

I should also add that the PSO Toolkit has a SQL Slot Content Finder, which allows you to execute an arbitrary SQL query without the need for the XML Application. If you don’t already have an XML application, it may be simpler to use.

Dave

7

I was trying to get around installing the PSO toolkit if at all possible but it is looking like that is the only way to return a child table field value.

I thought I might be able to get the value by using the $rx.db.get statement but that isn’t working because:

  1. I am getting an error:

Lexical error: org.apache.velocity.runtime.parser.TokenMgrError

  1. I don’t see how I can relate the SQL query in the WHERE clause to the parent item. I would want to say: SELECT items FROM the child table WHERE parent id = unique id…
#set($myChildren = $rx.db.get("RhythmyxData","select site_name FROM CT_ERAUCENTER_TEACHING_SITE))
8

First off, the PSOToolkit is not dangerous, and you should not be afraid of it. (After all, I wrote most of it). Most of the routines are things we found necessary (or convenient) during customer implementations, so they tend to be oriented towards real world problems.

As for the join, both your SQL Table (main and child) will have CONTENTID and REVISIONID columns on them. You have join on both columns:

SELECT MAIN.* FROM MAIN, CHILD WHERE MAIN.CONTENTID = CHILD.CONTENTID AND MAIN.REVISIONID = CHILD.REVISIONID …

You’ll need some other conditions, but that’s a good starting point. You may need, depending on the conditions to add the DISTINCT keyword, (e.g. SELECT DISTINCT) or to do a coordinated subquery, but that’s beyond where we can safely go in a forum post :slight_smile:

Use a SQL Tool (Oracle SQL Developer, the SQL Query Manager, etc) to get the statement right before you start developing anything in Rhythmyx.

Dave

9

Dave,

Would you be able to send me the PSO Toolkit or is there a way I can download it?

10

Tech Support says you can ask them for the PSO Toolkit.