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

51
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
READ ALSO
How do i search comma separated data in MySQL [duplicate]

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

This question already has an answer here:

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

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

I have to write a regex in SQL to filter and capture the file name and exclude the date stamps and the file fromat, is there any way we can do that? the examples I have are-

32
Emulate an Intersect in MySQL w/ multiple tables and Join

Emulate an Intersect in MySQL w/ multiple tables and Join

I need to emulate an Intersect query in MySQL

49
Write Pandas DataFrame into a existing MySQL Database Table

Write Pandas DataFrame into a existing MySQL Database Table

I have created a database using phpmyadmin called test that has one table called client_infoThe table in that database is empty (as shown in the attached image)

28