How to link data in two tables / inner Join /

83 Posts

puschpull Reply #1, 14 years, 7 months ago

Hi!
I have this problem:

Two tables:

Category (this is list of categories for second table)
Galerie (all other data for listing komplet photogallery)

Structure
Table pp_category

-- 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 `pp_category`;
CREATE TABLE `pp_category` (
  `category_id` smallint(4) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL,
  `name_alt` varchar(128) NOT NULL,
  `parent_id` smallint(4) default NULL,
  `view` smallint(2) NOT NULL default '1',
  PRIMARY KEY  (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 2012-03-08 07:20:26

Table pp_galerie

-- 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 `pp_galerie`;
CREATE TABLE `pp_galerie` (
  `galerie_id` int(10) NOT NULL auto_increment,
  `title` varchar(48) NOT NULL,
  `name_cz` varchar(64) NOT NULL,
  `name_lat` varchar(64) NOT NULL,
  `kategorie` smallint(4) NOT NULL,
  `kde` varchar(64) NOT NULL,
  `kdy` date default NULL,
  `upload_date_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `copyright` varchar(32) NOT NULL,
  `permission` smallint(2) NOT NULL,
  `display` smallint(2) NOT NULL,
  `short_text` mediumtext,
  `picture_name` varchar(48) default NULL,
  `thumbs_name` varchar(48) default NULL,
  `path_large` varchar(64) default NULL,
  `path_thumbs` varchar(64) default NULL,
  PRIMARY KEY  (`galerie_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 2012-03-08 07:31:54

My Package (schema) name "pepish2"

<?xml version="1.0" encoding="UTF-8"?>
<model package="pepish2" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Category" table="category" extends="xPDOObject">
		<field key="category_id" dbtype="smallint" precision="4" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="name" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="name_alt" dbtype="varchar" precision="128" phptype="string" null="false" />
		<field key="parent_id" dbtype="smallint" precision="4" phptype="integer" null="true" />
		<field key="view" dbtype="smallint" precision="2" phptype="integer" null="false" default="1" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="category_id" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="Galerie" table="galerie" extends="xPDOObject">
		<field key="galerie_id" dbtype="int" precision="10" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="title" dbtype="varchar" precision="48" phptype="string" null="false" />
		<field key="name_cz" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="name_lat" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="kategorie" dbtype="smallint" precision="4" phptype="integer" null="false" />
		<field key="kde" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="kdy" dbtype="date" phptype="date" null="true" />
		<field key="upload_date_time" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP" />
		<field key="copyright" dbtype="varchar" precision="32" phptype="string" null="false" />
		<field key="permission" dbtype="smallint" precision="2" phptype="integer" null="false" />
		<field key="display" dbtype="smallint" precision="2" phptype="integer" null="false" />
		<field key="short_text" dbtype="mediumtext" phptype="string" null="true" />
		<field key="picture_name" dbtype="varchar" precision="48" phptype="string" null="true" />
		<field key="thumbs_name" dbtype="varchar" precision="48" phptype="string" null="true" />
		<field key="path_large" dbtype="varchar" precision="64" phptype="string" null="true" />
		<field key="path_thumbs" dbtype="varchar" precision="64" phptype="string" null="true" />

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

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

An now:

In listing SQL Query i need in table pp_galerie column kategorie not number (column category_id from table pp_category) but name this kategorie (column name from table pp_category )

I experiment with many variants this code, but no success

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

$query = $modx->newQuery('Galerie');
$query->select($modx->getSelectColumns('Galerie', 'Galerie', 'Galerie_', 
	array(
	  'galerie_id', 
	  'name_cz', 
	  'name_lat',
	  'kategorie',
	  'kde', 
	  'kdy',
	  'path_large',
	)));
$query->select($modx->getSelectColumns('Category', 'Category', 'Category_', 
	array(
	  'category_id', 
	  'name', 
	)));
$query->innerJoin('Category','Category', array("Galerie.kategorie = Category.category_id"));


$query->where(array(
  //  'Category.name:LIKE' => 'Dravci',
  //  'Category.category_id:LIKE' => '21',
  //  'kategorie' => '21',
    'galerie_id:>=' => 10,
    'display:!=' => 2,
    'kde:IN' => array('Mostecko','ZOO'),
));
$query->sortby('name_cz','ASC');
$query->sortby('kdy','DESC');
$query->limit(120);
$vysledek = $modx->getCollection('Galerie',$query);

 $query->prepare();
 echo 'SQL QUERY:<br><font face="Tahoma" size="-1" color="#FFFF99">'.$query->toSql().'</font><br><hr>';
 echo 'Celkový počet nalezených záznamů: '.count($vysledek ).'<br><hr>';

other variant (not success)

$query = $modx->newQuery('Galerie');
$query->innerJoin('Category','Category', array("Galerie.kategorie = Category.category_id"));

/*
$query->select($modx->getSelectColumns('Galerie', 'Galerie', 'Galerie_', 
    array(
      'galerie_id', 
      'name_cz', 
      'name_lat',
      'kategorie',
      'kde', 
      'kdy',
      'path_large',
    )));
*/


$query->where(array(
     'Category.name:LIKE' => 'Dravci',
  //  'Category.category_id:LIKE' => '21',
  //    'kategorie' => '21',
    'galerie_id:>=' => 10,
    'display:!=' => 2,
    'kde:IN' => array('Mostecko','ZOO Plzeň'),
));
          /*
$query->orCondition(array(
    'kde:LIKE' => 'Chomutovsko%',
));
*/
$query->sortby('name_cz','ASC');
$query->sortby('kdy','DESC');
$query->limit(120);
$vysledek = $modx->getCollection('Galerie',$query);

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

This code work fine:
example is here:
http://puschpull.org/modx_rev/test-dropdown/dropdown-new

<?php
function getTree($parent) {  
  global $modx;
    $path = MODX_CORE_PATH . 'components/pepish2/';
    $result = $modx->addPackage('pepish2',$path . 'model/','pp_');  
  if($result){
  $g = $modx->newQuery('Category');
  $g->where(array(
    'parent_id' => $parent,
));
  $trees = $modx->getCollection('Category',$g);
}  
$pole = array();  
foreach ($trees as $tre) {
	$pole[] = $tre->get('category_id');
	$pole = array_merge($pole, getTree($tre->get('category_id')));
}
return $pole;
}




$path = MODX_CORE_PATH . 'components/pepish2/';
$result = $modx->addPackage('pepish2',$path . 'model/','pp_');

echo '<form action="[[~[[*id]]]]" method="post" class="form">
<select name="sel_cat" size="1">';

if($result){
  $d = $modx->newQuery('Category');
  $d->where(array(
    'view' => '1',
));
  $d->orCondition(array(
   'view' => '2',
));
  $d->sortby('category_id','ASC');
//$d->limit('16');

  $seznam = $modx->getCollection('Category',$d);
}

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

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

 $d->prepare();
  echo 'SQL QUERY:<br><font face="Tahoma" size="-2" color="#FFFF99">'.$d->toSql().'</font><br><hr>';


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



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

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

foreach ($seze as $se) {
	echo 'Vybraná kategorie je: '.$se->get('name').'<hr>';
}


$iii = $a;
$seznam_kategorii = getTree($iii);
$seznam_kategorii[] = $iii;
$seznam_kategorii_retezec = '\''.implode('\',\'', $seznam_kategorii).'\'';
echo 'Seznam kategorií celé vybrané větve:  <br>'.$seznam_kategorii_retezec.'<br><hr>';


if($result){
  $f = $modx->newQuery('Galerie');
  $f->where(array('kategorie:IN' => $seznam_kategorii));
  $f->sortby('galerie_id','ASC');
//$f->limit('16');
  $vypis = $modx->getCollection('Galerie',$f);
}
    
$f->prepare();
 echo 'SQL QUERY:<br><font face="Tahoma" size="-2" color="#FFFF99">'.$f->toSql().'</font><br><hr>';
 echo 'Celkový počet nalezených záznamů: '.count($vypis).'<br><hr>';    
    
echo '<div id="db">
<table align="center" width="96%" height="auto" border="1" bordercolor="#FFCC00" bgcolor="#008080">';
echo '<tr class="tab"><th>ID</th><th>Name CZ</th><th>Name Latin</th><th>Kategorie</th><th>Místo</th><th>Datum snímku</th><th>Cesta k fotografii</th></tr>';

foreach ($vypis as $vyp) {
  echo '<tr>';
  echo '<td> '.$vyp->get('galerie_id').'</td>'."\n";
  echo '<td> '.$vyp->get('name_cz').'</td>'."\n";
  echo '<td> '.$vyp->get('name_lat').'</td>'."\n";
  echo '<td> '.$vyp->get('kategorie').'</td>'."\n";
  echo '<td> '.$vyp->get('kde').'</td>'."\n";
  echo '<td> '.$vyp->get('kdy').'</td>'."\n";
  echo '<td> '.$vyp->get('path_large').'</td>'."\n";
  echo '</tr>';
}

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

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

Any idea? Thanks!

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

83 Posts

puschpull Reply #2, 14 years, 7 months ago

Simple drawing:

------------------------------------------------------------------
online check here
http://puschpull.org/modx_rev/test-dropdown/dropdown-new

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

64 Posts

Kristoffer Reply #3, 14 years, 7 months ago

Have you defined the relationship between the classes in your xPDO model? For more info see: http://rtfm.modx.com/display/xPDO20/Defining+Relationships
Then you should be able to use the method getCollectionGraph to retrieve your data. More info: http://rtfm.modx.com/display/xPDO20/getCollectionGraph

83 Posts

puschpull Reply #4, 14 years, 7 months ago

I make new Packages (pepish_join) from new tables with this schema

<?xml version="1.0" encoding="UTF-8"?>
<model package="pepish_join" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Category" table="category" extends="xPDOObject">
		<field key="category_id" dbtype="int" precision="10" phptype="integer" null="false" index="unique"  generated="native" />
		<field key="id" dbtype="smallint" precision="6" phptype="integer" null="false" index="pk" />
		<field key="name" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="name_alt" dbtype="varchar" precision="128" phptype="string" null="false" />
		<field key="parent_id" dbtype="smallint" precision="4" phptype="integer" null="true" />
		<field key="view" dbtype="smallint" precision="2" phptype="integer" null="false" default="1" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="id" length="" collation="A" null="false" />
		</index>
		<index alias="category_id" name="category_id" primary="false" unique="true" type="BTREE" >
			<column key="category_id" length="" collation="A" null="false" />
		</index>
	</object>
	<object class="Galerie" table="galerie" extends="xPDOObject">
		<field key="galerie_id" dbtype="int" precision="10" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="title" dbtype="varchar" precision="48" phptype="string" null="false" />
		<field key="name_cz" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="name_lat" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="kategorie" dbtype="smallint" precision="4" phptype="integer" null="false" index="index" />
		<field key="kde" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="kdy" dbtype="date" phptype="date" null="true" />
		<field key="upload_date_time" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP" />
		<field key="copyright" dbtype="varchar" precision="32" phptype="string" null="false" />
		<field key="permission" dbtype="smallint" precision="2" phptype="integer" null="false" />
		<field key="display" dbtype="smallint" precision="2" phptype="integer" null="false" />
		<field key="short_text" dbtype="mediumtext" phptype="string" null="true" />
		<field key="picture_name" dbtype="varchar" precision="48" phptype="string" null="true" />
		<field key="thumbs_name" dbtype="varchar" precision="48" phptype="string" null="true" />
		<field key="path_large" dbtype="varchar" precision="64" phptype="string" null="true" />
		<field key="path_thumbs" dbtype="varchar" precision="64" phptype="string" null="true" />

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

but the same tables
this SQL Query

SELECT galerie_id, name_cz, kategorie, name
FROM `copy_galerie` JOIN `copy_category` ON copy_galerie.kategorie = copy_category.id
WHERE copy_category.name = 'Vlci'
LIMIT 30

work fine (with adminer)

but in MODx not

  
  $f = $modx->newQuery('Galerie');
  $f->innerJoin('Category','Category', array("Galerie.kategorie = Category.category_id"));
  $f->where(array(
    //    'Category.name:LIKE' => 'Dravci',
    'kategorie:IN' => $seznam_kategorii
  ));
  $f->sortby('galerie_id','ASC');
//$f->limit('16');
  $vypis = $modx->getCollection('Galerie',$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 CZ</th><th>Name Latin</th><th>Kategorie</th><th>Místo</th><th>Datum snímku</th><th>Cesta k fotografii</th></tr>';

foreach ($vypis as $vyp) {
  echo '<tr>';
  echo '<td> '.$vyp->get('galerie_id').'</td>'."\n";
  echo '<td> '.$vyp->get('name_cz').'</td>'."\n";
  echo '<td> '.$vyp->get('name_lat').'</td>'."\n";
  echo '<td> '.$vyp->get('category.category_id').'</td>'."\n";
  echo '<td> '.$vyp->get('kde').'</td>'."\n";
  echo '<td> '.$vyp->get('kdy').'</td>'."\n";
  echo '<td> '.$vyp->get('path_large').'</td>'."\n";
  echo '</tr>';
}

echo '</table>

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

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

88 Posts

lazylegs Reply #5, 14 years, 7 months ago

Like Kristoffer said, you need to define the relationship in the schema and then build the maps from it. And the first link references directly to this topic.

Still example what you need to do here. I don't know which table would be the parent so you need to work that out.

# For join from Gategory to Galerie
<aggregate alias="Gallery" class="Galerie" local="category_id" foreign="kategorie" cardinality="one" owner="local" />

# Other way around
<aggregate alias="Category" class="Category" local="kategorie" foreign="category_id" cardinality="one" owner="foreign" />

If you define the join as <composite /> instead of aggregate, on the remove of the item on that table, the related items are removed from the other table

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

88 Posts

lazylegs Reply #6, 14 years, 7 months ago

Ah just noticed, you are missing $f->select(); You don't select any columns from the Category table.

To get the columns you want add this to query:

$f->select(array(
    $modx->getSelectColumns('Galerie', 'Galerie'),
    $modx->getSelectColumns('Category', 'Gategory', '', array('category_id')),
));

The xPDO reference to it: http://rtfm.modx.com/display/xPDO20/getSelectColumns
There are some forums posts from last weeks also related to this and if you scroll bit down on the next page: http://lazylegs.info/articles/subqueries-in-modx-revolution-using-xpdo/conclusion-and-good-to-know.html There is some explanation for it.

Almost retired from web-development industry but still randomly writing at Lazylegs.info and on schedule hopefully in near future to finish Oracle and PostgreSQL ports of MODX

83 Posts

puschpull Reply #7, 14 years, 7 months ago

lazylegs: Thanks!
:)

now i tested this code:

if($result){  
  $f = $modx->newQuery('Galerie');   
  
  $f->select(array(
    $modx->getSelectColumns('Galerie', 'Galerie', 'Galerie_'),
    $modx->getSelectColumns('Category', 'Category', 'Category_', array('category_id', 'name')),
));
  
  $f->innerJoin('Category','Category', array("Galerie.kategorie = Category.category_id")); 
  
  
  $f->where(array(
  'Category.name:LIKE' => 'Dravci',  // THIS WORKING FINE
    'kategorie:IN' => $seznam_kategorii
  ));
  $f->sortby('galerie_id','ASC');
  $vypis = $modx->getCollection('Galerie',$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 CZ</th><th>Name Latin</th><th>Kategorie</th><th>Místo</th><th>Datum snímku</th><th>Cesta k fotografii</th></tr>';

foreach ($vypis as $vyp) {
  echo '<tr>';
  echo '<td> '.$vyp->get('galerie_id').'</td>'."\n";
  echo '<td> '.$vyp->get('name_cz').'</td>'."\n";
  echo '<td> '.$vyp->get('name_lat').'</td>'."\n";
  echo '<td> '.$vyp->get('name').'</td>'."\n";   // HERE IS BLANK FIELDS !!!!?? AND  I WANT HERE CATEGORY.NAME 
  echo '<td> '.$vyp->get('kde').'</td>'."\n";
  echo '<td> '.$vyp->get('kdy').'</td>'."\n";
  echo '<td> '.$vyp->get('path_large').'</td>'."\n";
  echo '</tr>';
}

echo '</table>

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

Schema:

<?xml version="1.0" encoding="UTF-8"?>
<model package="pepish_join" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Category" table="category" extends="xPDOObject">
		<field key="category_id" dbtype="int" precision="10" phptype="integer" null="false" index="unique"  generated="native" />
		<field key="id" dbtype="smallint" precision="6" phptype="integer" null="false" index="pk" />
		<field key="name" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="name_alt" dbtype="varchar" precision="128" phptype="string" null="false" />
		<field key="parent_id" dbtype="smallint" precision="4" phptype="integer" null="true" />
		<field key="view" dbtype="smallint" precision="2" phptype="integer" null="false" default="1" />

		# Other way around
<aggregate alias="Category" class="Category" local="category_id" foreign="kategorie" cardinality="one" owner="foreign" />

	</object>
	<object class="Galerie" table="galerie" extends="xPDOObject">
		<field key="galerie_id" dbtype="int" precision="10" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="title" dbtype="varchar" precision="48" phptype="string" null="false" />
		<field key="name_cz" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="name_lat" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="kategorie" dbtype="smallint" precision="4" phptype="integer" null="false" index="index" />
		<field key="kde" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="kdy" dbtype="date" phptype="date" null="true" />
		<field key="upload_date_time" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP" />
		<field key="copyright" dbtype="varchar" precision="32" phptype="string" null="false" />
		<field key="permission" dbtype="smallint" precision="2" phptype="integer" null="false" />
		<field key="display" dbtype="smallint" precision="2" phptype="integer" null="false" />
		<field key="short_text" dbtype="mediumtext" phptype="string" null="true" />
		<field key="picture_name" dbtype="varchar" precision="48" phptype="string" null="true" />
		<field key="thumbs_name" dbtype="varchar" precision="48" phptype="string" null="true" />
		<field key="path_large" dbtype="varchar" precision="64" phptype="string" null="true" />
		<field key="path_thumbs" dbtype="varchar" precision="64" phptype="string" null="true" />

		# For join from Category to Galerie
<aggregate alias="Galerie" class="Galerie" local="kategorie" foreign="category_id" cardinality="one" owner="local" />

	</object>
</model>

Here is this code tested
http://puschpull.org/modx_rev/join
Select "Dravci" and Submit ("Odeslat")

Column "Kategoie" is blank
but with Query:

SELECT `Galerie`.`galerie_id` AS `Galerie_galerie_id`, `Galerie`.`title` AS `Galerie_title`, `Galerie`.`name_cz` AS `Galerie_name_cz`, `Galerie`.`name_lat` AS `Galerie_name_lat`, `Galerie`.`kategorie` AS `Galerie_kategorie`, `Galerie`.`kde` AS `Galerie_kde`, `Galerie`.`kdy` AS `Galerie_kdy`, `Galerie`.`upload_date_time` AS `Galerie_upload_date_time`, `Galerie`.`copyright` AS `Galerie_copyright`, `Galerie`.`permission` AS `Galerie_permission`, `Galerie`.`display` AS `Galerie_display`, `Galerie`.`short_text` AS `Galerie_short_text`, `Galerie`.`picture_name` AS `Galerie_picture_name`, `Galerie`.`thumbs_name` AS `Galerie_thumbs_name`, `Galerie`.`path_large` AS `Galerie_path_large`, `Galerie`.`path_thumbs` AS `Galerie_path_thumbs`, `Category`.`category_id` AS `Category_category_id`, `Category`.`name` AS `Category_name` FROM `copy_galerie` AS `Galerie` JOIN `copy_category` `Category` ON Galerie.kategorie = Category.category_id WHERE ( `Category`.`name` LIKE 'Dravci' AND `Galerie`.`kategorie` IN (21) ) ORDER BY galerie_id ASC

in mySQL receive correct result
in column `Category_name` is name of kategorie

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

64 Posts

Kristoffer Reply #8, 14 years, 7 months ago

For each class in your schema, you have to define in which way this class is related to the other classes in your model. In order to to do this, you first have to decide the hierarchy among your classes.
In your example, each Category can have multiple Galleries and each Gallery can only have one Category. Right?

Then you can define these relationships in your model. I can't really follow your model so I'm using a simplified one to show the technique.

<?xml version="1.0" encoding="UTF-8"?>
<model package="pepish_join" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
    <object class="Category" table="category" extends="xPDOObject">
        <field key="category_id" dbtype="int" precision="10" phptype="integer" null="false" index="pk" />
        <field key="name" dbtype="varchar" precision="64" phptype="string" null="false" />
 
        # Define relation to Galleries
<aggregate alias="Galleries" class="Galerie" local="category_id" foreign="kategorie" cardinality="many" owner="local" />
    </object>

    <object class="Galerie" table="galerie" extends="xPDOObject">
        <field key="galerie_id" dbtype="int" precision="10" phptype="integer" null="false" index="pk" />
        <field key="title" dbtype="varchar" precision="48" phptype="string" null="false" />
        <field key="kategorie" dbtype="int" precision="10" phptype="integer" null="false" index="index" />
 
        # Define relation to Category
<aggregate alias="Category" class="Category" local="kategorie" foreign="category_id" cardinality="one" owner="foreign" />
    </object>
</model>

Now you can use this relations in your code.

$f = $modx->newQuery('Galerie');
$f->where(array(
  'Category.name:LIKE' => 'Dravci'
));
$f->sortby('Galerie.galerie_id','ASC');
$vypis = $modx->getCollectionGraph('Galerie','{"Category":{}}',$f);
     
foreach ($vypis as $vyp) {
  $out = $vyp->toArray();
  $out[category_name] = $vyp->Category->get('name');

  Do something with the data, for example parse a chunk with a template for one row in a table.
}

83 Posts

puschpull Reply #9, 14 years, 7 months ago

Kristoffer: thanks, but i'm not in good condition think about ...

I tested your code, but always have problem .
I make pictures diagram with structure my data.
I need simple and understandable sample with my data and my table structure.
:)

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

83 Posts

puschpull Reply #10, 14 years, 7 months ago

Summary of my current problem:

Two tables

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

DROP TABLE IF EXISTS `aa_gallery`;
CREATE TABLE `aa_gallery` (
  `gal_id` int(10) NOT NULL auto_increment,
  `title` varchar(48) NOT NULL,
  `name_cz` varchar(64) NOT NULL,
  `name_lat` varchar(64) NOT NULL,
  `kategorie` int(10) NOT NULL,
  `kde` varchar(64) NOT NULL,
  `kdy` date default NULL,
  `upload_date_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `copyright` varchar(32) NOT NULL,
  `permission` smallint(2) NOT NULL,
  `display` smallint(2) NOT NULL,
  `short_text` mediumtext,
  `picture_name` varchar(48) default NULL,
  `thumbs_name` varchar(48) default NULL,
  `path_large` varchar(64) default NULL,
  `path_thumbs` varchar(64) default NULL,
  PRIMARY KEY  (`gal_id`),
  KEY `kategorie` (`kategorie`),
  CONSTRAINT `aa_gallery_ibfk_1` FOREIGN KEY (`kategorie`) REFERENCES `aa_category` (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

DROP TABLE IF EXISTS `aa_category`;
CREATE TABLE `aa_category` (
  `cat_id` int(10) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL,
  `name_alt` varchar(128) NOT NULL,
  `parent_id` int(10) default NULL,
  `view` smallint(2) NOT NULL,
  PRIMARY KEY  (`cat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Relations:

New Package generated with CreateXpdoClasses
(from here: http://bobsguides.com/custom-db-tables.html )

Package name:
pp_gal

b]CreateXpdoClasses[/b] generated this Schema:
pp_gal.mysql.schema.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <model package="pp_gal" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
    <object class="Category" table="category" extends="xPDOObject">
    <field key="cat_id" dbtype="int" precision="10" phptype="integer" null="false" index="pk" generated="native" />
    <field key="name" dbtype="varchar" precision="64" phptype="string" null="false" />
    <field key="name_alt" dbtype="varchar" precision="128" phptype="string" null="false" />
    <field key="parent_id" dbtype="int" precision="10" phptype="integer" null="true" />
    <field key="view" dbtype="smallint" precision="2" phptype="integer" null="false" />
     
    <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
    <column key="cat_id" length="" collation="A" null="false" />
    </index>
    </object>
    <object class="Gallery" table="gallery" extends="xPDOObject">
    <field key="gal_id" dbtype="int" precision="10" phptype="integer" null="false" index="pk" generated="native" />
    <field key="title" dbtype="varchar" precision="48" phptype="string" null="false" />
    <field key="name_cz" dbtype="varchar" precision="64" phptype="string" null="false" />
    <field key="name_lat" dbtype="varchar" precision="64" phptype="string" null="false" />
    <field key="kategorie" dbtype="int" precision="10" phptype="integer" null="false" index="index" />
    <field key="kde" dbtype="varchar" precision="64" phptype="string" null="false" />
    <field key="kdy" dbtype="date" phptype="date" null="true" />
    <field key="upload_date_time" dbtype="timestamp" phptype="timestamp" null="false" default="CURRENT_TIMESTAMP" />
    <field key="copyright" dbtype="varchar" precision="32" phptype="string" null="false" />
    <field key="permission" dbtype="smallint" precision="2" phptype="integer" null="false" />
    <field key="display" dbtype="smallint" precision="2" phptype="integer" null="false" />
    <field key="short_text" dbtype="mediumtext" phptype="string" null="true" />
    <field key="picture_name" dbtype="varchar" precision="48" phptype="string" null="true" />
    <field key="thumbs_name" dbtype="varchar" precision="48" phptype="string" null="true" />
    <field key="path_large" dbtype="varchar" precision="64" phptype="string" null="true" />
    <field key="path_thumbs" dbtype="varchar" precision="64" phptype="string" null="true" />
     
    <index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
    <column key="gal_id" length="" collation="A" null="false" />
    </index>
    <index alias="kategorie" name="kategorie" primary="false" unique="false" type="BTREE" >
    <column key="kategorie" length="" collation="A" null="false" />
    </index>
    </object>
    </model>

With this SQL Query:

SELECT `Gallery`.`gal_id` AS `Gallery_gal_id`, `Gallery`.`title` AS `Gallery_title`, `Gallery`.`name_cz` AS `Gallery_name_cz`, `Gallery`.`name_lat` AS `Gallery_name_lat`, `Gallery`.`kategorie` AS `Gallery_kategorie`, `Gallery`.`kde` AS `Gallery_kde`, `Gallery`.`kdy` AS `Gallery_kdy`, `Gallery`.`upload_date_time` AS `Gallery_upload_date_time`, `Gallery`.`copyright` AS `Gallery_copyright`, `Gallery`.`permission` AS `Gallery_permission`, `Gallery`.`display` AS `Gallery_display`, `Gallery`.`short_text` AS `Gallery_short_text`, `Gallery`.`picture_name` AS `Gallery_picture_name`, `Gallery`.`thumbs_name` AS `Gallery_thumbs_name`, `Gallery`.`path_large` AS `Gallery_path_large`, `Gallery`.`path_thumbs` AS `Gallery_path_thumbs`, `Category`.`cat_id` AS `Category_cat_id`, `Category`.`name` AS `Category_name` FROM `aa_gallery` AS `Gallery` JOIN `aa_category` `Category` ON Gallery.kategorie = Category.cat_id WHERE ( `Category`.`name` LIKE 'Dravci' AND `Gallery`.`kategorie` IN (21) ) ORDER BY gal_id ASC

is my result OK

...but in MODx snippet not !!
http://puschpull.org/modx_rev/join
please select "Dravci" and confirm "Odeslat"
Column Kategorie is empty

Code of this Snippet:

<?php
function getTree($parent) {  
  global $modx;
    $path = MODX_CORE_PATH . 'components/pp_gal/';
    $result = $modx->addPackage('pp_gal',$path . 'model/','aa_');  
  if($result){
  $g = $modx->newQuery('Category');
  $g->where(array(
    'parent_id' => $parent,
));
  $trees = $modx->getCollection('Category',$g);
}  
$pole = array();  
foreach ($trees as $tre) {
	$pole[] = $tre->get('cat_id');
	$pole = array_merge($pole, getTree($tre->get('cat_id')));
}
return $pole;
}




$path = MODX_CORE_PATH . 'components/pp_gal/';
$result = $modx->addPackage('pp_gal',$path . 'model/','aa_');  

echo '<form action="[[~[[*id]]]]" method="post" class="form">
<select name="sel_cat" size="1">';

if($result){
  $d = $modx->newQuery('Category');
  $d->where(array(
    'view' => '1',
));
  $d->orCondition(array(
   'view' => '2',
));
  $d->sortby('cat_id','ASC');
//$d->limit('16');

  $seznam = $modx->getCollection('Category',$d);
}

foreach ($seznam as $sez) {
	echo '<option value='.$sez->get('cat_id').'>'.$sez->get('name').'</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_cat'])) $_POST['sel_cat'] = '0';
$a = ($_POST['sel_cat']);



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

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

foreach ($seze as $se) {
	echo 'Vybraná kategorie je: '.$se->get('name').'<hr>';
}


$iii = $a;
$seznam_kategorii = getTree($iii);
$seznam_kategorii[] = $iii;
$seznam_kategorii_retezec = '\''.implode('\',\'', $seznam_kategorii).'\'';
echo 'Seznam kategorií celé vybrané větve:  <br>'.$seznam_kategorii_retezec.'<br><hr>';







if($result){

  
  $f = $modx->newQuery('Gallery');
  
  
   

  $f->select(array(
    $modx->getSelectColumns('Gallery', 'Gallery', 'Gallery_'),
    $modx->getSelectColumns('Category', 'Category', 'Category_', array('cat_id', 'name')),
));  
  $f->innerJoin('Category','Category', array("Gallery.kategorie = Category.cat_id")); 


  /*
  $f->select($modx->getSelectColumns('Gallery', 'Gallery', 'Gallery_',
    array(
      'gal_id',
      'name_cz',
      'name_lat',
      'kategorie',
      'kde',
      'kdy',
      'path_large',
    )));
  $f->select($modx->getSelectColumns('Category', 'Category', 'Category_',
    array(
      'cat_id',
      'name',
    )));
*/
  
  $f->where(array(
    'Category.name:LIKE' => 'Dravci',
//  'kategorie:LIKE' => '21',
    'kategorie:IN' => $seznam_kategorii
  ));
  $f->sortby('gal_id','ASC');
//$f->limit('16');
  $vypis = $modx->getCollection('Gallery',$f);
  //$vypis = $modx->getCollectionGraph('Gallery','{"Category":{}}',$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 CZ</th><th>Name Latin</th><th>Kategorie</th><th>Místo</th><th>Datum snímku</th><th>Cesta k fotografii</th></tr>';



foreach ($vypis as $vyp) {
//   $out = $vyp->toArray();
//   $out[category_name] = $vyp->Category->get('name');

  echo '<tr>';
  echo '<td> '.$vyp->get('gal_id').'</td>'."\n";
  echo '<td> '.$vyp->get('name_cz').'</td>'."\n";
  echo '<td> '.$vyp->get('name_lat').'</td>'."\n";
  echo '<td> '.$vyp->get('name').'</td>'."\n";
  echo '<td> '.$vyp->get('kde').'</td>'."\n";
  echo '<td> '.$vyp->get('kdy').'</td>'."\n";
  echo '<td> '.$vyp->get('path_large').'</td>'."\n";
  echo '</tr>';
}

echo '</table>

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

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

I test and "laborate" with this two line in XML schema

<aggregate alias="Gallery" class="Gallery" local="kategorie" foreign="name" cardinality="one" owner="foreign" />
<aggregate alias="category" class="Category" local="cat_id" foreign="name" cardinality="many" owner="local" />

but i don't know ..

quantity of combination is large
and method "attempt / mistake" is very very time exacting ....

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