![]() The second option is running multiple SELECT count(*) queries with all the conditions. This is probably best if the are no that many rows on the table The first option would be a single query with no WHERE clause and one SUM for every condition. Once you have all the conditions (and sub-conditions) you have two alternatives to mount the SQL. This is because some conditions may be complex and have nested conditions.Īll this conditions and sub-conditions are being stored on the array. We need to use recursive regular expressions in order to detect opening/closing parenthesis.Įvery conditions is stored on an array and then sent to be processed (recursively). Once setup, we use a regex to fetch conditions for the current Level. In the previous example, It would be (Cond1 AND Cond2) OR Cond3. So the first thing to do is add extra parenthesis (with regexes) where needed so that the following algorithm will split correctly the conditions. We cannot split by AND|OR because we would be missing Cond1 AND Cond2 as a whole. So in an example like this: Cond1 AND Cond2 OR Cond3 However we cannot split ANDs and ORs as equal because ANDs have higher priority over ORs. We need a method for splitting the conditions. I don't know if MySQL has some sort of EXPLAIN for this, but suspect not, so that I will have to break out the SELECT as described and COUNT each sub-clause separately. I.e, break down skill=C++ AND ((skill=UML OR skill=Python) OR (not skill=Perl)) into COUNT(*) WHERE skill=C++ and `COUNT (*) WHERE (skill=UML OR skill=Python), etc So, using either PHP (and rexex?) or MySql, how can I break that down to see which parts of the search term contribute to the result? there might have been 200 candidates with C++, but 50 were not suitable because they have neither UML nor Python experience. I could like to COUNT(*) the number of hits, but I would also be very interested to know how much each "sub-clause" (if that's the correct term) of the query contributed to the result.Į.g. Maybe a regex? Just some code which finds every sub-term of a query, so that I can query the sub-terms individually.) (I highlighted such as because some answers seem to think that I am only interested in this query. As per my previous question, a software recruiter can enter a boolean text string, such as C++ AND ((UML OR Python) OR (not Perl)), which I will translate to SELECT * FROM candidates WHERE skill=C++ AND ((skill=UML OR skill=Python) OR (not skill=Perl)).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |