CMDBuild Forum

Please help with CQL understanding or find documentation

Old Versions
#1

Hello.

I continue tries to use CQL in manner of HQL (Hibernate Query Language) to archive transient filtering, like:

  SELECT Node.Name, Node.description FROM Node WHERE company.useType.Name = 'Production'

Is it possible?

It starts from stalled topic http://www.cmdbuild.org/forum/forum-in-english/304195182, I search forum and found 2-3 similar quaestions like: http://www.cmdbuild.org/forum/forum-in-english/430046613?set_language=en&cl=en, but eventually without success.

Is there in user interface GUI to test CQL interactively? I found it only in administrative interface in REFERENCE field configuration.

Meantime my current observations and tries (tried vie REST v2 /cql/) do far:

    

  • 
1) filter: '{"CQL": "from Node"}' // Returns me 140 records - all in Node class active records.
    • 

  • 2) "from Node WHERE 1=2" // Also returns 140 records, no error! Bug?
    • 

  • 3) "from Node WHERE company in (425, 34944)" // 4 records
    • 

  •    "from Node WHERE company.id in (425, 34944)" // 4 records
    • 

  •    "from Node WHERE company in ( /(425, 34944)/ )" // 4 records (syntax )
    • 

  • But:
    • 

  • 

      "from Node WHERE company.id in ( /(select Id from Company )/ )" // 500 Internal error:
    

    • 

  • 

    java.lang.RuntimeException: org.apache.cxf.interceptor.Fault: PreparedStatementCallback; bad SQL grammar [SELECT *, count(*) over() AS _Node__RowsCount FROM ( 
SELECT
"Node#nodeType"."Description" AS "Node#nodeType#Description", "Node#company"."Description" AS "Node#company#Description", "Node"."Code" AS "Node#Code", "Node"."Name" AS "Node#Name", "Node"."Notes" AS "Node#Notes", "Node"."Description" AS "Node#Description", "Node".company AS "Node#company", "Node".key AS "Node#key", "Node"."nodeType" AS "Node#nodeType", "Node"."IdClass"::oid AS "_Node_IdClass", "Node"."Id" AS "_Node_Id", "Node"."User" AS "_Node_User", "Node"."BeginDate" AS "_Node_BeginDate" 
FROM "Node" AS "Node" 
LEFT JOIN "LookUp" AS "Node#nodeType" ON "Node#nodeType"."Id" = "Node"."nodeType"
LEFT JOIN "MonObject" AS "Node#company" ON "Node#company"."Id" = "Node".company 
WHERE "Node".company IN (select Id from Company ) AND "Node"."Status" = ?) AS main
ORDER BY "Node#Description" ASC, "_Node_Id" 
LIMIT 2147483647 
OFFSET 0]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "company" does not exist
  Position: 785
    

    

    • 

  • 4) "SELECT company from Node" - return me 140 Node's beside Company field requested. Also no errors. Bug?
    • 

  • 5) "from Node WHERE company in ( /(425, 34944)/ )"}''' // 4 records
    • 



 


        


        
          

        

           
           
           1 Like
         


         

            
            
          


      

      

        

          

            
              

                
              

          

          

          


          
              
              
          #2
          
        

        

          
I'm sorry, you can not use CQL with the HQL syntax.


 You can find some CQL examples on the CMDBuild Administrator Manual (Appendix - page 111).
CMDBuild Team

        


        

        

           
           
           
         


         

            
            
          


      

      

        

          

            
              

                
              

          

          

          


          
              
              
          #3
          
        

        

          
Sure I have read that. There 2-3 short examples which absolutely do not helpfull to clarify in my case.


 


Is it not possible use even subqueries to accomplish what I want?


 

Previously Tecnoteca wrote:



I'm sorry, you can not use CQL with the HQL syntax.


 You can find some CQL examples on the CMDBuild Administrator Manual (Appendix - page 111).
CMDBuild Team