$this->db->orderby ?

[eluser]outtolunch[/eluser]
Hello,

I was running through the forum, but i didn't seem to find the solution for my problem.

What I'm trying to do is, I want to write a SQL query, which would do the data sorting according this:

$this->db->order_by("sub_area="'.$sub_area.'"", "DESC");

Or basically show the ones with sub_area = 'something' first and then the rest.

I get a nasty SQL error.

Thank you for your replies.

[eluser]mohsin917[/eluser]
Buddy I don know that you can put this "sub_area = ".$sub_area in order_by function...
In this function you can give it 1st the field name and 2nd order. If you want to access records where "sub_area = '".$sub_area."'" then use this where funcition like

$this->db->where('sub_area', $sub_area);

[eluser]mohsin917[/eluser]
Might get some useful help here

[eluser]outtolunch[/eluser]
$this->db->order_by("sub_area", $sub_area, "DESC");
$companies = $this->db->getwhere('companies', array('area' => ''.$area.'', 'category' => ''.$category.'', 'status' => 'active'), $limit, $offset);

thats how i do it at the moment, i use getwhere to get the stuff i want, but the order_by would help me to retrieve sub_area='something' first.


i could use a SQL query: SELECT * FROM companies WHERE area = 'london' AND category = 'builders' AND status = 'active' ORDER BY sub_area='brent' desc, and work with $this->db->query();

But what about the perfomance? Is it gonna work faster if I use $this->db->getwhere() and then $this->db->order_by(); or just $this->db->query($query_string); or somethin?

By the way, I read the manual as well about the database functions.

Thanks for your help.

[eluser]outtolunch[/eluser]
any solutions?

[eluser]Christopher Blankenship[/eluser]
outtolunch truly there is no solution to your question.... SQL's ORDER BY does not work the way you are intending suggestion to look at the following : http://www.sql-tutorial.com/sql-order-by-sql-tutorial/

You are treating order by like a where clause. The only way to do what you are wanting is to separate the queries to return 1 query with those equal to brent then a second query to return those that are not equal to brent then merge the two returns together and return the result.

This is one way of doing it.

1. create a null array as a placeholder.
2. query 1.
3. query 2.
4. foreach result in query 1 add to the placeholder.
5. foreach result in query 2 add to the placeholder.
6. return placeholder.

[eluser]outtolunch[/eluser]
Well, yeah I thought of that. But I was trying to work with -> SELECT * FROM companies WHERE area = ‘london’ AND category = ‘builders’ AND status = ‘active’ ORDER BY sub_area=‘brent’ desc. It worked nicely, but when I add

LIMIT $limit, $offset I get a SQL error saying about the ','.


Surely should be a straight forwards thing.

[eluser]kgill[/eluser]
For those who are telling him ORDER BY doesn't work like that, ordinarily you'd be right if we're talking standards but MySQL went rogue and actually allows expressions in the order by clause so it will work but only if you're using MySQL (which somewhat defeats the purpose of bothering to use active record but that's beside the point).

Now that said I tested the following query and it works, so if you're getting errors when you convert it to AR, then AR is screwing it up when it builds the SQL (enable profiling to verify):

Code:

select *
from companies
WHERE area = 'london'
AND category = 'builders'
AND status = 'active'
ORDER BY sub_area=‘brent’ DESC, sub_area desc
limit 1, 10

if you're using any other DB you have to resort to unions and place holder columns:

Code:

select 1, companies.*
from companies
WHERE area = 'london'
AND category = 'builders'
AND status = 'active'
and sub_area = 'brent'
union
select 2, companies.*
from companies
WHERE area = 'london'
AND category = 'builders'
AND status = 'active'
and sub_area <> 'brent'
order by 1, sub_area desc

As for what's faster, using $this->db->query is always going to be faster because you aren't messing around with all the functions calls for constructing the SQL for you but the speed trade off isn't going to be anything you will notice so basically you're pointlessly optimizing.

[eluser]outtolunch[/eluser]
Thanks a lot for your help, thats what I was looking for.

Just I've got one more problem with pagination.

I use CI pagination class.

my data model looks like that at the moment:

$query = "SELECT * FROM companies WHERE area='$area' AND category='$category' AND status='active' ORDER BY sub_area='$sub_area' DESC, sub_area DESC";
$companies = $this->db->query($query);

return $companies;

If I add into SQL query the LIMIT $limit, $offset I start getting some syntax errors about the ','.

Is there a way to use $this->db->limit($limit, $offset); ?

Lets say use $this->db->query($query), then $this->db->limit($limit, $offset) and return mysql result.

[eluser]outtolunch[/eluser]
got this thing working at the end.

$query = "SELECT * FROM companies WHERE area='$area' AND category='$category' AND status='active' ORDER BY sub_area='$sub_area' DESC, sub_area DESC LIMIT $limit OFFSET $offset";

thats the query.

thanks again everyone, who gave advice.