MODX Staff
I searched the forum for days about a answer to execute a SELECT Statement over more than one table, i.e. return values from more than one table. In fact I have to make a feature made in Evo ready for Revo. There I have ONE query Statement which selects data from 10 tables related to eachother. I tried several examples from the tutorials and from the forum here but no success. It all ends up with a final getCollection call which is related to a certain data object in the schema.For getting related objects, there are many methods in xPDO, such as getCollectionGraph(). These refer to a relation graph that can be automatically loaded based on the defined relationships. So, no, it is not xPDO's attempt to break anything up into smaller queries with logic more into script code than into a complex DB query. You can generate very complex queries with the API and execute them using the object methods xPDO provides, or simply prepare a PDOStatement from it that you can execute and fetch results from manually. xPDO just wraps the functionality of PDO.
It seems to me that in xPDO the attempt is to have several small queries and put the logic more into script code than into a complex DB query.
Could anybody confirm this? Then I can stop searching further.
It's easy to execute it with $modx->query(). But for how long will the deprecated DBAPI live in MODX Revo?
<?php
$kundenKonfig = $modx->getObject('KundenKonfig',array('usergroup'=>$usergroup));
$kundenKonfig->set('vk_jaehrlich',0); //just to get along with my query stmt.
foreach($kundenKonfig->getMany('fkKundeLob') as $kundeLob) {
foreach($kundeLob->getMany('fkKundeLobTarif') as $kundeLobTarif) {
$tarif = $kundeLobTarif->getOne('fkTarif');
foreach($tarif->getMany('fkTarifrolle','Tarifrolle.gueltig_ab<=CURDATE()') as $tarifrolle) {//don't know how to set the filter here? Additionally I have to find the "youngest" record (timestamp). How do I control the order the collection?
foreach($tarifrolle->getMany('fkTarifrollenSequenz') {
....
<?php
$modx->addPackage('WPCalc', MODX_CORE_PATH . 'components/WPCalc/model/','modx_');
$kundenKonfig = $modx->getObjectGraph('KundenKonfig','{KundeLobs:{Lob:{LobLeistungsarts:{Leistungsart:{}}}}}',array('id' => 1));
var_dump('kundenKonfig',$kundenKonfig->toArray());
foreach($kundenKonfig->KundeLobs as $kundeLob){
var_dump('kundeLob',$kundeLob->toArray());
$kundeLobTarif = $kundeLob->getOne('KundeLobTarif');
$tarif = $kundeLobTarif->getOne('Tarif');
var_dump('tarif',$tarif->toArray());
$query = $modx->newQuery('Tarifrolle');
$query->where(array('Tarifrolle.tarif'=>$tarif->get('id')));
$query->andCondition(array('Tarifrolle.gueltig_ab < CURDATE()'));
$query->sortby('Tarifrolle.timestamp','DESC');
$tarifrolle = $modx->getObject('Tarifrolle',$query);
var_dump('tarifrolle',$tarifrolle->toArray());
$lob = $kundeLob->getOne('Lob');
var_dump('lob',$lob->toArray());
foreach($lob->LobLeistungsarts as $lobLeistungsart){
var_dump('lobLeistungsart',$lobLeistungsart->toArray());
var_dump('Leistungsart',$lobLeistungsart->Leistungsart->toArray());
$query = $modx->newQuery('TarifrollenSequenz');
$query->where(array('tarifrolle_id'=>$tarifrolle->get('id')
,'leistungsart'=>$lobLeistungsart->Leistungsart->get('leistungsart')));
var_dump('is_tarifrollenSequenz',count($modx->getObject('TarifrollenSequenz',$query)));
}
}
return;