All of my demonstrations will assume this line is already declared:
$db = JFactory::getDBO();
I will also provide a "guinea pig" table as a demo reference and playground for researchers when sample data is necessary for context. (db-fiddle.com demo)
CREATE TABLE IF NOT EXISTS `#__calendar` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`month` varchar(100) NOT NULL,
`color` varchar(100) NOT NULL,
`total` int(11) NOT NULL,
`status` varchar(10) NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `month` (`month`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
INSERT INTO `#__calendar` (`id`, `month`, `color`, `total`, `status`) VALUES
(1, 'January', 'red', 234, 'active'),
(2, 'February', 'red-orange', 998, 'inactive'),
(3, 'March', 'orange', 10, 'active'),
(4, 'April', 'orange-yellow', 718, null),
(5, 'May', 'yellow', 11, 'active'),
(6, 'June', 'yellow-green', 209, 'active'),
(7, 'July', 'green', 315, 'active'),
(8, 'August', 'green-blue', 8, 'inactive'),
(9, 'September', 'blue', 555, 'active'),
(10, 'October', 'blue-violet', 188, 'active'),
(11, 'November', 'violet', 39, 'active'),
(12, 'December', 'violet-red', 711, 'inactive');
| id | month | color | total | status |
| --- | --------- | ------------- | ----- | -------- |
| 1 | January | red | 234 | active |
| 2 | February | red-orange | 998 | inactive |
| 3 | March | orange | 10 | active |
| 4 | April | orange-yellow | 718 | |
| 5 | May | yellow | 11 | active |
| 6 | June | yellow-green | 209 | active |
| 7 | July | green | 315 | active |
| 8 | August | green-blue | 8 | inactive |
| 9 | September | blue | 555 | active |
| 10 | October | blue-violet | 188 | active |
| 11 | November | violet | 39 | active |
| 12 | December | violet-red | 711 | inactive |
View on DB Fiddle
Here is a list that I have compiled that reveals possible/likely issues and misunderstandings that may be encountered while calling methods from the $query object:
Chaining an Undeclared Object
If you are used to chaining from the $query variable declaration, you will likely hit a snag if you want to call a method from within a chained method.
Issue:
$query = $db->getQuery(true)
->select($query->rand());
Error: 0 Call to a member function rand() on null
Instead, you need to first declare $query object as a variable before you can call a (un-chained) method from it.
Solution: Declare $query then chain to your heart's content.
$query = $db->getQuery(true);
$query->select($query->rand()); // output: 0.28479570456295805
Null Parameters With concatenate()
concatenate() conditionally calls one of two MySQL functions. If you provide a non-falsey (not an empty, false, null, or zero value) as the second parameter, CONCAT_WS() will be called, otherwise CONCAT(). Learn the difference. While Joomla tries to provide a safeguard against a trouble-making null value as the separator/glue parameter via if ($separator), it ruins the opportunity to glue columns/text which may or may not be NULL together with an empty space (not NULL). What might cause trouble is if CONCAT() is rendered by the method and one of the values to be glued together is NULL.
Issue:
Syntax:
$query = $db->getQuery(true);
$query->select($query->concatenate(["month", "status"]))
->from("#__calendar")
->where("id = 4");`
or
$query = $db->getQuery(true);
$query->select($query->concatenate(["month", "status"], ""))
->from("#__calendar")
->where("id = 4");`
Output: NULL instead of: "April" (with empty string glued to end)
Solutions:
Learn about NULL-converting techniques and other workarounds here.
There are too many fringe cases to spell out here, but you might wish to employ COALESCE(`column_with_possibly_null_values`,'') or IFNULL(`column_with_possibly_null_values`,'') or avoid the Joomla method entirely and manually code your preferred MySQL function.
Recommendation: I'd actually like the gitters to consider modifying the source code from if ($separator) to if ($separator !== null) or similar to permit the use of an empty string as glue.
Syntax Error With regexp()
Issue:
Syntax:
$query = $db->getQuery(true);
$query->select("month")
->from("#__calendar")
->where($db->qn("month") . $query->regexp("[^elrty]$"));
Error: Query Syntax Error ...near [^elrty]$
Solution: The regex pattern must be single-quoted, so use $db-q() or $db->quote().
$query = $db->getQuery(true);
$query->select("month")
->from("#__calendar")
->where($db->qn("month") . $query->regexp($db->q("[^elrty]$"))); // output: March
Additionally: If you are going to pass user-supplied / untrusted data into the regex pattern, understand that you will need to escape characters with special meaning to the regex engine. In php, the tool is almost always preg_quote(), but because we are passing the value all the way through to MySQL, some additional adjustment is required (double-backslashing is used for escaping). If you are not overly familiar with MySQL's implementation or regular expressions, be aware that pattern delimiters are not used and that if you are going to use forward slashes in your pattern, you will need to write \\\\ in php for every \ that you intend to deliver.
New Example: If a row had a month value of wh^t? the hey! and you wanted to search for it by providing $search = "wh^t? the" into regexp(), you would need a where clause coded like this:
->where("month " . $query->regexp($db->q(preg_replace('~[-/\\^$*+?.()|[\]{}]~u', '\\\\\\\\$0', $search), false)));
// yes, you really do need all of these backslashes ------------------------------^^^^^^^^
and it would render as WHERE month REGEXP 'wh\\^t\\? the'
Furthermore, if you wanted to include word boundaries on both sides of the search variable in your condition logic, the expression would look like this:
->where("month " . $query->regexp($db->quote('([[:<:]]|^)' . preg_replace('~[-/\\^$*+?.()|[\]{}]~u', '\\\\\\\\$0', $search) . '([[:>:]]|$)', false)));
and it would render as WHERE month REGEXP '([[:<:]]|^)wh\\^t\\? the([[:>:]]|$)'
Passing a Seed to rand()
This is certainly a lesser known bit of functionality and not likely to be implemented for the vast majority of queries seeking randomization, but MySQL's RAND() function accepts an optional parameter to be used as the "seed". Learn more. This is useful if you need a predictable/repeatable sequence of "random numbers" for your query. Unfortunately, the Joomla core doesn't acknowledge any parameters passed to this function and will silently omit the seed value.
Issue:
Syntax:
$query = $db->getQuery(true);
$query->select("month")
->from("#__calendar")
->where("FLOOR(" . $query->rand(2) . "*10) = 9");
Output: [completely random / unpredictable resultset]
Solution: Do not call the rand() method when supplying a seed value.
$query = $db->getQuery(true);
$query->select("month")
->from("#__calendar")
->where("FLOOR(RAND(2)*10) = 9"); // output: June (*everytime)
Recommendation: I would like to see the source code updated to allow the passing of a seed value. Overall, I find this method to be a waste of programming, because offers less functionality and bloats the query building process with more characters. Do we need a method for every function? e.g. FLOOR(), MAX(), etc? If nothing else is done, the capital R typo should be changed in the source code.
*Full disclosure, I've never used RAND() with a seed in any of my projects -- this is new discovery for me.
Using castAsChar() Without a Length Parameter
Issue:
Syntax:
$query = $db->getQuery(true);
$query->select("id, month")
->from($db->qn("#__calendar"))
->where("id IN (1,2,10,11,12)")
->order($query->castAsChar("id"));
Output: [natural sorting (numeric) instead of computer sorting (alphabetically)]
Learn more about natural versus computer sorting. The source code is designed to abort the method's default action if there is no length parameter supplied in the call. The rendered order clause becomes: ORDER BY id <- no trace of CHAR() despite the developer's intention.
Solution: Do not call this method if you are not going to provide a length parameter.
$query->select("id, month")
->from($db->qn("#__calendar"))
->where("id IN (1,2,10,11,12)")
->order("CAST(id AS CHAR)"); // output order: 1, 10, 11, 12, 2
Additionally: This function can be used to truncate a value to a certain amount of characters (multibyte-safe). So, SELECT CAST('âêîôû' AS CHAR(2)) returns âê.
Recommendation: Providing more flexibility (or additional methods) to the types of casting that can be done seems like a good idea. Learn about other types.
