Order_by doesn't parse the string properly #1826

Closed
daljit3 opened this Issue Sep 24, 2012 · 6 comments

Comments

Projects
None yet
2 participants

daljit3 commented Sep 24, 2012

        $this->db->select('id, name, subject, created_at')
                ->from('mydata')
                ->limit($limit, $offset)
                ->order_by("category = 'system' DESC ")
                ->order_by($sort_by, $sort_order);

The above query fails as In the query above, the first order_by clause parses the full string as a name of the column " category = 'system' DESC "

The error message I get is
`
Error Number: 1054

Unknown column 'category= 'system' DESC' in 'order clause'
I am using 3.0-dev version of CI which I downloaded about 2-3 weeks ago.

Contributor

dchill42 commented Sep 24, 2012

In what database is " category = 'system' " a valid field identifier or syntax for an ORDER BY clause?

In any case, have you tried ->order_by("category = 'system'", "DESC", FALSE) ?
That would separate the DESC clause from your identifier and prevent escaping the identifier (with the third parameter).

daljit3 commented Sep 24, 2012

Hi @dchill42 - False doesn't work. Order_by takes either 2 parameters i.e. a field name, ASC|DESC or just a string which is what I am doing in the first order_by clause.

Contributor

dchill42 commented Sep 24, 2012

No, if you look at Query Builder line 988 you will see that it takes up to 3 parameters: the identifier (or a comma-separated list of them), the optional separate direction spec, and an optional boolean to escape the identifier(s).

However, on closer inspection, I do see that the escape parameter defaults to not escaping the identifiers, so that probably isn't the problem.

My suspicion is that you are simply providing an invalid argument. To the best of my knowledge, an equals sign is not part of the valid syntax for ORDER BY. Does your query (as built) work in a direct database client with your data source?

daljit3 commented Sep 24, 2012

Ah actually I was too lazy to check the query builder code - I had a quick look at the CI documentation which is for 2.1. and it mentions 2 parameters only so I kind of assumed it takes 2 only.

I just tried this , ->order_by("category = 'system' DESC ",'',FALSE) and it worked fine. Previously I tried to pass second param as a False which obviously wasn't going to work.

I understand what you are trying to say for equals sign but the method I have used is more or less similar to this code

$array = array('name !=' => $name, 'id <' => $id, 'date >' => $date);
$this->db->where($array);

URL http://codeigniter.com/user_guide/database/active_record.html

so if I were to use 2 parameters approach I would not be able to provide value for category column in order by clause. I hope it makes a bit more clear now.

Just to mention - this code was working ok previously but when I updated to 3.0-dev it didn't test this specific code.

Contributor

dchill42 commented Sep 24, 2012

Oh, I think I get it now - you want to order by rows in the "system" category first, followed by rows not in the "system" category. For general clarity, and especially when combining with other ORDER BY clauses, I would recommend enclosing that in parentheses: ->order_by( '(category = "system")', 'DESC', FALSE )

Either way, I hope your query is working for you now. I'm not sure there's a change to be made to CI that would make this scenario any better (other than maybe stronger documentation).

daljit3 commented Sep 24, 2012

Yes, you got it now. :) thanks for the parenthesis tip Cheers

@daljit3 daljit3 closed this Sep 24, 2012

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment