Query using 2 classes

Help / Discuss
1

Hi!

How to write xwql query to two classes?
I write to one and it works:

select soft.softType, doc.title, soft.softVersion, soft.softLicense, soft.softAdmins, soft.softComment from Document as doc, doc.object(SoftwareInventory.Code.SoftwareInventoryClass) as soft where soft.softID > 1 and doc.fullName not like '%Template'

If I add a second class - the result is empty:

select soft.softType, doc.title, soft.softVersion, soft.softLicense, soft.softAdmins, soft.softComment, hdw.hdwSoftware from Document as doc, doc.object(SoftwareInventory.Code.SoftwareInventoryClass) as soft, doc.object(HardwareInventory.Code.HardwareInventoryClass) as hdw where soft.softID > 1 and hdw.hdwID > 1 and doc.fullName not like '%Template'

Where is my mistake?

I read these articles, but need more detailed help.
After a good old classical SQL, HQL / HQWL twists my brain :frowning:

Thanks.

2

Hello!
The first thing I didn’t see in both your queries was the connection between the tables, like this:

select … from t1, t2 where t1.id = t2.id and …

3

And I got it! You’re using a join between two objects that are on the same document.

And if you remove the restriction and hdw.hdwID > 1 and see what values the property hdw.hdwID takes in the select statement.

4

Hi!

Thanks for attention.

The first thing I didn’t see in both your queries was the connection between the tables

This is not necessary (although it is wrong for large tables - we will get cross join), we can do the terms in WHERE clause.
Here is an example from documentation:

select
  att.filename
from
  XWikiAttachment att,
  XWikiDocument doc
where
  doc.fullName='Main.WebHome'
  and att.docId=doc.id

And I got it! You’re using a join between two objects that are on the same document.

It’s a different documents.

And if you remove the restriction and hdw.hdwID > 1 and see what values the property hdw.hdwID takes in the select statement.

Unfortunately, it doesn’t work too.

XWQL and HQL in XWiki are slightly more unusual than classic SQL.

5

Why? As I understand it: you run through the list of documents, on each document you refer to two different objects of this document, check the properties of these objects?

An example of a request to join two different documents:

Make a join between two objects that are not on the same document and are bound by a property:
from doc.object(‘Space.Class’) as obj, Document doc1, doc1.object(‘Space.Class1’) obj1 where obj.prop1 = doc1.fullName and obj1.prop2 like ‘%text%’ order by obj1.prop3

According to the example:

Query on 2 XObjects: where doc.object(XWiki.XWikiUsers).email like ‘%xwiki.com’ and doc.object(XWiki.ArticleClass).content like ‘%ludovic%’

I would try to perform a test query:
select
soft.softType, doc.title, soft.softVersion, soft.softLicense, soft.softAdmins, soft.softComment
from
Document as doc
where
doc.object(SoftwareInventory.Code.SoftwareInventoryClass).softID > 1
and doc.object(HardwareInventory.Code.HardwareInventoryClass).hdwID > 1
and …