|
|
|
Re: How to filter by a parent field in a link1 transaction [message #1490 is a reply to message #1488] |
Wed, 16 July 2008 16:30 |
AJM
Messages: 2363 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
It is a simple question. The LINK1 pattern contains 2 tables (entities) called $dbouter and $dblink, so in which _cm_pre_getData() method are you trying to modify the 'where' string - in $dbouter or $dblink?
It sounds like you have 'team' as $dbouter and 'team_player_xref' as $dblink, and $dblink then joins with 'player'.
By default the 'where' string which is extracted from $dbouter and passed to $dblink is the primary key of the database row in $dbouter. If you wish to pass an extra value to $dblink called 'date' then the first step you need to take is to obtain a value for 'date'. Your attempt to use the _cm_pre_getData() method in $dbouter is totally wrong as you haven't even read the 'team' record yet. You can either put code in the _cm_post_getData() method to read the 'category' table AFTER you have obtained a value for category_id from the 'team' record, or you can read the 'category' table AT THE SAME TIME as reading the 'team' table by building an sql SELECT statement along the lines of
SELECT ... FROM team LEFT JOIN category USING (category_id)
You can have the framework do this automatically for you by modifying the relationship between 'category' and 'team' to specify which fields are to be returned from the 'category' table whenever the 'team' table is read. Please refer to http://www.tonymarston.net/php-mysql/menuguide/dict_relation ship(upd).html and http://www.tonymarston.net/php-mysql/data-dictionary.html#sq l.joins for details.
To have the value for 'date' included in the 'where' string which is extracted from $dbouter and passed to $dblink you need to make use of the _cm_filterWhere() method.
As the value for 'date' is obtained from the 'category' table where 'category_id' is obtained from the 'team' table, you could even put your code in the _cm_pre_getData() method of $dblink before you build the sql SELECT statement.
Once you have got this far you need to look at the sql SELECT statement which is issued by $dblink - either by stepping through with your debugger or using the facilities described in http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq51. Then you need to identify what changes need to be made to this statement in order to achieve the result that you want, then make the changes as described in either http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq08 or http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq84.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: How to filter by a parent field in a link1 transaction [message #1495 is a reply to message #1486] |
Thu, 17 July 2008 15:15 |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
Thanks for your help.
By modifying the relationship I could retrieve only one date, but in this case I need two dates from the category table, to check if the birth date is between those two.
So maybe I can modify the relationship to include date1, then in _cm_get_predata(), as I allready have the join with category I can just add the piece of code to the where clause.
Quote: | you can either put code in the _cm_post_getData() method to read the 'category' table AFTER you have obtained a value for category_id from the 'team' record
|
Correct me if I'm wrong, but at this point I could add data, but would it be possible to remove from the read records the ones that don't meet the criteria player.birthdate between category.date1 and category.date2?
[Updated on: Thu, 17 July 2008 15:24] Report message to a moderator
|
|
|
Re: How to filter by a parent field in a link1 transaction [message #1496 is a reply to message #1495] |
Thu, 17 July 2008 15:49 |
AJM
Messages: 2363 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
bonzo_bcn wrote on Thu, 17 July 2008 20:15 | By modifying the relationship I could retrieve only one date
|
Why only one date? If you set 'Parent Field' to 'CALCULATED' you can put as many field names as you like into 'Calculated Field'.
Quote: | would it be possible to remove from the read records the ones that don't meet the criteria player.birthdate between category.date1 and category.date2?
|
Yes. Simply iterate through the $rows variable and remove the ones you don't want. This might not be a good idea though as the number of rows displayed would not be the same as the number of rows retrieved, and this could mess up your pagination. If would be *FAR* better to do all the necessary filtering in the sql SELECT statement.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
Re: How to filter by a parent field in a link1 transaction [message #1497 is a reply to message #1486] |
Thu, 17 July 2008 15:58 |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
So in calculated fields I can put 'fecini,fecfin' so that it retrieves two fields? I thought only one filed was possible.
I've defined the relationship between team and category to retrieve fecini, so now in the team_player_xref(link1).php.sql I have:
SELECT SQL_CALC_FOUND_ROWS equipo.*, categoria.fecini
FROM equipo LEFT JOIN categoria ON (categoria.categoria_id=equipo.categoria_id)
WHERE ( equipo.equipo_id='2' ) AND equipo.entidad_id=2 LIMIT 1 OFFSET 0 =>Count=1
SELECT SQL_CALC_FOUND_ROWS equipo.equipo_id, participante.participante_id,
CONCAT_WS(' ',participante.catsalut, participante.nombre, participante.apellido1,participante.apellido2) AS part_det,
CASE WHEN part_equipo_xref.participante_id IS NULL THEN 'F' ELSE 'T' END AS selected
FROM equipo CROSS JOIN participante
LEFT JOIN part_equipo_xref ON (equipo.equipo_id=part_equipo_xref.equipo_id
AND participante.participante_id=part_equipo_xref.participante_id)
WHERE equipo.equipo_id='2' AND participante.participante_id IN (SELECT participante_id FROM part_ent_xref WHERE entidad_id=2) LIMIT 10 OFFSET 0 =>Count=1
as you can see categoria.fecini is not in the final SQL of part_equipo_xref(link1).php.sql, I included this in class team_player_xref:
function _cm_filterWhere ($array=null)
// identify field names which are NOT to be filtered out of a $where string.
{
$array[] = 'fecini';
return $array;
} // _cm_filterWhere
How can I make fecini appear in the sql? I read the docs you pointed me to but didn't find it out.
[Updated on: Thu, 17 July 2008 16:45] Report message to a moderator
|
|
|
Re: How to filter by a parent field in a link1 transaction [message #1498 is a reply to message #1497] |
Thu, 17 July 2008 16:49 |
AJM
Messages: 2363 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
If you have obained a value for 'fecini' in $dbouter then you need to modify the primary key which is extracted from $dbouter before it is passed to $dblink. If you look in file 'std.link1.inc' you will see that this is performed at line 217 with the following code:
// get primary key of current row retrieved from OUTER entity
$pkeynames = $dbouter->getPkeyNamesAdjusted();
$where = array2where($outer_data, $pkeynames, $dbouter);
This calls the _cm_getPkeyNames() method to make any adjustments, so you should use this instead of _cm_filterWhere() which is used by the RECEIVING object not the GENERATING object. You should also use 'fecini' for the field name instead of 'categoria.fecini' as all field names returned by an sql SELECT statement are unqualified.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
Re: How to filter by a parent field in a link1 transaction [message #1501 is a reply to message #1486] |
Fri, 18 July 2008 02:19 |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
this is being generated:
SELECT SQL_CALC_FOUND_ROWS equipo.equipo_id, participante.participante_id, participante.catsalut, participante.nombre, participante.apellido1, participante.apellido2, CASE WHEN part_equipo_xref.participante_id IS NULL THEN 'F' ELSE 'T' END AS selected
FROM equipo CROSS JOIN participante
LEFT JOIN part_equipo_xref ON (equipo.equipo_id=part_equipo_xref.equipo_id AND participante.participante_id=part_equipo_xref.participante_i d)
WHERE equipo.equipo_id='2' AND participante.participante_id IN (SELECT participante_id FROM part_ent_xref WHERE entidad_id=2) LIMIT 10 OFFSET 0 =>Count=1
and this should be generated
SELECT SQL_CALC_FOUND_ROWS equipo.equipo_id, categoria.fecini, categoria.fecfin, participante.participante_id, participante.catsalut, participante.nombre, participante.apellido1, participante.apellido2, CASE WHEN part_equipo_xref.participante_id IS NULL THEN 'F' ELSE 'T' END AS selected
FROM equipo CROSS JOIN participante
LEFT JOIN part_equipo_xref ON (equipo.equipo_id=part_equipo_xref.equipo_id AND participante.participante_id=part_equipo_xref.participante_i d)
LEFT JOIN categoria ON (categoria.categoria_id=equipo.categoria_id)
WHERE equipo.equipo_id='2' AND participante.participante_id IN (SELECT participante_id FROM part_ent_xref WHERE entidad_id=2) LIMIT 10 OFFSET 0 =>Count=1
|
|
|
Re: How to filter by a parent field in a link1 transaction [message #1502 is a reply to message #1501] |
Fri, 18 July 2008 05:05 |
AJM
Messages: 2363 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
That SELECT statement is being constructed from within 'part_equipo_xref', so it can only include automatic JOINs for relationships where 'part_equipo_xref' is the child.
If you wish to modify the SELECT statement which is constructed automatically then you need to follow the instructions at http://www.tonymarston.net/php-mysql/infrastructure-faq.html #faq84, as in the following example:
function _cm_pre_getData ($where, $where_array, $fieldarray=null)
{
// construct default SELECT and FROM clauses using parent relations
$this->sql_select = null;
$this->sql_from = null;
$this->sql_groupby = null;
$this->sql_having = null;
$this->sql_from = $this->_sqlForeignJoin($this->sql_select, $this->sql_from, $this->parent_relations);
// add code to obtain facility_name
$this->sql_select .= ', categoria.fecini, categoria.fecfin';
$this->sql_from .= ' LEFT JOIN categoria ON (categoria.categoria_id=equipo.categoria_id)';
return $where;
} // _cm_pre_getData
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
Re: How to filter by a parent field in a link1 transaction [message #1505 is a reply to message #1486] |
Fri, 18 July 2008 06:17 |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
Thant worked great if I only select one team, but in the case I select two teams and open the link1 transaction, the first team is shown ok, but when I click the 'next' link I get an error, because the sql statement seems to be different:
Fatal Error: MySQL error: 1054 - Unknown column 'equipo.categoria_id' in 'on clause' (# 1054).
SQL query: SELECT SQL_CALC_FOUND_ROWS part_equipo_xref.*, participante.catsalut, participante.nombre,
participante.apellido1, participante.apellido2,
categoria.fecini, categoria.fecfin
FROM part_equipo_xref LEFT JOIN participante ON
(participante.participante_id=part_equipo_xref.participante_id)
LEFT JOIN categoria ON (categoria.categoria_id=equipo.categoria_id)
WHERE part_equipo_xref.participante_id IN (SELECT participante_id FROM part_ent_xref WHERE entidad_id=2)
AND participante.fecnac between categoria.fecini
AND categoria.fecfin LIMIT 10 OFFSET 0
At this pont it doesn't perform the join with 'equipo'.
|
|
|
Re: How to filter by a parent field in a link1 transaction [message #1506 is a reply to message #1505] |
Fri, 18 July 2008 06:33 |
AJM
Messages: 2363 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
The code inside the _cm_pre_getData() method should be inside the following condition:
if (empty($this->sql_from)) {
// construct default SELECT and FROM clauses using parent relations
$this->sql_select = null;
$this->sql_from = null;
$this->sql_groupby = null;
$this->sql_having = null;
$where_str = $this->_sqlAssembleWhere($where, $where_array);
// add code to obtain category data
$this->sql_select .= ', categoria.fecini, categoria.fecfin';
$this->sql_from .= ' LEFT JOIN categoria ON (categoria.categoria_id=equipo.categoria_id)';
} // if
I shall have to amend my FAQ accordingly.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|