Jahia Dev Forum > where clause fails...

0 (0 Good)
0 (0 Bad)

where clause fails with JCR-SQL2

by  fabu »  2013/03/11 15:44

I have some troubles with JCR-SQL2, when I want to retrieve simple objects with 
 
SELECT * FROM [nnt:article] as article where article.[j:nodename] = ?
 
No result found.
 
If I try SELECT * FROM [nnt:article] as article
 
There is no problem.
 
It's happening with all objects and any property, where clause fails except with "is [not] null"
 
Any idea?
 

Thanks in advance.

BR

  (fabu)

Number of messages  28
Registration date
0 (0 Good)
0 (0 Bad)

Re: where clause fails with JCR-SQL2

by  rvt »  2013/03/11 15:54

Hey,

I just tried this (SELECT * FROM [jnt:user] as users WHERE users.[j:nodename] = 'root') and it worked fine, can yuo be more specific what you are trying to do, and/or show some code?

Ries

  • Re: where clause fails with JCR-SQL2
    2013/03/11 15:54

    rvt <p> Hey,</p> <p> I just tried this (SELECT * FROM [jnt:user] as users WHERE users.[j:nodename] = &#39;root&#39;) and it worked fine, can yuo be more specific what you are trying to do, and/or show some code?</p> <p> Ries</p>

  • Number of messages  185
    Registration date Mar 11, 2013
    0 (0 Good)
    0 (0 Bad)

    Re : Re: where clause fails with JCR-SQL2

    by  fabu »  2013/03/11 16:06

    It works for me too,

    I declared a new object:

    [nnt:actualite] > jnt:content, mix:title, nmix:myComponent

     - id (long) hidden indexed=no
     - text (string) indexed=no
     
    into a list 
     
    [nnt:actualites] > jnt:content, jmix:list , jmix:orderedList, nmix:myComponent
     orderable
     + * (nnt:actualite) = nnt:actualite version

     

    And I had some actualite into actualites

    Then I try to retrieve them with

    SELECT * FROM [nnt:acutalite] as item where item.[id] = '1363007526111'

    Finally there is no result.

    If I try with SELECT * FROM [nnt:acutalite] as item, I retrieve all of them!

     

      (fabu)

    Number of messages  28
    Registration date
    0 (0 Good)
    0 (0 Bad)

    Re: where clause fails with JCR-SQL2

    by  shyrkov »  2013/03/11 21:08

    Hello,

    I am not sure, I understood, if you managed to do a query bythe node name?

    Just in case here is an example of a query for nnt:article nodes, filtered by node name:

    select * from [nnt:article] where localname() = 'my-test-article'

     

    As to your last query (SELECT * FROM [nnt:acutalite] as item where item.[id] = '1363007526111') it won't work as you have defined "id" property as not-indexed, so it cannot be queried.

    If you need to perform queries on that property, remove the "indexed=no" from the definition (a full repository re-indexing will be needed, if you have already nnt:actualite nodes created).

    If you just do not want a property content to appear in the full text index, but still be able to query by that property, than "nofulltext" is enough. But this is for "string" properties only. Properties of type "long" are not automatically added to the full text index, as far as I remember.

     

    Kind regards

    Sergiy

     

  • Re: where clause fails with JCR-SQL2
    2013/03/11 21:08

    shyrkov <p> Hello,</p> <p> I am not sure, I understood, if you managed to do a query bythe node name?</p> <p> Just in case here is an example of a query for nnt:article nodes, filtered by node name:</p> <p> select * from [nnt:article] where localname() = &#39;my-test-article&#39;</p> <p> &nbsp;</p> <p> As to your last query (SELECT * FROM [nnt:acutalite] as item where item.[id] = &#39;1363007526111&#39;) it won&#39;t work as you have defined &quot;id&quot; property as not-indexed, so it cannot be queried.</p> <p> If you need to perform queries on that property, remove the &quot;indexed=no&quot; from the definition (a full repository re-indexing will be needed, if you have already nnt:actualite nodes created).</p> <p> If you just do not want a property content to appear in the full text index, but still be able to query by that property, than &quot;nofulltext&quot; is enough. But this is for &quot;string&quot; properties only. Properties of type &quot;long&quot; are not automatically added to the full text index, as far as I remember.</p> <p> &nbsp;</p> <p> Kind regards</p> <p> Sergiy</p> <p> &nbsp;</p>

  • Number of messages  407
    Registration date Mar 11, 2013
    0 (0 Good)
    0 (0 Bad)

    Re : Re: where clause fails with JCR-SQL2

    by  fabu »  2013/03/12 10:07

    Thank you Sergiy for that precision.
     
    I want to retrieve an object by a newly created property,
    A listener creates an id by inserting a timestamp on it.
    So I would like to retrieve them by ID and then generate JSON web services from http://.../actualites?id=[TIMESTAMP]
     
    That's why I request nnt:actualite with: 
    > SELECT * FROM [nnt:acutalite] as item where item.[id] = '1363007526111'

    I will try with "nofulltext" and string property.

    Thanks again.

    Fabien

      (fabu)

    Number of messages  28
    Registration date
    0 (0 Good)
    0 (0 Bad)

    Re : where clause fails with JCR-SQL2

    by  shyrkov »  2013/03/12 10:37

    Hello Fabien,

    just in case it could be helpful to you: each node in the repository has an automatically generated identifier (UUID - String), so that you can always retrieve the node by it using the query:

     

    SELECT * FROM [nnt:acutalite] where [jcr:uuid]='cb85de31-46b5-4e77-bd74-230dffbf706f'

    or in case you have access to the Java repository API using:

     

    session.getNodeByIdentifier("cb85de31-46b5-4e77-bd74-230dffbf706f");

     

    The only limitation of the UUIDs are that they are not preserved upon export/import, i.e. if you export your content and import it into another Jahia instance, the UUIDs for the nodes won't be the same.

     

    Kind regards

    Sergiy

  • Re : where clause fails with JCR-SQL2
    2013/03/12 10:37

    shyrkov <p> Hello Fabien,</p> <p> just in case it could be helpful to you: each node in the repository has an automatically generated identifier (UUID - String), so that you can always retrieve the node by it using the query:</p> <p> &nbsp;</p> <p> SELECT * FROM [nnt:acutalite] where [jcr:uuid]=&#39;cb85de31-46b5-4e77-bd74-230dffbf706f&#39;</p> <p> or in case you have access to the Java repository API using:</p> <p> &nbsp;</p> <p> session.getNodeByIdentifier(&quot;cb85de31-46b5-4e77-bd74-230dffbf706f&quot;);</p> <p> &nbsp;</p> <p> The only limitation of the UUIDs are that they are not preserved upon export/import, i.e. if you export your content and import it into another Jahia instance, the UUIDs for the nodes won&#39;t be the same.</p> <p> &nbsp;</p> <p> Kind regards</p> <p> Sergiy</p>

  • Number of messages  407
    Registration date Mar 12, 2013
    0 (0 Good)
    0 (0 Bad)

    Re : Re : where clause fails with JCR-SQL2

    by  fabu »  2013/03/12 13:30

    Thank you again Sergiy,

    We have to set an id that is numeric only, to communicate with a 3rd party app.

    And for information, I've tried to delete "indexed=no" but there is the same problem.

    Obviously, I reindex from tools.

    I still believe there are another problems since when I try to retrieve my object with  

    SELECT * FROM [nnt:acutalite] where [jcr:uuid]=' {nnt:actualite.jcr:uuid} '

    I found no result.

    Is it possible that JCR is broken?

      (fabu)

    Number of messages  28
    Registration date
    0 (0 Good)
    0 (0 Bad)

    Re : Re : Re : where clause fails with JCR-SQL2

    by  fabu »  2013/03/12 14:36

    Ok, let me apologize,

    It works! 

    We have make a white list to index into indexing_configuration.xml, I forgot it.

    I'm surprising that when I don't precise a where clause, all results are shown?

    However, I don't want to retrieve my [nnt:actualite] into my search results,

    So I will just index [nnt:actualite].[id] as long 

    Unless there is an another solution than mark id property as string and nofulltext!

     

    Thanks for all.

    Best Regards,

    Fabien

      (fabu)

    Number of messages  28
    Registration date
    Contact
    Share
    Feedback

    Get in touch

    Whether you are a current user or if you are just evaluating Jahia, we are here to help.

    Contact us

    Share this page