Welcome Guest, Not a member yet? Register   Sign In
string based custom ordering in active record
#1

[eluser]tsandesh23[/eluser]
This is my normal mysql query:
Code:
$sql = "SELECT * FROM item order by ";
if(my_condition)
{
       $sql. = "FIELD(`category`, 'Auto & Accessories', 'Couch', 'Bed',
       'Desk &     Office', 'Bike &            Scooter', 'Tools', 'Leisure',
        'Iron & Wood', 'Cabinet', 'Kitchen & Accessories', 'Refrigerator & Appliances',
        'Toys & Games', 'Chair', 'Table', 'Garden & Terrace', 'TV, HIFI & Computers',
        'General Item')";

}
else
{
     $sql .= "category asc";
}

I need it in CI in active record. I tried in following way:
Code:
if(my_condition)
            {
                $this->db->order_by("FIELD(`category`, 'Auto & Accessories', 'Couch',
                'Bed', 'Desk & Office', 'Bike & Scooter', 'Tools', 'Leisure', 'Iron &
                 Wood', 'Cabinet', 'Kitchen & Accessories', 'Refrigerator &
                 Appliances', 'Toys & Games', 'Chair', 'Table', 'Garden & Terrace',
                 'TV, HIFI & Computers', 'General Item')");

            }
            else
            {
                $this->db->order_by("category", "asc");
            }
But, I got this error::

Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'Couch'`, `'Bed'`, `'Desk` &
Office', `'Bike` & Scooter', `'Tools'`, `'Leisure'`,' at line 6


Filename: C:\xampp\htdocs\straatjutter_service\system\database\DB_driver.php

Line Number: 330
How to solve it? Actually I need my item's category sorted in above format. This can be done in normal mysql query. But, get confused in doing it in active records.
#2

[eluser]TheFuzzy0ne[/eluser]
Welcome to the CodeIgniter forums!

Please surround your code with [code][/code] tags in future, so we can read your code a little easier. Smile

I assume your categories are stored in another table? If so, you can just assign a number to each category, and sort by that. Alternatively, you can implement a CASE statement into the SELECT portion of your query, which will do the same thing, but that could get quite messy, and wouldn't be optimised.
#3

[eluser]tsandesh23[/eluser]
The category is a field in item table and (‘Auto & Accessories’, ‘Couch’, ‘Bed’, ‘Desk & Office’, ‘Bike & Scooter’, ‘Tools’, ‘Leisure’, ‘Iron & Wood’, ‘Cabinet’, ‘Kitchen & Accessories’, ‘Refrigerator & Appliances’, ‘Toys & Games’, ‘Chair’, ‘Table’, ‘Garden & Terrace’, ‘TV, HIFI & Computers’, ‘General Item’) are the possible values in category list. One record can contain one of above value and these are the only possible values in category field.
The problem is, the database is already designed and is live and thousands of customers are using this service. Initially, this project was built in normal php+mysql, but, our company need to change it into CI.

So, I can't change the database schema. Only thing that I should do is make the exact copy of previous code in CI and all the queries in active records. So, please help me to order these categories in above order.
#4

[eluser]TheFuzzy0ne[/eluser]
If your database hasn't been normalised, then there's only so much you can do to compensate for the design flaws.

As I mentioned before, you can use a CASE statement in your SELECT query, and order by that.

Alternatively, just code your MySQL by hand instead of using the Active Record class.

However, I don't understand why you're using FIELD(), since that's not going to do what you want to anyway. What's wrong with:

Code:
$this->db->order_by('category', 'asc');

Perhaps if you post your table structure, we'd be able to get a better understanding of the problem.
#5

[eluser]tsandesh23[/eluser]
My Database Name is StraatJutter and there are 10's of tables. But, tables related to above question are:

1> item (id, condition, description, created_at, updated_at, image_url_original, image_url_mid, image_url_thumb, category , device_id, latitude, longitude, flagged, inappropriate, status)

2> device(id, device_token, email, facebook, phone, preferred)

Previous code was::
Code:
$sql =  "select i.id, `condition`, `description`, `created_at`, `updated_at`,
                         concat('$url', `image_url_original`) as image_url_original,
                         concat('$url', `image_url_mid`) as image_url_mid,
                         concat('$url', `image_url_thumb`) as image_url_thumb, `category`, `latitude`, `longitude` ,
           d.device_token, d.facebook, d.preferred, d.email, d.phone, status from
                         item AS i, device AS d WHERE created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)        
                                              AND status='shared' AND i.device_id = d.device_token  order by ";

                                 if($language === 'nl_NL') {

                                $sql .= "FIELD('category', 'Auto & Accessories', 'Couch', 'Bed', 'Desk & Office', 'Bike & Scooter',
                                                                        'Tools', 'Leisure', 'Iron & Wood', 'Cabinet', 'Kitchen & Accessories',
                                                                        'Refrigerator & Appliances', 'Toys & Games', 'Chair', 'Table', 'Garden & Terrace',
                                                                        'TV, HIFI & Computers', 'General Item')";

                                
                        } else {
    $sql .= "category asc";
   }

The above code is working properly. If the language is 'nl_NL', that is dutch language, then the categories should be ordered in above format so that in dutch language, that will be like alphabetical order.

Now, I need it in CI active records format. I did following thing::

Code:
$this->db->select('i.id, condition, description, created_at, updated_at, category, latitude, longitude, status,                
                                                  d.device_token, d.facebook, d.preferred, d.email, d.phone');
                $this->db->select("CONCAT(  '$url',  image_url_original ) AS image_url_original");
                $this->db->select("CONCAT(  '$url',  image_url_mid ) AS image_url_mid");
                $this->db->select("CONCAT(  '$url',  image_url_thumb ) AS image_url_thumb");

                $this->db->from('item as i');
                $this->db->join('device as d', 'i.device_id = d.device_token');

                $this->db->where('created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)');
                $this->db->where('i.status','shared');

                if($language === 'nl_NL')
                {

                 $this->db->order_by("FIELD(`category`, 'Auto & Accessories', 'Couch', 'Bed', 'Desk & Office', 'Bike & Scooter', 'Tools',
                                                       'Leisure', 'Iron & Wood', 'Cabinet', 'Kitchen & Accessories', 'Refrigerator & Appliances', 'Toys &
                                                        Games', 'Chair', 'Table', 'Garden & Terrace', 'TV, HIFI & Computers', 'General Item')");
                    
                }
                else
                {
                    $this->db->order_by("category", "asc");
                }

The else part is working properly, but if part is not working , (note: $url is a variable with some url value and it's working.., it has no problem).
So, if it can be done by CASE in select, then, please give one short example. I am getting confusion in using CASE in active records and order by too.
#6

[eluser]TheFuzzy0ne[/eluser]
Please edit your post and put your code into [code][/code] tags.

F'ugly code:

$this->db->select(‘i.id, condition, description, created_at, updated_at, category, latitude, longitude, status,
d.device_token, d.facebook, d.preferred, d.email, d.phone’);
$this->db->select(“CONCAT( ‘$url’, image_url_original ) AS image_url_original”);
$this->db->select(“CONCAT( ‘$url’, image_url_mid ) AS image_url_mid”);
$this->db->select(“CONCAT( ‘$url’, image_url_thumb ) AS image_url_thumb”);

$this->db->from(‘item as i’);
$this->db->join(‘device as d’, ‘i.device_id = d.device_token’);

$this->db->where(‘created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)’);
$this->db->where(‘i.status’,‘shared’);

Neater code:
[code]
Code:
$this->db->select(‘i.id, condition, description, created_at, updated_at, category, latitude, longitude, status,          
    d.device_token, d.facebook, d.preferred, d.email, d.phone’);
$this->db->select(“CONCAT(  ‘$url’,  image_url_original ) AS image_url_original”);
$this->db->select(“CONCAT(  ‘$url’,  image_url_mid ) AS image_url_mid”);
$this->db->select(“CONCAT(  ‘$url’,  image_url_thumb ) AS image_url_thumb”);

$this->db->from(‘item as i’);
$this->db->join(‘device as d’, ‘i.device_id = d.device_token’);

$this->db->where(‘created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)’);
$this->db->where(‘i.status’,‘shared’);
[/code]
#7

[eluser]tsandesh23[/eluser]
ok, done. And sorry for delay in editing the post. Please help me to complete this task. Thanks in advance!!
#8

[eluser]TheFuzzy0ne[/eluser]
Aaah, now I see how it works.

The active record class is designed for simple queries. Is there any reason you can't just stick to a manual query like you did before?

You could probably do this:
Code:
$this->db->select('i.id, condition, description, created_at, updated_at, category, latitude, longitude, status,          
    d.device_token, d.facebook, d.preferred, d.email, d.phone');
$this->db->select("CONCAT('$url', `image_url_original`) AS `image_url_original`", false);
$this->db->select("CONCAT('$url', `image_url_mid`) AS `image_url_mid`", false);
$this->db->select("CONCAT('$url', `image_url_thumb`) AS `image_url_thumb`", false);
$this->db->select('FIELD(`category`,
    'Auto & Accessories', 'Couch', 'Bed', 'Desk & Office', 'Bike & Scooter', 'Tools',
    'Leisure', 'Iron & Wood', 'Cabinet', 'Kitchen & Accessories', 'Refrigerator & Appliances',
    'Toys & Games', 'Chair', 'Table', 'Garden & Terrace', 'TV, HIFI & Computers', 'General Item') AS `sort_col`', false);

$this->db->from('item as i');
$this->db->join('device as d', 'i.device_id = d.device_token');

$this->db->where('created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)');
$this->db->where('i.status', 'shared');

if($language === 'nl_NL') {
    $this->db->order_by('sort_col', 'asc');
} else {
    $this->db->order_by('category', 'asc');
}

I haven't tested it, but it should work if there are no syntax errors.

Please let me know how you get on.
#9

[eluser]tsandesh23[/eluser]
Thanx, it is working now..




Theme © iAndrew 2016 - Forum software by © MyBB