QQuerying Type tables
Fri, 02/05/2010 - 08:56
Hi,
We were talking about expanding QCubed to be able to QQuery type tables. I opened a ticket #445 and created a patch that does exactly that. I think it is very useful feature.
So, with this code, it is possible to get something like:
<?php
PeopleType::QueryArray(QQ::AndCondition(
QQ::Equal(QQN::PeopleType()->People->PeopleDepartmentId, 12),
QQ::Like(QQN::PeopleType()->Name, '%person%')
),
QQ::OrderBy(QQN::PeopleType()->Name, false));
?>The result will be an array like $NameArray from PeopleType table, with results given from query.
Is that better performant than this?
<?php$people = People::QueryArray(QQ::AndCondition(
QQ::Equal(QQN::People()->PeopleDepartmentId, 12),
QQ::Like(QQN::People()->Name, '%person%')
),
QQ::GroupBy(QQN::People()->TypeId)
);
$types = array();
foreach($people as $person)
$types[$person->TypeId] = PersonType::$NameArray[$person->TypeId];
?>
If so, I can maybe see this being useful, though I worry that there may be some sort of runtime performance hit as a result of these changes even when not querying the Type table.
Performance is much better, since it will not create objects for People and then populate an array. With this it will just populate one dimensional array.
Moreover, there is something you oversaw in your example. Using QQ::Like() on Type table's unique filed (;
Also, if we have those tables:
PeopleType
----
Id
Name
People
----
Id
PeopleTypeId
Name
Surname
...
ContractVarinat
----
Id
PeopleTypeId
ContractTypeId
...
It is not easy to create query that will get all people that have some contract type...
You would have to do
<?php$aryCv = ContractVariant::QueryArray(QQ::Equal(
QQN::ContractVariant()->ContractVariant->ContractTypeId,
9));
$ary = array();
foreach($aryCv as $objCv) {
$ary[] = $objCv->PeopleTypeId;
}
$aryPeople = People::QueryArray(QQ::In(
QQN::People()->PeopleTypeId,
$ary));
?>
With this, you just hit
<?php$aryPeople = People::QueryArray(QQ::Equal(
QQN::People()->PeopleType->ContractVariant->ContractTypeId,
9));
?>
And one important note. It is possible to use order by (one of issues with current qq).
I have been using this patch on one of my projects, and since now I haven't notice any downfall of regular qq.
Very, very interesting work, thanks, dugokontov!
I noticed the ticket where you submitted your code for making this happen - thanks a bunch for your contribution! Since it's such a significant chunk of new code, I would like to ask you to:
1) Please create an example (like those on http://examples.qcu.be) that demonstrates this new functionality - against the examples site database.
2) Create a few unit tests that would verify that the functionality is working correctly.
All of the above shouldn't take you too long at all - but it would SIGNIFICANTLY help the community learn about your component, as well as be confident in modifying your code in the future (unit tests give such a great comfortable feeling when changing someone else's code :))
Thanks again!
Hey Guys,
Did this enhancement actually make it in? The ticket was last updated 16 months ago, but does not suggest it was added.
Would be a really handy feature for me right now.
Thanks,
=-)
Excellent,
I just applied the patch and have it working on QCubed 2.0.2.
Our Type table has an "Order" and "Published" field, the Order field was the key item to have working. We wanted to be able to set a global display order for the items using this Type table opposed to setting the display order for each individual entry.
I think we will have to scrap the Published feature for now though.
Big thank you to Dugokontov
How it was used Example
protected function dtrClientUrls_Bind() {
$this->dtrClientUrls->DataSource = ClientUrls::QueryArray(
QQ::AndCondition(QQ::Equal(QQN::ClientUrls()->ClientId, $this->objClient->Id)),
QQ::OrderBy(QQN::ClientUrls()->UrlType->Order)
);
}