[eluser]Tim Brownlaw[/eluser]
I was thinking that the FALSE in the Select might be getting remembered.
I've not looked into the code on this, I was just intrigued by your problem.
So I tried an experiment and found that you needed to have the last select statement with 'TRUE' after
any preceding FALSE settings.
I've only tried this with the code provided in this post.
Like this...
Code:
$this->db->select('(SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count', FALSE);
$this->db->select('*',TRUE);
$this->db->order_by('group_name', 'asc');
$result = $this->db->get('group');
I have swapped the two select statements around and set the last one to TRUE.
Leaving TRUE out results in the same 'bad' output already seen.So it's not switching back by default.
So this produces
Code:
SELECT (SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count, * FROM (`group`) ORDER BY `group_name` asc
As you can see, group_name is now 'ticked'. So next step is this...
Code:
$this->db->select('(SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count', FALSE);
$this->db->order_by('group_name', 'asc');
$this->db->select('*',TRUE);
$result = $this->db->get('group');
Shuffling everything above the $this->db->select('*',TRUE); that we don't require backticks for.
The output becomes
Code:
SELECT (SELECT COUNT(*) FROM user_group WHERE user_group_group_id = group_id) AS user_count, * FROM (`group`) ORDER BY group_name asc
Which is what you were after.
So there is a 'quirk' as you noted.
Disabling backticks in the select $this->db->select turns them off for all statements afterwards and you need to force them back on in another $this->db->select but then everything after that gets backticked.
I'd be curious to know if you get any further as the SQL itself has errors in it.
The reason you need the backticks on the tablename group is because it is a keyword, so MySQL let's
you get away with `group` so it's ignored. That's a clue as to why the SQL statement itself won't work.
Cheers
Tim
