How to link data in two tables / inner Join /

83 Posts

puschpull Reply #11, 14 years, 7 months ago

Still couple pictures:

**********************************************************************************

Petr Püschel, Czech Republic
<hr>
http://puschpull.org
<hr>

83 Posts

puschpull Reply #12, 14 years, 7 months ago

Nobody know answer ?

Thanks!
:-)

Petr Püschel, Czech Republic
<hr>
http://puschpull.org
<hr>

83 Posts

puschpull Reply #13, 14 years, 7 months ago

Nobody nothing?

Petr Püschel, Czech Republic
<hr>
http://puschpull.org
<hr>

64 Posts

Kristoffer Reply #14, 14 years, 7 months ago

My opinion is that you have all the answers in my and lazylegs replies to this post and in the documentation for modx and xpdo. No offence but I will not write your application for you and if you don't manage to figure it out by yourself maybe you should consider to hire someone to do it for you.

Start with a more simple setup, for example two tables with primary keys and one title field plus the field for the foreign key in the second table. Then write the schema for this simple setup by hand because it is good practise. Add both indexes and relations to the schema according to the xpdo documentation. Then generate the model and start to play with the xpdo methods to join the two tables. When you have figured out the concept, then you should be able to scale it up to your application.

83 Posts

puschpull Reply #15, 14 years, 6 months ago

Kristoffer: OK
Thanks!

I Try This
:)

Petr Püschel, Czech Republic
<hr>
http://puschpull.org
<hr>

83 Posts

puschpull Reply #16, 14 years, 6 months ago

So, here is new and absolute simple data and tables:

Structure:

********************************************************************************************
********************************************************************************************
Data:

********************************************************************************************
********************************************************************************************
Structure:

********************************************************************************************
********************************************************************************************
Data:

********************************************************************************************
********************************************************************************************

sample Query (1):

********************************************************************************************
********************************************************************************************
sample Query (2):

********************************************************************************************
********************************************************************************************

Create Package with Bobs snippet "CreateXpdoClasses"
http://bobsguides.com/custom-db-tables.html

Package name:
pp_simple

Here is schema:
pp_simple.mysql.schema.xml

<?xml version="1.0" encoding="UTF-8"?>
<model package="pp_simple" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Job" table="job" extends="xPDOObject">
		<field key="id_job" dbtype="int" precision="10" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="type_job" dbtype="varchar" precision="64" phptype="string" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="id_job" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="People" table="people" extends="xPDOObject">
		<field key="id_people" dbtype="int" precision="10" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="name" dbtype="varchar" precision="32" phptype="string" null="false" />
		<field key="age" dbtype="int" precision="4" phptype="integer" null="false" />
		<field key="actual_job" dbtype="int" precision="10" phptype="integer" null="false" index="index" />

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

Here is testing page:
http://puschpull.org/modx_rev/join-simple

core of my snippet [[!join_simple]]

<?php
if($result){
  $f = $modx->newQuery('People');
  $f->sortby('id_people','ASC');
  $f->limit('16');  
}

$vypis = $modx->getCollection('People',$f); 

$f->prepare();
echo 'SQL QUERY:<div class="sql">'.$f->toSql().'</div><hr>';
echo 'Number of Rows: '.count($vypis).'<br><hr>';    
    
echo '<div id="db"><table class="border">';
echo '<tr><th>ID</th><th>Name</th><th>Age</th><th>Actual Job</th><th>Type Job</th></tr>';

foreach ($vypis as $vyp) {
  echo '<tr>';
  echo '<td> '.$vyp->get('id_people').'</td>'."\n";
  echo '<td> '.$vyp->get('name').'</td>'."\n";
  echo '<td> '.$vyp->get('age').'</td>'."\n";
  echo '<td> '.$vyp->get('actual_job').'</td>'."\n";
  echo '<td> '.$vyp->get('type_job').'</td>'."\n";
  echo '</tr>';
}

echo '</table></div> <!-- end db -->';

I need Relations and Join table People with table Job
and in column type_job listen actual_job name

Thanks!
:-)

Petr Püschel, Czech Republic
<hr>
http://puschpull.org
<hr>

83 Posts

puschpull Reply #17, 14 years, 6 months ago

Problem is Solved !
:-))

Result page:

http://puschpull.org/modx_rev/tables-a-b-adv

Little sample

Here is database table:

-- Adminer 3.3.4 MySQL dump

SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = 'SYSTEM';
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `abc_table_a`;
CREATE TABLE `abc_table_a` (
  `id_column_a_1` int(10) NOT NULL auto_increment,
  `column_a_2` varchar(64) NOT NULL,
  `column_a_3` int(10) NOT NULL,
  PRIMARY KEY  (`id_column_a_1`),
  KEY `column_a_3` (`column_a_3`),
  CONSTRAINT `abc_table_a_ibfk_1` FOREIGN KEY (`column_a_3`) REFERENCES `abc_table_b` (`id_column_b_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `abc_table_a` (`id_column_a_1`, `column_a_2`, `column_a_3`) VALUES
(1,	'Adam',	4),
(2,	'Bruno',	1),
(3,	'Carl',	2),
(4,	'Mary',	2),
(5,	'John',	3),
(6,	'Paul',	3),
(7,	'Nancy',	1);

DROP TABLE IF EXISTS `abc_table_b`;
CREATE TABLE `abc_table_b` (
  `id_column_b_1` int(10) NOT NULL auto_increment,
  `column_b_2` varchar(64) NOT NULL,
  PRIMARY KEY  (`id_column_b_1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `abc_table_b` (`id_column_b_1`, `column_b_2`) VALUES
(1,	'Child'),
(2,	'Student'),
(3,	'Worker'),
(4,	'Soldier');

-- 2012-03-28 10:50:31

With CMP Generator:
XML schema

    <?xml version="1.0" encoding="UTF-8"?>
    <model package="tabulky_a_b" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">


    <object class="TableA" table="table_a" extends="xPDOObject">
    <field key="id_column_a_1" dbtype="int" precision="10" phptype="integer" null="false" index="pk" generated="native" />
    <field key="column_a_2" dbtype="varchar" precision="64" phptype="string" null="false" />
    <field key="column_a_3" dbtype="int" precision="10" phptype="integer" null="false" index="index" />
     
    <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
    <column key="id_column_a_1" length="" collation="A" null="false" />
    </index>
    <index alias="column_a_3" name="column_a_3" primary="false" unique="false" type="BTREE" >
    <column key="column_a_3" length="" collation="A" null="false" />
    </index>

	<aggregate alias="TableB" class="TableB" local="column_a_3" foreign="id_column_b_1" owner="foreign" cardinality="one" />

    </object>



    <object class="TableB" table="table_b" extends="xPDOObject">
    <field key="id_column_b_1" dbtype="int" precision="10" phptype="integer" null="false" index="pk" generated="native" />
    <field key="column_b_2" dbtype="varchar" precision="64" phptype="string" null="false" />
     
    <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
    <column key="id_column_b_1" length="" collation="A" null="false" />
    </index>

	</object>


    </model>

Line

	<aggregate alias="TableB" class="TableB" local="column_a_3" foreign="id_column_b_1" owner="foreign" cardinality="one" />

paste I in generaed XML code

Snippet:

<?php
$path = MODX_CORE_PATH . 'components/tabulky_a_b/';
$result = $modx->addPackage('tabulky_a_b',$path . 'model/','abc_');  

echo '<form action="[[~[[*id]]]]" method="post" class="form">
<select name="sel_job" size="1">
<option value="0">ALL</option>';

if($result){
  $d = $modx->newQuery('TableB');
  $d->sortby('id_column_b_1','ASC');
  $seznam = $modx->getCollection('TableB',$d);
}

foreach ($seznam as $sez) {
	echo '<option value='.$sez->get('id_column_b_1').'>'.$sez->get('column_b_2').'</option>';
}

echo '</select>
<input type="submit" value="Odeslat">
</form><br><hr>';


 $d->prepare();
  echo 'SQL QUERY:<div class="sql">'.$d->toSql().'</div><hr>';

if (!isset($_POST['sel_job'])) $_POST['sel_job'] = '0';
$a = ($_POST['sel_job']);

echo 'ID zvoleného Job je: '.$a.'<br>';


if($result){
  $e = $modx->newQuery('TableB');
  $e->where(array(
    'id_column_b_1' => $a,
));
  $seze = $modx->getCollection('TableB',$e);
}

foreach ($seze as $se) {
	echo 'Vybraný Job je: '.$se->get('column_b_2').'<hr>';
}

if ($a == "0"){  // pokud budou vybrány všechny TableB, pak zobraz všechny TableA

if($result){
  $f = $modx->newQuery('TableA');
   $f->innerJoin('TableB','TableB', array("TableA.column_a_3 = TableB.id_column_b_1"));
  
  $f->select(array(
    $modx->getSelectColumns('TableA', 'TableA'),
    $modx->getSelectColumns('TableB', 'TableB', '', array('column_b_2','id_column_b_1')),
));
  
//  $f->where(array('column_a_3' => $a));
  $f->sortby('column_a_2','ASC');
  $f->limit('16');
}}  
  
else {           // jinak se zobrazí TableA z vybraného TableB
if($result){
  $f = $modx->newQuery('TableA');
   $f->innerJoin('TableB','TableB', array("TableA.column_a_3 = TableB.id_column_b_1"));
  
  $f->select(array(
    $modx->getSelectColumns('TableA', 'TableA'),
    $modx->getSelectColumns('TableB', 'TableB', '', array('column_b_2','id_column_b_1')),
));
  
  $f->where(array('column_a_3' => $a));
  $f->sortby('column_a_2','ASC');
  $f->limit('16');
}}

$vypis = $modx->getCollection('TableA',$f);  
// $vypis = $modx->getCollectionGraph('TableA', '{"TableB":{}', $f);


$f->prepare();
echo 'SQL QUERY:<div class="sql">'.$f->toSql().'</div><hr>';
echo 'Celkový počet nalezených záznamů: '.count($vypis).'<br><hr>';    
    
echo '<div id="db">
<table class="border">';
echo '<tr><th>ID</th><th>Name</th><th>Job id</th><th>Job nazev</th><th>Job id cizi</th></tr>';

foreach ($vypis as $vyp) {
  echo '<tr>';
  echo '<td> '.$vyp->get('id_column_a_1').'</td>'."\n";
  echo '<td> '.$vyp->get('column_a_2').'</td>'."\n";
  echo '<td> '.$vyp->get('column_a_3').'</td>'."\n";
  echo '<td> '.$vyp->get('column_b_2').'</td>'."\n";
  echo '<td> '.$vyp->get('id_column_b_1').'</td>'."\n";
  echo '</tr>';
}

echo '</table>
</div> <!-- end db -->';

And result page:

http://puschpull.org/modx_rev/tables-a-b-adv

Thanks All !
:-)

Petr Püschel, Czech Republic
<hr>
http://puschpull.org
<hr>