FuelPHP Forums

williamlitwa

Hi,

I feel like this should be built into Orm. I have a table of clients. Those clients have projects attached to them. I want to sort the table by how many projects each client has. Simple. Or not so much, unless I'm missing something.

This is what I have:

$data['clients'] = DB::select(DB::expr(Model_Client::table().'.*, COUNT('.Model_Project::table().'.id) AS num_projects'))
                     ->from(Model_Client::table())
                     ->join(Model_Project::table(),'LEFT')
                     ->on(Model_Client::table().'.id','=',Model_Project::table().'.client_id')
                     ->where(Model_Client::table().'.account_id',$this->current_user->account_id)
                     ->as_object('Model_Client')
                     ->order_by('num_projects','asc')
                     ->limit($pagination->per_page)
                     ->offset($pagination->offset)
                     ->execute()
                     ->as_array();

But now I'm stuck, because it only returns the first result. Is there a simpler way to do this? Am I missing something obvious? How can I get this to work?

Thanks.

atabak

$clients = Model_Client::query()
                ->where('account_id', $this->current_user->account_id)
                ->offset($pagination->per_page)
                ->limit($pagination->offset)
                ->get();
        if($clients)
        {
            foreach ($clients as $client)
            {
                echo $client->name.' => '.count($client->projects).'<br>';
            }
        }
set Has Many to clients model and Belongs To projects model

Harro

That doesn't address the count sort order.

ORM queries support select() and order_by(), perhaps just use atabak's query as the basis and add both? I don't know if the ORM supports DB::expr(), never done it myself.

atabak

ORM SELECT support DB::expr() [hidden in docs or docs! i cant find it ;) ], but i cant find any way to change field name in orm query builder (in query, field like `t0`.`id` AS `t0_c0` and then change to model field name when return)

best way is :
$data = DB::select(array('c.id', 'cid'), array('c.name', 'cname'), array(DB::expr('(select count(*) from '.Model_Project::table().' where category_id = cid)'), 'pcount'))
                ->from(array(Model_Client::table(), 'c'))
                ->join(array(Model_Project::table(), 'p'), 'LEFT')
                ->on('c.id', '=', 'p.client_id')
                ->where('c.account_id', $this->current_user->account_id)
                ->order_by('pcount', 'ASC')
                ->execute()
                ->as_array();
if you want to use ORM (you must know the field name created in query builder like t0_c0, t0_c1,....):
$data = Model_Client::query()
                ->select('*', DB::expr('(select count(*) from '.Model_Project::table().' where category_id = ProjectIDName)')) // t0_c0
                ->join(array(Model_Project::table(), 'p'), 'LEFT')
                ->on('c.id', '=', 'p.client_id')
                ->where('c.account_id', $this->current_user->account_id)
                ->order_by('CountQueryName', 'ASC') // t0_c2
                ->limit($pagination->per_page)
                ->offset($pagination->offset)
                ->get();

Harro

Don't think you can.

The aliases are generated to make easy hydration possible. The code maintains a columnname-to-alias list so it can convert columnnames in things like where(), but the DB::expr() doesn't have a column name.

If you insist on using the ORM you might have to hard-code these aliases. I agree with you that a standard DB query is the best way to go.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion