Web Service query operator similar to LIKE

Jerry Podhajsky · February 2017

I’m experimenting with a Luminate Web Services query, selecting Participants based on an EventID. My query looks like this:

select Participant from TeamRaiserRegistration where EventId = 1234 and Participant.ConsName.FirstName = ‘Ann’

That works just fine, but I need something like the typical SQL ‘LIKE’ operator in order to include variants of the name ‘Ann’ — Annabel, AnnMarie, Manny, whatever...matching the string anywhere in the field value, in a case insensitive manner.

In typical SQL my query would look like:

select Participant from TeamRaiserRegistration where EventId = 1234 and Participant.ConsName.FirstName LIKE ‘%Ann%’

This doesn’t work and I’ve found other references to CQL (Cassandra Query Language — is this what the web service query uses?) — that tell me something similar to LIKE doesn’t exist in this query language. The docs say the wildcard is

'*' | FIELD '*'

But using '*Ann*' does not work either.

I’m hoping I’m just missing it or there’s a workaround. Thanks in advance!

Matthew Smith 2 · February 2017

This isn't the full solution you're looking for but you may be able to essentially duplicate the functionality of LIKE 'Ann%' with:

select Participant from TeamRaiserRegistration where EventId = 1234 and Participant.ConsName.FirstName >= ‘Ann’ and Participant.ConsName.FirstName < 'Ano'

Not sure about LIKE '%Ann%' though.

Noah Cooper · February 2017

Jerry Podhajsky:

I’m experimenting with a Luminate Web Services query, selecting Participants based on an EventID. My query looks like this:

select Participant from TeamRaiserRegistration where EventId = 1234 and Participant.ConsName.FirstName = ‘Ann’

That works just fine, but I need something like the typical SQL ‘LIKE’ operator in order to include variants of the name ‘Ann’ — Annabel, AnnMarie, Manny, whatever...matching the string anywhere in the field value, in a case insensitive manner.

In typical SQL my query would look like:

select Participant from TeamRaiserRegistration where EventId = 1234 and Participant.ConsName.FirstName LIKE ‘%Ann%’

This doesn’t work and I’ve found other references to CQL (Cassandra Query Language — is this what the web service query uses?) — that tell me something similar to LIKE doesn’t exist in this query language. The docs say the wildcard is

'*' | FIELD '*'

But using '*Ann*' does not work either.

I’m hoping I’m just missing it or there’s a workaround. Thanks in advance!

One thing I'd note is that while the Web Services API does not support the LIKE operator, you can do what you're describing with the REST API getParticipants method which allows wildcards, e.g. first_name=Ann%.

http://open.convio.com/api/#teamraiser_api.getParticipants_method.html

Noah Cooper · February 2017

Jerry Podhajsky:

I’m experimenting with a Luminate Web Services query, selecting Participants based on an EventID. My query looks like this:

select Participant from TeamRaiserRegistration where EventId = 1234 and Participant.ConsName.FirstName = ‘Ann’

That works just fine, but I need something like the typical SQL ‘LIKE’ operator in order to include variants of the name ‘Ann’ — Annabel, AnnMarie, Manny, whatever...matching the string anywhere in the field value, in a case insensitive manner.

In typical SQL my query would look like:

select Participant from TeamRaiserRegistration where EventId = 1234 and Participant.ConsName.FirstName LIKE ‘%Ann%’

This doesn’t work and I’ve found other references to CQL (Cassandra Query Language — is this what the web service query uses?) — that tell me something similar to LIKE doesn’t exist in this query language. The docs say the wildcard is

'*' | FIELD '*'

But using '*Ann*' does not work either.

I’m hoping I’m just missing it or there’s a workaround. Thanks in advance!

Oh, and CQL is "Convio Query Language".

Jerry Podhajsky · February 2017

Noah Cooper:

Jerry Podhajsky:

I’m experimenting with a Luminate Web Services query, selecting Participants based on an EventID. My query looks like this:

select Participant from TeamRaiserRegistration where EventId = 1234 and Participant.ConsName.FirstName = ‘Ann’

That works just fine, but I need something like the typical SQL ‘LIKE’ operator in order to include variants of the name ‘Ann’ — Annabel, AnnMarie, Manny, whatever...matching the string anywhere in the field value, in a case insensitive manner.

In typical SQL my query would look like:

select Participant from TeamRaiserRegistration where EventId = 1234 and Participant.ConsName.FirstName LIKE ‘%Ann%’

This doesn’t work and I’ve found other references to CQL (Cassandra Query Language — is this what the web service query uses?) — that tell me something similar to LIKE doesn’t exist in this query language. The docs say the wildcard is

'*' | FIELD '*'

But using '*Ann*' does not work either.

I’m hoping I’m just missing it or there’s a workaround. Thanks in advance!

Oh, and CQL is "Convio Query Language".

Thanks for all responses. What I ended up doing is querying for all participants and teams for a specific eventId and storing that as a json array on the client. From there I can filter and sort on the client side. Works fine. This may not be a great solution long term, but for now the number of participants isn't so great that it causes a significant delay.

I'm just getting up to speed with Convio/Luminate, so I will probably have many easy/dumb/obvious questions going forward...this is the first!

Web Service query operator similar to LIKE

Comments

Categories