Forum Discussion

AmanMalhotra's avatar
AmanMalhotra
Expert
2 years ago

How to get only Messages where some specific custom field have value?

Hi Team,

I have a custom field added by Khoros in the messages object, I want to get only Messages where that specific custom field has value.

Sample query:
select c_abcd from messages where c_abcd!=""

I don't want to get all messages and then in the loop filter the messages which don't have any value.

Is there any direct API or solution to get the messages where the c_abcd custom field has some value?

Any related replies would be appreciated.

Regards
Aman

API
Community API
FreeMarker
Rest API
REST API v2 & LiQL
  • akshays's avatar
    akshays
    Ace
    2 years ago

    The exact query that you shared should work, basically c_abcd!="" would return all the messages with some value populated in this field. If possible you can also add certain Sort and Limit to structure the response. 

    • Akenefick's avatar
      Akenefick
      Genius
      2 years ago

      I tried this out and unfortunately it doesn't seem to work. Our community has a country custom field.

      Here I used c_country!="" as a constraint and got back users with no value for c_country.

       

      Then I tried using a specific country as a constraint.

      It seems like this should work but apparently you can't use custom fields as LiQL constraints.

      • AmanMalhotra's avatar
        AmanMalhotra
        Expert
        2 years ago

        Hi Akenefick ,

        Thank you for all the investigation you have done, similarly, it is not working for me and I am looking if there's any trick to make it work.

    • AmanMalhotra's avatar
      AmanMalhotra
      Expert
      2 years ago

      akshays I hope it worked but it is not working as of now, maybe it needs to be configured from khoros end and they say it is PS work and will involve money.

  • StanGromer's avatar
    StanGromer
    Boss
    2 years ago

    AmanMalhotra tried match? My engineer said best he thinks best Khoros can do is this:

    SELECT * FROM messages WHERE c_abcd MATCHES 'banana'

     

  • AmanMalhotra's avatar
    AmanMalhotra
    Expert
    2 years ago

    Hi isxtn,

    Custom fields cannot be used as a collection in queries, I hope you are aware of the Lithium LIQL syntaxes.
    I am able to get the custom fields but I have a requirement to only get the messages where a custom field has some value.

    Thank you for your reply.

  • StanGromer's avatar
    StanGromer
    Boss
    2 years ago

    My engineer is out for a few more days, but I can try to look through a few components on our side if you haven't figured this out yet, we have a bunch of custom attributes so I am guessing we have this figured out somewhere in the code.

    My non-engineering brain, I wrote this up long ago about SAML & custom attributes, it help at all? It's been a minute, but I know it drove me crazy how the same attribute would be called differently depending on what you are doing https://community.khoros.com/t5/Khoros-Community-Product/Using-SSO-Single-Sign-on-with-SAML-assertion-with-Custom-profile/m-p/628902#M11326

  • AmanMalhotra's avatar
    AmanMalhotra
    Expert
    2 years ago

    Hi StanGromer ,

    I tried match but it is giving response size 0

    Also, I just want the messages where c_abcd has some value and I think matches will not solve the requirement.

    Please let me know if there are any other solutions I can try.

    Thank you so much for your help.

  • isxtn's avatar
    isxtn
    Advisor
    2 years ago

    Does it work if you just query c_country like this? Do you get any response? 

    SELECT * FROM c_country

     

    You should be able to query custom fields. If you are not, it may be a misconfiguration on the part of Khoros and they should check it again on a support ticket to see what's going on. 

  • isxtn's avatar
    isxtn
    Advisor
    8 months ago

    Have you tried 

    SELECT id, body, subject FROM messages WHERE c_country !=''