Can I reuse a where clause with many conditions in MySQL?

October 25, 2019, at 1:20 PM

I am migrating many MySQL queries from being hard-coded in a Node.js app to stored procedures (for use in a Java app). In the original Node app there is one SQL condition saved as a constant string that is substituted into many different SQL functions as part of the where clause and I cannot figure out how to accomplish the same thing using MySQL stored functions.

The constant is a SQL string that checks whether a variety of properties are null on a user object:

const USER_INFO_NOT_NULL = `u.id IS NOT NULL \
  AND u.email IS NOT NULL \
  AND u.gender IS NOT NULL \
  AND u.date_of_birth IS NOT NULL \
  AND u.first_name IS NOT NULL \
  AND u.last_name IS NOT NULL`

This SQL statement is substituted into many different queries as one of the conditions in their WHERE statement.

For example:

SELECT COUNT(*) count
    FROM User u
    WHERE ${USER_INFO_NOT_NULL} 
        AND some_other_condition
        AND another_condition
...

Unfortunately, not all of the queries are as simple as the one above and it would be a lot of work to rewrite each one.

I've tried using a stored function, however, stored functions can only take scalar values as parameters so I cannot pass along a user row.

I've also tried having the function take the user id (not ideal as the queries have already queried the user), query for that user and return a boolean based on the above condition. However, I'm not sure how to combine a SELECT with a RETURN in a stored function (can you do this?).

I've looked into prepared statements, however, they don't seem to work in this way as they only support a limited number of SQL commands.

So, is there a way to do something similar to what already exists in the Node.js app using only MySQL features?

Answer 1

You can define a view.

CREATE VIEW user_info_not_null 
SELECT *
FROM User u
WHERE u.id IS NOT NULL 
  AND u.email IS NOT NULL 
  AND u.gender IS NOT NULL 
  AND u.date_of_birth IS NOT NULL 
  AND u.first_name IS NOT NULL 
  AND u.last_name IS NOT NULL;

Then you can use the view like a table:

SELECT COUNT(*) count
FROM user_info_not_null
WHERE some_other_condition
AND another_condition

POPULAR ONLINE

Can I reuse a where clause with many conditions in MySQL?

Pandas: concise way to create/set subset or rows and columns (with MultiIndex)

PHP displaying extra tags

Dynamic variable value on HTML with Django

How do i search comma separated data in MySQL [duplicate]

Is there any regex to grab the names from alpha-numeric file name

Emulate an Intersect in MySQL w/ multiple tables and Join

Write Pandas DataFrame into a existing MySQL Database Table