While working with CI is moderately fun, sometimes this framework is getting on my nerves. The ActiveRecord, apart from the fact that it has nothing to do with ROR ActiveRecord philosophy is pretty annoying when it comes to “more complex” (not awfully dumb) queries. Let’s assume the following query (plain sql)
SELECT product.id AS id, name, shortdesc, price, hidden, issmall,
uniqueness, images, COUNT(product_id) AS categorynum
FROM product
LEFT JOIN productcategory AS pc
ON product.id = pc.product_id
GROUP BY product_id
ORDER BY name ASC
LIMIT 15
OFFSET 2
This is not that complicated, is it? Product and category is a many to many relationship via productcategory, everything else should be pretty straightforward, including the pagination. Btw offset is a valid keyword, the Jedit hiliter is a bit outdated.
Since I don’t want to spend my time rewriting this query to something activerecordish, but while I still am in need of escaping/filtering, I would do something like this (which would not work):
$sql = '
SELECT product.id AS id, name, shortdesc, price, hidden, issmall,
uniqueness, images, COUNT(product_id) AS categorynum
FROM product
LEFT JOIN productcategory AS pc
ON product.id = pc.product_id
GROUP BY product_id
ORDER BY ? ?
LIMIT ?
OFFSET ?
';
$query = $this->db->query($sql, array($sort, $dir, $perpage, $offset));
The problem is the order by clause, which does have parameters from outside php, so should be filtered, but is enclosed with backticks (yes, I can turn off escaping, but that’s not what I want to, especially not for all the fields). By the way, the binding is done with question marks, which is a common practice, but which is in my opinion can be very annoying. As a sidenote, I think the “%VAR% -> foreach replace with array[VAR]” template-like syntax is much better (for quick and simple solutions), but back to the original topic, moving onto the final solution:
$this->db->select("product.id AS id, name, shortdesc, price, hidden,
issmall, uniqueness, images, COUNT(product_id) AS categorynum");
$this->db->join('productcategory AS pc', 'product.id = pc.product_id');
$this->db->group_by('product_id');
$this->db->order_by($sort, $dir);
$this->db->limit($perpage, $offset);
$query = $this->db->get('product');
Is this nicer than the original? For hardcore php fans it may be, but to me it is pretty much the same as the original sql code; add the fact that I’m stuck with this solution, I tend to dislike it even more than hybrid sql queries (from different orm layers).