Order by a count-column

Order by a count-column

Login or register to post comments

4 replies [Last post]

Fri, 05/20/2011 - 18:49

vargasandor

Offline

Joined: 03/10/2010

Hi all!

In the code from examples:

<?php
    $objProjectArray = Project::QueryArray(
        QQ::All(),
        QQ::Clause(
            QQ::GroupBy(QQN::Project()->Id),
            QQ::Count(QQN::Project()->PersonAsTeamMember->PersonId, 'team_member_count')
        )
    );

    foreach ($objProjectArray as $objProject) {
        _p($objProject->Name . ' (' . $objProject->GetVirtualAttribute('team_member_count') . ' team members)');
        _p('<br/>', false);
    }
?>

is there any possibility to order the results by the virtual attribute 'team_member_count' ? Actually descending.

Thank You,
Sanyi.

Sat, 05/21/2011 - 14:34

#1

sp

Offline

Joined: 05/20/2011

I didn't actually try it in the example, but adding the following to the QQ:Clause list should work

QQ::OrderBy(QQ::Virtual("team_member_count"), false)

Sat, 05/21/2011 - 18:48

#2

vargasandor

Offline

Joined: 03/10/2010

Thank You, SP.
This is a step forward, but:

$objKepekArray = Favorits::QueryArray(
				QQ::All(),
				QQ::Clause(
					QQ::GroupBy(QQN::Favorits()->ModellId),
					QQ::Count(QQN::Favorits()->ModellId, 'id_count'),
					QQ::LimitInfo(100, $intNyiR),
					QQ::OrderBy(QQ::Virtual("id_count"), false)
				)				
			);

says:
Undefined Virtual Node: id_count .

What do I do wrong?

Thank You,
Sanyi.

Mon, 05/23/2011 - 05:03

#3

sp

Offline

Joined: 05/20/2011

I am sorry, looks like I mispoke. This has been a problem for a while. OrderBy does not recognize the QQuery computation virtual attributes. However, you can workaround this by using a SubSql instead. Its not clean, but it works.

So something like:

$objKepekArray = Favorits::QueryArray(
QQ::All(),
QQ::Clause(
QQ::Expand(
QQ::Virtual(
'id_count',
QQ::SubSql('SELECT count(modell_id) FROM favorits))
),
QQ::GroupBy(QQN::Favorits()->ModellId),
QQ::LimitInfo(100, $intNyiR),
QQ::OrderBy(QQ::Virtual("id_count"), false)
)
);

Mon, 05/23/2011 - 18:56

#4

vargasandor

Offline

Joined: 03/10/2010

With a correction (sql command close ) this is working.
But the order is not what should be.

Unfortunately the qcubed does not read views, other way, the:

	CREATE VIEW `favoritesview` AS select `favorits`.`modell_id` AS `modell_id`,count(`favorits`.`modell_id`) AS `megj` from `favorits` group by `favorits`.`modell_id` order by count(`favorits`.`modell_id`) desc

would do perfectly.

Can we generate code for views, is there maybe a solution?
Sanyi.