avenirer
Sorting/sub-sorting with relationship queries.
I think this might be covered in your documentation but not fully understanding it. I'd like my results to first be sorted by a value coming in from it's relationship, and secondly by it's own value.
Example I would like all people first sorted alphabetically by their role, and then if there are multiple people with the same role they should be sorted alphabetically by their last name. When I use the query below, it only sorts by the role.
$results = $this->person_model->with_role('order_by:title,asc')->order_by('last_name')->get_all();
Then why not do it like this?
$results = $this->role_model->with_persons('order_by:last_name,asc')->order_by('title','asc')->get_all();
:P
I need to be able to query the person model as primary. The example is simplified quite a bit, where I am actually using it I have additional with_ joins, a number of conditionals, and cache hook. I also like all the people being flat and not nested.
@louiswalch could you try now?
$results = $this->person_model->with_role('fields:whatever,whatever2,whatever3|join:true')->order_by('roles.title, persons.last_name','asc')->get_all();
Cool, nice addition. It's working, but can I define an "AS" with the field name? The table being joined has a field name shared with parent.
If you still want to define an AS, when mentioning fields, you must also prepends them with their table name... or wait until I make an update on this.
Ahh, you are right. It's prepends field name with relation name. It seems thou that the joined table name is also writing over the parent table's field name too.
So for my example above, if person and role both had a field named 'name' then the results contain 'name' and 'role_name' which are both populated with the value from role table.
That is because you didn't mention the fields you want from main table.
$results = $this->person_model->fields('field1,field2,field3')->with_role('fields:whatever,whatever2,whatever3|join:true')->order_by('roles.title, persons.last_name','asc')->get_all();
if you don't use fields() in the chain, it will use "SELECT *" which selects everything from both tables...
Ok that works, thanks. I just hate hardcoding fields when I want all of them.
@avenirer Where is "join: true" in use in the MY_Model? I can not locate it!
https://github.com/avenirer/CodeIgniter-MY_Model/issues/207#issuecomment-280690478
$results = $this->person_model->with_role('fields:whatever,whatever2,whatever3|join:true')->order_by('roles.title, persons.last_name','asc')->get_all();
@zilzurna (...but, since you asked, it is a join for two tables with a one-to-one relationship... this way, you only do a query and not two...)
@avenirer
Do not tell your friend what your enemy should not know. Arthur Schopenhauer
^_^ https://github.com/avenirer/CodeIgniter-MY_Model/blob/version-3.0/core/MY_Model.php#L1446
For this they would have to read the code. But usually, many people are soooooo lazy...