How can we group by a column that is not in its own table in CodeIgniter DataMapper ORM?

I am using the DataMapper ORM and I have the following tables:

channel_providers
==================
id(pk)
name

channels
=========
id(pk)
channel_provider_id(fk)
name

sales_records
==============
id(pk)
channel_id(fk)
customer_name
salesman_name
amount

What I would like to do is, to display all rows in the sales_records tables including channel provider name and channel name, but group by the name column in the channel_providers table.开发者_如何学Go

Currently, I have following codes:

$sales_records=new Sales_record();
$sales_records->get();

foreach($sales_records as $sales_record){
 $sales_record->channel->get();
 $sales_record->channel->channel_provider->get();
 //now I can write codes to to echo all data, but the problem is that $sales_records is not grouped by the channel_providers.name column

}

From the group_by example in this page: http://datamapper.wanwizard.eu/pages/get.html

I know I can use group_by function on a column that exists in the table that the object associates with. But in my case, the column that I want to use group_by is not in its own table, how can this be done?

Many thanks to you all.

Using the group_by clause should work, since it should automatically make the necessary join clauses for you, thereby opening you up to being able to use it. Have you actually tried it?

If that doesn't work, you can fall back to CodeIgniter's base Active Record style, for something like this, before your get calls and after your object creation, since Datamapper just sits on top of CI's database stuff:

$this->db->join('channels','channels.id = sales_records.channel_id');
$this->db->join('channel_providers','channel_providers.id = channels.channel_provider_id');

You should be able to then group your results using the group_by and get code from before:

$sales_record->channel->channel_provider->group_by('channel_providers.name')->get();