Retrieving db information from multiple tables

101 Posts

mintnl Reply #1, 8 years, 6 months ago

Hi, i'm developing a package ("Register") where i have multiple db-tables containing info on boats.
Table "boat" (class=RegBoat) has information on the boat number (id), when was it built and from which mould etc.
Table "owner" (class=RegOwn) has information on the owner of the boat, since when is it owned by this person, what's his name etc.
Table "seen" (class=RegSeen) has information on which race this boat last has been seen, the name and date of this event

i currently have:

$package_path = $modx->getOption('core_path').'components/Register/model/';
$modx->addPackage('Register', $package_path);

$query = $modx->newQuery('RegOwn');
$query->select('RegOwn.*, RegSeen.*');

$query->sortby('RegOwn.own_bnr','DESC');
$query->sortby('RegOwn.own_date','DESC');

$query->leftJoin('RegSeen', 'RegSeen', array('RegOwn.own_bnr = RegSeen.seen_bnr'));
//$query->prepare();
//$modx->log(xPDO::LOG_LEVEL_ERROR, $query->toSql());
//$output = $query->toSql();
$lines = $modx->getCollection('RegOwn',$query);

foreach ($lines as $line) {
    $fields = $line->toArray();
    $output .= $modx->getChunk('RegisterMainTableRow', $fields);    
}
 return $output;

I can parse this info in a chunk (RegisterMainTableRow) and display my table containing columns such as BoatNr, OwnerName, OwnerDate, SeenEvent, SeenDate

This displays correctly, however there is also a history in the Owner and Seen tables. I now use a foreach loop to skip double boat-numbers.
Because without a loop i get ALL results in my tables: say there were two owners (current and previous) of a boat that has been seen on two events (i.e. 2015 event and 2014 event)
As a result 4 rows are displayed in my table:
1: current owner - 2015 event
2: current owner - 2014 event
3: previous owner - 2015 event
4: previous owner - 2014 event

This is probably due to the fact that there is an left-join on the DB.

Question: Can anyone point me in the right direction to let the $query only display "current owner - 2015 event" in my table. So only the latest entries; only one row on ownership linked to only the latest event. So via a where-statement or sorting so that owner_date is highest and seen_date is highest.

Is this possible?

This question has been answered by multiple community members. See the first response.

Addict since 2012....

5,888 Posts

Bruno17 Reply #2, 8 years, 6 months ago

maybe with a subquery in the select, where you select only the RegSeen.date - field ordered by that field and limited to 1

An example.query with Resources to get its latest TV would look like that

select id,pagetitle, (select TV.tmplvarid from modx_site_tmplvar_contentvalues as TV where TV.contentid=resource.id order by tmplvarid DESC limit 1) as tmplvarid from modx_site_content as resource
order by resource.id

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

189 Posts

gissirob Reply #3, 8 years, 6 months ago

Without knowing your data or the structure of RegSeen, you may be able to use the MAX function in your select statement. It would mean breaking out RegSeen.* into individual columns, using MAX(RegSeen.Last_Seen_Date) for the appropriate column. You'll need a GROUP BY as well on the other columns.

discuss.answer

101 Posts

mintnl Reply #4, 8 years, 6 months ago

Thanx for your answers,

i solved a part of the problem.
Here is my working, probably very inefficient, code:

//add package
$package_path = $modx->getOption('core_path').'components/Register/model/';
$modx->addPackage('Register', $package_path);

$bnr=array(); $bootnummer=array(); $velden=array(); $fields=array();
//create query on seen at last event
$vraag = $modx->newQuery('RegLaatst');
$vraag->sortby('laatst_jaar', 'DESC');
$vraag->sortby('laatst_gemeld_tijd', 'DESC');
$allelaatsten = $modx->getCollection('RegLaatst',$query);
//create array with boatnrs lasts seen rows
foreach ($allelaatsten as $eenlaatste) {
    if(!in_array($eenlaatste->get('laatst_bnr'),$bnr)){
        $bnr[]=$eenlaatste->get('laatst_bnr');
        $velden[$eenlaatste->get('laatst_bnr')] = $eenlaatste->toArray();
    }
}

//create owner records query
$query = $modx->newQuery('RegOwn');
//$query->groupby('own_bnr');
$query->sortby('own_bnr','DESC');
$query->sortby('own_jaar','DESC');

//write exact query to log
$query->prepare();
$dequery = $query->toSql();
$modx->log(xPDO::LOG_LEVEL_ERROR, $dequery);

//getPage help
$total = $modx->getCount('RegOwn',$query);
$totalVar = $modx->getOption('totalVar', $scriptProperties, 'total');
$modx->setPlaceholder($totalVar,$total);
$limit = $modx->getOption('limit',$scriptProperties,10);
$offset = $modx->getOption('offset',$scriptProperties,0);


$query->limit($limit,$offset);

$regels = $modx->getCollection('RegOwn',$query);

//create array with most recent lines on ownership per boat.
foreach ($regels as $regel) {
    if(!in_array($regel->get('own_bnr'),$bootnummer)){
        $bootnummer[]=$regel->get('own_bnr'); 
        $fields[$regel->get('own_bnr')] = $regel->toArray();
//merge rows  per boat on ownership and last seen
        $alles[$regel->get('own_bnr')]= array_merge((array)$fields[$regel->get('own_bnr')],(array)$velden[$regel->get('own_bnr')]);
        $output .= $modx->getChunk('RegisterOverzichtTabelRij', $alles[$regel->get('own_bnr')]);    
    }
}

 return $output;

This returns my expected output, partially. As long as there is no getPage limit/offset is involved ;-) of course because of the original query returning about 700 rows and the cutdown itteration via the foreach only returning 380 rows. I realised the offset in the database is not the offset in de array because of the later in the foreach removed rows.

I've tried adding the $query->groupby('own_bnr'), this directly cuts down the number of rows to the correct size (one row per boat) only if there were multiple rows on ownership it somehow selects the row of a previous owner (even while the sort is bnr DESC, year DESC, so the most recent owner is the first row returned by the query.) Changing the sort to boatnr DESC, year ASC does not make a change.

I'm at a loss. So i've tried the groupby, but also tried the MAX(year) option described by gissirob (maybe not in the correct way and ofcourse am willing to try bruno's SELECT but don't know how but seem to lack the skills. So if anyone can help? That would be greatly appriciated!

Addict since 2012....

5,888 Posts

Bruno17 Reply #5, 8 years, 6 months ago

can you show your xpdo-schema?

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

discuss.answer

101 Posts

mintnl Reply #6, 8 years, 6 months ago

Here it is (contains more tables):

<?xml version="1.0" encoding="UTF-8"?>
<model package="Register" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="RegBnaam" table="reg_bnaam" extends="xPDOObject">
		<field key="bnaam_id" dbtype="int" precision="6" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="bnaam_bnr" dbtype="int" precision="3" phptype="integer" null="false" />
		<field key="bnaam_bnaam" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="bnaam_jaar" dbtype="int" precision="4" phptype="integer" null="false" />
		<field key="bnaam_gemeld_door" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="bnaam_gemeld_tijd" dbtype="int" precision="15" phptype="integer" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="bnaam_id" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="RegBoot" table="reg_boot" extends="xPDOObject">
		<field key="boot_id" dbtype="int" precision="6" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="boot_bnr" dbtype="int" precision="3" phptype="integer" null="false" />
		<field key="boot_jaar" dbtype="int" precision="4" phptype="integer" null="false" default="0" />
		<field key="boot_mal" dbtype="int" precision="1" phptype="integer" null="false" />
		<field key="boot_bouwer" dbtype="varchar" precision="25" phptype="string" null="false" />
		<field key="boot_meetbrief" dbtype="int" precision="1" phptype="integer" null="false" />
		<field key="boot_rip" dbtype="int" precision="4" phptype="integer" null="false" />
		<field key="boot_gemeld_door" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="boot_gemeld_tijd" dbtype="int" precision="15" phptype="integer" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="boot_id" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="RegGear" table="reg_gear" extends="xPDOObject">
		<field key="gear_id" dbtype="int" precision="6" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="gear_bnr" dbtype="int" precision="3" phptype="integer" null="false" />
		<field key="gear_wat" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="gear_merk" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="gear_type" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="gear_jaar" dbtype="int" precision="4" phptype="integer" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="gear_id" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="RegLaatst" table="reg_laatst" extends="xPDOObject">
		<field key="laatst_id" dbtype="int" precision="6" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="laatst_bnr" dbtype="int" precision="3" phptype="integer" null="false" />
		<field key="laatst_jaar" dbtype="int" precision="4" phptype="integer" null="false" />
		<field key="laatst_waar" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="laatst_land" dbtype="varchar" precision="3" phptype="string" null="false" />
		<field key="laatst_gemeld_door" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="laatst_gemeld_tijd" dbtype="int" precision="15" phptype="integer" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="laatst_id" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="RegOwn" table="reg_own" extends="xPDOObject">
		<field key="own_id" dbtype="int" precision="6" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="own_bnr" dbtype="int" precision="3" phptype="integer" null="false" />
		<field key="own_naam" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="own_club" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="own_land" dbtype="varchar" precision="3" phptype="string" null="false" />
		<field key="own_jaar" dbtype="int" precision="4" phptype="integer" null="false" />
		<field key="own_gemeld_door" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="own_gemeld_tijd" dbtype="int" precision="15" phptype="integer" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="own_id" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="RegRes" table="reg_res" extends="xPDOObject">
		<field key="res_id" dbtype="int" precision="6" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="res_bnr" dbtype="int" precision="3" phptype="integer" null="false" />
		<field key="res_land" dbtype="char" precision="3" phptype="string" null="false" />
		<field key="res_stuur" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="res_beman" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="res_wat" dbtype="set" precision="'EURO','NLNAT','UKNAT','RACE','JAARB'" phptype="string" null="false" />
		<field key="res_jaar" dbtype="int" precision="4" phptype="integer" null="false" />
		<field key="res_plaats" dbtype="int" precision="2" phptype="integer" null="false" />
		<field key="res_punten" dbtype="int" precision="4" phptype="integer" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="res_id" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="RegTeam" table="reg_team" extends="xPDOObject">
		<field key="team_id" dbtype="int" precision="6" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="team_bnr" dbtype="int" precision="3" phptype="integer" null="false" />
		<field key="team_stuur" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="team_beman" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="team_sorteer" dbtype="int" precision="2" phptype="integer" null="false" />
		<field key="team_gemeld_door" dbtype="varchar" precision="50" phptype="string" null="false" />
		<field key="team_gemeld_tijd" dbtype="int" precision="15" phptype="integer" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="team_id" length="" collation="A" null="false" />
		</index>
	</object>
</model>

Addict since 2012....

Answered Retrieving db information from multiple tables