5 replies to this topic

[Cris D.]

I noticed in the index.php file the multi-table abstract capability for the getIntTableRows() function.

However, the sql query generated using this API generated an error:

Quote

...print_r($from);
Array ( [0] => etogal [1] => testtable1 [2] => testtable2 )...

« Execution of a query to the database failed »
SQL: SELECT * FROM `deagcr_etomite`.etomite_etogal, `deagcr_etomite`.etomite_testtable1, `deagcr_etomite`.etomite_testtable2 WHERE id=1 ORDER BY id ASC LIMIT 1;

After looking at the API code it looks as though this (above) is what is supposed to happen, (adding the table names in a string for each instance)...

// added multi-table abstraction capability
	if(is_array($from)) {
	  $tbl = "";
	  foreach ($from as $_from) $tbl .= $this->db.$_from.", ";
	  $tbl = substr($tbl,0,-2);
	} else {
	  $tbl = ($this->dbConfig['table_prefix'] != ''
			  && strpos($from,$this->dbConfig['table_prefix']) === 0
			  || !$addPrefix)
			  ? $this->dbConfig['dbase'].".".$from
			  : $this->db.$from;
	}

Handjamming the query into PHPMyAdmin used the following query:

Quote

SELECT `etomite_etogal`.*, `etomite_testtable1`.*, `etomite_testtable2`.*
FROM etomite_etogal, etomite_testtable1, etomite_testtable2
WHERE ((`etomite_etogal`.* id=1) AND (`etomite_testtable1`.* id=1) AND (`etomite_testtable2`.* id=1))

As you can see, the SELECT fields="*", FROM , and WHERE clauses are appended to each table instance instead of a string of tables with a generic SQL query being applied to all .

I was wondering if anyone else had successfully used this API for multi-table selection, and if so, what am I doing wrong?

[Ralph]

This is one of those features I wrote into the API quite some time ago and was just recently thinking about testing the overall functionality... I know it worked when it was implemented, but I'll need to play with it a bit to refresh my memory... I just had the same issue with my form class yesterday and will also be sorting that out over the weekend so I can add some documentation...

[Cris D.]

Any Progress on this one? Don't feel pressured (just asking).

[Ralph]

Cris D., on Jun 1 2007, 06:10 PM, said:

Any Progress on this one? Don't feel pressured (just asking).

I haven't checked yet but thanks for the reminder...

[Cris D.]

I've been doing some testing with this API and have documented some limitations and requirements in using it for multi-table calls:

• $from must be called as an array like: $from=array(table1,table2,table3);
  
• It will return all records from all tables in one array per row ie all records from row 1 from all tables will be merged into a single array, then all records from row 2 etc.
  
• If two tables have the same column (key) name, the last called value overwrites the previous value in the array.
  
• $sort can only be set if it refers to a unique column name from all tables called (can not be a shared name).
  
• $dir can only be set if a valid $sort field has been set.
  
• $fields must be set to return ALL fields as: $fields="*" Otherwise the FULL table name needs to be passed in a comma separated list in this format: "database.etoprefix_tablename .colname" for each column required.
  
• If all fields are required for any table, the * can still be applied in place of the column name in the list.
  
• You can use $where only if a single table ($from) is being passed.
  
• With this API, the table that returns the largest number of records (or $limit) will determine the number of records to be returned.
  
• Results from tables with fewer rows will be used to pad the empty array fields with repeating values if the number of records being returned are not equal for all tables.

Therefore, if you want your multiple table results with all fields to be merged into a single array per row that overwrites previous values and includes repeating data from the tables with fewer rows and replaces values with keys of the same name (quite likely only a specialized use for this result), it is probably easier to make two calls and manually construct your arrays inside the snippet.
[edited 5-9-07 typo]

Edited by Cris D., 05 September 2007 - 12:22 PM.

[Ralph]

This feature, due to the overall limitations and complexities, has probably never been used by anyone other than me when I was adding the functionality... And during that time I was only able to do limited testing... I wouldn't recommend using this feature in submitted snippets due to these limitations... And although there will be many other changes in the Cocoon code base, this feature will be removed... I'd recommend using a standard query for multi-table data abstractions...