Forum Discussion

jeffshurtliff's avatar
jeffshurtliff
Boss
5 years ago

Unable to use the 'email' field in a WHERE clause when using LiQL against the users table

I am trying to figure out how I can retrieve the User ID or login (i.e. username) for a user when I only have their email address using LiQL, but for some reason I am getting an "Invalid query syntax" error in all of my attempts.

Below is the JSON returned when querying using Python.

{'status': 'error', 'message': 'Invalid query syntax', 'data': {'type': 'error_data', 'code': 604, 'developer_message': "'email' is not a valid constraint for the object 'users'. in: SELECT login FROM users WHERE email = 'admin@lithium.com'", 'more_info': ''}, 'metadata': {}}

 

Below is a screenshot from the API Browser in Studio.

Does anyone have any idea what I'm doing wrong?  I've tried all sorts of adjustments and email addresses (even using LIKE, MATCHES, etc.) but no dice.

Thanks!
Jeff

  • him_varma's avatar
    him_varma
    5 years ago

    Hi jeffshurtliff 

    It seems that the where clause constraint you are trying to use is not available.

    However as an alternate you can use the v1 API call to fetch user from email. Please find below:

    [yourcommunityurl]/restapi/vc/search/users?q=email:[email id]

    Please let me know if this was helpful.

    Thanks

  • Tarun's avatar
    Tarun
    Maven
    5 years ago

    Hi jeffshurtliff ,

    It seems the constraint for the email of the user's that you are trying to users is not supported i.e. you can't pass all fields after where clause.

    The constraints that can work with the particular users query are listed over here:

    https://devdocportal.lithium.com/t5/Community-API-v2-Reference/bd-p/restv2docs?section=commv2&collection=users#constraints

    Please refer to them and see if some other constraints can help you to carry the requirements.

    like you can have a query like:

    SELECT login FROM users WHERE login LIKE '%jane%'

     

    Please Give Kudos if you find this post useful and mark this as Accepted Solution if this is the required solution.


    Regards

    Tarun Kumar

  • him_varma's avatar
    him_varma
    Advisor
    5 years ago

    Hi jeffshurtliff 

    It seems that the where clause constraint you are trying to use is not available.

    However as an alternate you can use the v1 API call to fetch user from email. Please find below:

    [yourcommunityurl]/restapi/vc/search/users?q=email:[email id]

    Please let me know if this was helpful.

    Thanks

    • jeffshurtliff's avatar
      jeffshurtliff
      Boss
      5 years ago

      Hi him_varma,

      Thanks for the quick reply and the suggestion!  That's a great workaround!

      Does anyone from Khoros know why email isn't a valid constraint for users in LiQL and if this is a defect or something for which an enhancement request (i.e. idea) should be submitted?

      Thanks!

      Jeff

       

      • DougS's avatar
        DougS
        Khoros Oracle
        5 years ago

        Hi jeffshurtliff ,

        I think at the time we first launched API V2, a decision was made not to support email address as a constraint. You are welcome to submit an idea requesting this.

        Thanks,

        -Doug