Pagination with sorting

[eluser]dmorin[/eluser]
It sounds like CI might be escaping the whole count block of text causing mysql to look for it as a column. The problem isn't that it's trying to count from account, it's that it's looking for a column named "count(mrc...)" What version of CI are you using? If 1.7.0 I would recommend upgrading to 1.7.1 as it deals with db escaping much better.

A way to test this is the set the second parameter of the SELECT method to FALSE. So:

Code:

$this->db->select('a.id, a.name, a.billing_address_street,
        a.billing_address_city, a.billing_address_state, a.billing_address_postalcode,
        a.billing_address_country, a.website, a.description, a.eighthundrednumber_c,
        a.mechanixlooprating_c, a.extension_c, a.petfriendly_c, a.womenowned_c, a.providearide_c,
        count(mrc.id) as num_reviews', FALSE);

let me know if that changes anything.

EDIT:
I also just looked more closely at what you tried and it looks like you're trying to include where-like strings in your select which is bad. Things like "mrc.userid_c = a.id" are done in the JOIN criteria or the Where portion, NOT in the select. The exception is when you're doing an entire subquery, but those are usually really bad/inefficient.

EDIT2:
I just noticed you posted the resulting SQL and I was right that CI is adding ticks around the whole count item. Try setting the second param to FALSE as I mentioned above and see what that does.