Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » How to filter by a parent field in a link1 transaction
How to filter by a parent field in a link1 transaction [message #1486] Wed, 16 July 2008 13:24 Go to next message
bonzo_bcn is currently offline  bonzo_bcn
Messages: 152
Registered: June 2008
Senior Member
I have three tables:
team
team_player_xref
player

In class team_player_xref, I need to access a field value of team in order to modify the $where clause.

I tried with _cm_get_predata but at that point there is not parent data.

How can I do it?

[Updated on: Wed, 16 July 2008 13:57]

Report message to a moderator

Re: How to filter by a parent field in a link1 transaction [message #1487 is a reply to message #1486] Wed, 16 July 2008 14:57 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
Senior Member
Which 'where' string are you trying to modify - the one that goes into $dbouter, the one that goes into $dblink, or the one that comes out of $dblink to go to a child form?

Which table do you have as $dbouter?
Which table do you have as $dblink?


Re: How to filter by a parent field in a link1 transaction [message #1488 is a reply to message #1486] Wed, 16 July 2008 15:21 Go to previous messageGo to next message
bonzo_bcn is currently offline  bonzo_bcn
Messages: 152
Registered: June 2008
Senior Member
those questions are too advanced for my current level in radicore Smile I just wanted to modify the $where that is passed as parameter to _cm_pre_getData.

I will explain what I try to do because maybe there is another aproach:
I have a list1 of teams, from there I have a link1 transaction to team_player_xref, so I can assign players to the team.

I need to filter the players that are shown in the link1 transaction so that only the players that by age are in the category of the team are shown.

(A team has a category_id which links to a category table. The category table has this fields: category_id,date1,date2)
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 Go to previous messageGo to next message
AJM is currently offline  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.


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 Go to previous messageGo to next message
bonzo_bcn is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  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.


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 Go to previous messageGo to next message
bonzo_bcn is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  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.


Re: How to filter by a parent field in a link1 transaction [message #1499 is a reply to message #1486] Thu, 17 July 2008 17:09 Go to previous messageGo to next message
bonzo_bcn is currently offline  bonzo_bcn
Messages: 152
Registered: June 2008
Senior Member
Ok, I understand.

So now I added this code
    function _cm_getPkeyNames ($pkey_array, $task_id, $pattern_id=null)
    // perform custom processing before the selection string is passed to another form.
    {
        $pkey_array[] = 'fecini';       // append to array



        return $pkey_array;

    } // _cm_getPkeyNames

to class team but the resulting sql in teamp_player_xref still hasn't the field fecini (allthough it's selected when accessing team).
Re: How to filter by a parent field in a link1 transaction [message #1500 is a reply to message #1499] Thu, 17 July 2008 18:10 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
Senior Member
What sql is being generated? What sql should be generated?

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 Go to previous messageGo to next message
bonzo_bcn is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  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


Re: How to filter by a parent field in a link1 transaction [message #1503 is a reply to message #1486] Fri, 18 July 2008 05:25 Go to previous messageGo to next message
bonzo_bcn is currently offline  bonzo_bcn
Messages: 152
Registered: June 2008
Senior Member
I only added this code to test:
 $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);


And it's generating a wrong FROM clause:
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) 
[B]part_equipo_xref LEFT JOIN participante ON (participante.participante_id=part_equipo_xref.participante_id)[/B]

The code in bold wasn't included in the originally generated sql.
Re: How to filter by a parent field in a link1 transaction [message #1504 is a reply to message #1503] Fri, 18 July 2008 06:07 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2363
Registered: April 2006
Location: Surrey, UK
Senior Member
Whoops, my mistake. Embarassed The link object requires special processing which is bypassed with the code I gave you. You should change the line
$this->sql_from    = $this->_sqlForeignJoin($this->sql_select, $this->sql_from, $this->parent_relations);

to
$where_str = $this->_sqlAssembleWhere($where, $where_array);

This should generate the correct sql statement, which you can then modify.


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 Go to previous messageGo to next message
bonzo_bcn is currently offline  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 Go to previous message
AJM is currently offline  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.


Previous Topic: override noedit on foreign field in multi2
Next Topic: How ro remove the 'page created in' message
Goto Forum:
  


Current Time: Thu Oct 31 20:00:19 EDT 2024

Total time taken to generate the page: 0.09654 seconds