Home » RADICORE » How To » How to filter by a parent field in a link1 transaction
|
|
|
|
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 #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 #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: 2382 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
|
|
|
|
|
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'.
|
|
|
|
Goto Forum:
Current Time: Fri Jul 04 07:12:45 EDT 2025
Total time taken to generate the page: 0.01924 seconds
|