Select on a field that is not pk

Questions
1

Hi,
I’m using version 1.2 and would like to perform select by a field that is unique (not the pk) and also by some field that is not unique. I saw in the documentation the method kong.db.:select(primary_key), but it works only on pk. Do I have a way to do this without iterating all the entities?

Thanks,
Janet

2

Hello, I faced similar issue - I needed to implement an extension to OAuth2 management API to allow removal of all tokens for particular authenticated_userid. I found out a method db.connector:query() that allows executing arbitrary DB queries, although bear in mind that it is not using cache so performance may be an issue.

Here is the code snippet demonstrating this (note that I used PDK to actually delete tokens, in order to propagate cache update events into cluster):

    DELETE = function(self, db)
        -- first select all tokens ID belonging to particular user, then delete it one by one in order to refresh nodes caches
        local authenticated_userid = unescape_uri(self.params.authenticated_userid)
        local tokens, err = db.connector:query("select id from oauth2_tokens where authenticated_userid='" .. authenticated_userid .."'")
        if err then
          return endpoints.handle_error(err)
        end
        for k,v in pairs(tokens) do
          if (v.id) then
            kong.db.oauth2_tokens:delete({id = v.id})
          end
        end
        return kong.response.exit(ngx.HTTP_NO_CONTENT)
    end