Assume I have a single mySQL table (users) with the following fields:
userid gender region age ethnicity income
I want to be able to return the number of total records based on the number a user enters. Furthermore, they will also be providing additional criteria.
In the simplest example, they may ask for 1,000 records, where 600 records should have gender = ‘Male’ and 400 records where gender = ‘Female’. That’s simple enough to do.
Now, go one step further. Assume they now want to specify Region:
GENDER
Male: 600 records
Female: 400 records
REGION
North: 100 records
South: 200 records
East: 300 records
West: 400 records
Again, only 1000 records should be returned, but in the end, there must be 600 males, 400 females, 100 Northerners, 200 Southerners, 300 Easterners and 400 Westerners.
I know this isn’t valid syntax, but using pseudo-mySQL code, it hopefully illustrates what I’m trying to do:
(SELECT * FROM users WHERE gender = 'Male' LIMIT 600 UNION SELECT * FROM users WHERE gender = 'Female' LIMIT 400) INTERSECT (SELECT * FROM users WHERE region = 'North' LIMIT 100 UNION SELECT * FROM users WHERE region = 'South' LIMIT 200 UNION SELECT * FROM users WHERE region = 'East' LIMIT 300 UNION SELECT * FROM users WHERE region = 'West' LIMIT 400)
Note that I’m not looking for a one-time query. The total number of records and the number of records within each criteria will constantly be changing based on input by the user. So, I’m trying to come up with a generic solution that can be re-used over and over, not a hard-coded solution.
To make things more complicated, now add more criteria. There could also be age, ethnicity and income each with their own set number of records for each group, additional code appended to above:
INTERSECT (SELECT * FROM users WHERE age >= 18 and age <= 24 LIMIT 300 UNION SELECT * FROM users WHERE age >= 25 and age <= 36 LIMIT 200 UNION SELECT * FROM users WHERE age >= 37 and age <= 54 LIMIT 200 UNION SELECT * FROM users WHERE age >= 55 LIMIT 300) INTERSECT etc.
I’m not sure if this is possible to write in one query or if this requires multiple statements and iterations.
Advertisement
Answer
Flatten Your Criteria
You can flatten your multi-dimensional criteria into a single level criteria
Now this criteria can be achieved in one query as follow
(SELECT * FROM users WHERE gender = 'Male' AND region = 'North' LIMIT 40) UNION ALL (SELECT * FROM users WHERE gender = 'Male' AND region = 'South' LIMIT 80) UNION ALL (SELECT * FROM users WHERE gender = 'Male' AND region = 'East' LIMIT 120) UNION ALL (SELECT * FROM users WHERE gender = 'Male' AND region = 'West' LIMIT 160) UNION ALL (SELECT * FROM users WHERE gender = 'Female' AND region = 'North' LIMIT 60) UNION ALL (SELECT * FROM users WHERE gender = 'Female' AND region = 'South' LIMIT 120) UNION ALL (SELECT * FROM users WHERE gender = 'Female' AND region = 'East' LIMIT 180) UNION ALL (SELECT * FROM users WHERE gender = 'Female' AND region = 'West' LIMIT 240)
Problem
- It does not always return the correct result. For example, if there are less than 40 users whose are male and from north, then the query will return less than 1,000 records.
Adjust Your Criteria
Let say that there is less than 40 users whose are male and from north. Then, you need to adjust other criteria quantity to cover the missing quantity from “Male” and “North”. I believe it is not possible to do it with bare SQL. This is pseudo code that I have in mind. For sake of simplification, I think we will only query for Male, Female, North, and South
conditions.add({ gender: 'Male', region: 'North', limit: 40 })
conditions.add({ gender: 'Male', region: 'South', limit: 80 })
conditions.add({ gender: 'Female', region: 'North', limit: 60 })
conditions.add({ gender: 'Female', region: 'South', limit: 120 })
foreach(conditions as condition) {
temp = getResultFromDatabaseByCondition(condition)
conditions.remove(condition)
// there is not enough result for this condition,
// increase other condition quantity
if (temp.length < condition.limit) {
adjust(...);
}
}
Let say that there are only 30 northener male. So we need to adjust +10 male, and +10 northener.
To Adjust
---------------------------------------------------
Male +10
North +10
Remain Conditions
----------------------------------------------------
{ gender: 'Male', region: 'South', limit: 80 }
{ gender: 'Female', region: 'North', limit: 60 }
{ gender: 'Female', region: 'South', limit: 120 }
‘Male’ + ‘South’ is the first condition that match the ‘Male’ adjustment condition. Increase it by +10, and remove it from the “remain condition” list. Since, we increase the South, we need to decrease it back at other condition. So add “South” condition into “To Adjust” list
To Adjust
---------------------------------------------------
South -10
North +10
Remain Conditions
----------------------------------------------------
{ gender: 'Female', region: 'North', limit: 60 }
{ gender: 'Female', region: 'South', limit: 120 }
Final Conditions
----------------------------------------------------
{ gender: 'Male', region: 'South', limit: 90 }
Find condition that match the ‘South’ and repeat the same process.
To Adjust
---------------------------------------------------
Female +10
North +10
Remain Conditions
----------------------------------------------------
{ gender: 'Female', region: 'North', limit: 60 }
Final Conditions
----------------------------------------------------
{ gender: 'Female', region: 'South', limit: 110 }
{ gender: 'Male', region: 'South', limit: 90 }
And finally
{ gender: 'Female', region: 'North', limit: 70 }
{ gender: 'Female', region: 'South', limit: 110 }
{ gender: 'Male', region: 'South', limit: 90 }
I haven’t come up with the exact implementation of adjustment yet. It is more difficult than I have expected. I will update once I can figure out how to implement it.