⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

ORDER BY works only for one column at a time



Rodrigo Furtado

Rodrigo Furtado
  • profile picture
  • Member

Posted 21 January 2013 - 12:34 PM

Currently, it's not possible to order by multiple columns with grocery crud, but code igniter has this functionality, so it must be something really easy to correct :)

Lets say we have this table

id | company_name | segment | relevance
1 | bbbbbbbbbbbb | industry | 50
2 | cccccccccccccc | industry | 100
3 | aaaaaaaaaaaa | commerce | 25

4 | dddddddddddd | goverment | 10

And we try to order like this:

$crud->order_by('segment','asc');
$crud->order_by('relevance','desc');
$crud->order_by('company_name','asc');

It 'll only order by the LAST order_by you put and totally ignore the rest.

AMEN ?

davidoster

davidoster
  • profile picture
  • Member

Posted 27 January 2013 - 18:48 PM

I believe you can use your own model and from there set the ordering

check here, http://www.grocerycrud.com/documentation/options_functions/set_model

victor

victor
  • profile picture
  • Member

Posted 27 January 2013 - 19:14 PM

I think You can use your parameters as string: $this->db->order_by('title desc, name asc');

davedriesmans

davedriesmans
  • profile picture
  • Member

Posted 04 July 2013 - 08:58 AM

Rodrigo, did you found the solution?

do you mind sharing? looking for the same...


davidoster

davidoster
  • profile picture
  • Member

Posted 04 July 2013 - 09:58 AM

I think You can use your parameters as string: $this->db->order_by('title desc, name asc');

 

 

[member=Victor] is absolutely right!!! Yes you can order like this with multiple fields!!!

Sorry for the disorientation and making it harder than it was to solve it!


davedriesmans

davedriesmans
  • profile picture
  • Member

Posted 04 July 2013 - 10:33 AM

victor is absolutely right!!! Yes you can order like this with multiple fields!!!
Sorry for the disorientation and making it harder than it was to solve it!

yeah, but it doesn't work (and wasn't expecting it) from the controller


victor

victor
  • profile picture
  • Member

Posted 04 July 2013 - 11:52 AM

$crud-> order_by(....

davedriesmans

davedriesmans
  • profile picture
  • Member

Posted 04 July 2013 - 13:57 PM

ah, it's actually     

$crud->order_by('title asc, name');

 without the last asc


davidoster

davidoster
  • profile picture
  • Member

Posted 04 July 2013 - 20:01 PM

It should work, check here, http://ellislab.com/codeigniter/user-guide/database/active_record.html#select

scroll down till you get to the line, 

$this->db->order_by();


rothkj1022

rothkj1022
  • profile picture
  • Member

Posted 05 July 2013 - 15:58 PM

It should work, check here, http://ellislab.com/codeigniter/user-guide/database/active_record.html#select

scroll down till you get to the line, 

$this->db->order_by();

 

Thanks for the reference, but davedriesmans is right, it does not work.  When using multiple order_by calls, it only sets the first.  If using a string, it always appends " asc" to the end, but that won't work if the last sort in your query needs to be desc.


rothkj1022

rothkj1022
  • profile picture
  • Member

Posted 05 July 2013 - 16:12 PM

A few more comments:

 

To correct myself, when using multiple order_by calls, it only sets the *last* order_by, not the first.

 

I was able to workaround this bug by just adding my table's id as a final parameter when passing the multiple sorts as a string, like this:

$crud->order_by('sortcol1 asc, sortcol2 desc, id');

 

There must be some client-side sorting variable being persisted by a cookie/session variable.  When trying to test my order_by results I was running into the data sort not changing when I refreshed.  I disabled cookies and tried again, and that did the trick.


davidoster

davidoster
  • profile picture
  • Member

Posted 05 July 2013 - 20:39 PM

A few more comments:

 

To correct myself, when using multiple order_by calls, it only sets the *last* order_by, not the first.

 

I was able to workaround this bug by just adding my table's id as a final parameter when passing the multiple sorts as a string, like this:

$crud->order_by('sortcol1 asc, sortcol2 desc, id');

 

There must be some client-side sorting variable being persisted by a cookie/session variable.  When trying to test my order_by results I was running into the data sort not changing when I refreshed.  I disabled cookies and tried again, and that did the trick.

 

 

Post your code and the structure of any tables please . There is something strange there.


Amit Shah

Amit Shah
  • profile picture
  • Member

Posted 06 July 2013 - 06:17 AM

Well, what theme you are using? Are you using flexgrid or datatables, the solution is working and achievable in flexgrid but not in datatables. What in datatable it dose is collects all the records to play around with and based on the value from cookie, it will reset the order to the same. I have experienced the same. I will recommend not to use datatable in this case. If you looking for the same interface like it, you can alter flexgrid css for the same. Of course ... both has different requirements and hence will need some skill to duplicate the same from datatables to flexgrid, but it is quite possible as i myself have achieved the same for few of my projects.

 

If you using flexgrid and still having the trouble then its something that needs to be looked into for sure.. but i never faced that issue with flexgrid for sure.


davidoster

davidoster
  • profile picture
  • Member

Posted 06 July 2013 - 09:04 AM

O! I need to check this!!!

 

What in datatable it dose is collects all the records to play around with and based on the value from cookie, it will reset the order to the same. I have experienced the same. 


Hemant Randive

Hemant Randive
  • profile picture
  • Member

Posted 20 November 2015 - 11:55 AM

I am using grocery crud datatables theme i have tried flexigrid too but its sorting of table by two columns not work in it. I want to sort the table by two columns in grocery crud view


aron2020

aron2020
  • profile picture
  • Member

Posted 02 February 2016 - 19:05 PM

here is what works:

 

order by two columns:

1)

   $crud->order_by('last_name, start_date');  same as $crud->order_by('last_name, start_date', 'ASC');

 

2)

   $crud->order_by('last_name, start_date', 'DESC');

 

current;y one cannot have different directions per column (i.e. "order by c1 ASC, c2 DESC" is not possible)

 

would be nice to be able to do:

 

   $crud->order_by('last_name, start_date', 'ASC, DESC');   (will translate to "order by last_name ASC, start_date DESC")