Hi all,
I’m hoping to get some advice on a more efficient way to retrieve all the properties of custom objects attached to a page.
My problem is that each call to getProperty(‘xx’).getValue() appears to generate a separate database query. That’s fine for a small number of objects, but on a page with, say, 100 custom objects, each with 10 properties, that generates 1000 queries. Under heavy load, I’m finding that causes major bottlenecks with e.g. database connections and often crashes the site.
Currently, I do something like this:
#set($objs = $doc.getObjects('XWiki.CustomClass'))
#foreach($obj in $objs)
#set($prop = $obj.getProperty('CustomProp1').getValue())
#set($prop = $obj.getProperty('CustomProp2').getValue())
#end
I thought I could use HQL to get all the data in a single query. The issue I’m running into is that while the class definition may have 10 custom properties, each individual object may only have stored values for some.
So, if I write the query to return one property per row, there’s no easy way to iterate by object, because the number of returned rows corresponding to each object isn’t fixed.
In standard SQL, this is easy with a left join, but HQL doesn’t appear to support left joins. So a query like this results in excluding all objects that don’t have a value for, say, CustomProp2:
select
prop1.value,
prop2.value
from
XWikiDocument doc,
BaseObject obj,
StringProperty prop1,
StringProperty prop2
where
doc.fullName=obj.name
and obj.className='XWiki.CustomClass'
and (prop1.id.id=obj.id and prop1.name = 'CustomProp1')
and (prop2.id.id=obj.id and prop2.name = 'CustomProp2')
I’ve read through all the HQL query examples in the docs, but don’t see anything matching my use case. I also don’t think I can use the REST API for this, because it has the same problem - each call to /objects/x/properties would need a separate HTTP request and generate a separate database query.
Does anyone know an easy way to get all the properties for all objects on a page in an easily iterable form in a single query?
Any help is greatly appreciated.