Migrating SQL applications to Cassandra – Pattern #4

Pattern #4: Get rid of all OR operators from sql where clauses.

For example, consider an SQL requirement to authenticate user by comparing the string entered by the user with either username or email or phone:

select u1.* from user u1 where (u1.username = ?1 or u1.email = ?1 or u1.phone = ?1) and u1.password = ?2

Cassandra does not allow OR in the where clause.

This should be accomplished by executing three separate sql calls:

1) User.findByUsername(?1, ?2)

2) User.findByEmail(?1, ?2)

and 3) User.findByPhone(?1, ?2) – either in parallel or sequentially and then either combining the results or returning as soon as one of the service returns a valid result – whatever the requirement may be.

With Ohioedge Persistence API for C* you can accomplish this by using a union statement as:

return User.findByUsername(?1, ?2) -> union User.findByEmail(?1, ?2)  -> union User.findByPhone(?1, ?2)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s