How to make a list2 on a non existing child table [message #1905] |
Tue, 09 December 2008 06:59 ![Go to next message Go to next message](/theme/default/images/down.png) |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
I have a list1 of 'clients' and I want to make a list2 transaction that shows, for every selected client, the detail of an amount they have to pay.
The amount to pay is a union of different tables. I've created a class, extended the sql, defined the relationship etc.
What it's not working is that when I select a client from the list1 screen and click on the button of the list2 screen, the list 2 transaction shows all the clients, and for every client, the detail is not filtered, so for every client I see the detail of all the clients.
I can't seem to figure out where the problem is, any ideas? Is what I'm trying to achieve possible with Radicore?
|
|
|
Re: How to make a list2 on a non existing child table [message #1906 is a reply to message #1905] |
Tue, 09 December 2008 07:45 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Anything is possible, but you need to know how Radicore works in order to know when it will do what you want as standard, or will need some custom code somewhere.
The description of a LIST2 pattern clearly states that after retrieving a single row from the OUTER table it will retrieve all associated rows from the INNER table. It does this by extracting the primary key of the OUTER row and passing it to the INNER table in the $where string. This assumes that the primary key of OUTER is also used as a foreign key on INNER. It will use this string in the getData() method to populate its data array, and the contents of this data array will be loaded into the screen.
If there are too may records being displayed then you need to adjust the $where string accordingly, or manually adjust the contents of the data array.
It is also possible to skip the getData() altogether and populate the data array manually.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
Re: How to make a list2 on a non existing child table [message #1909 is a reply to message #1908] |
Tue, 09 December 2008 08:35 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
The framework isn't corrupting the sql statement it's just that the function extractTableNames returns an incorrect value:'SELECT', and therefore the records aren't filtered.
This is the correct statement, the sql statement that I want to construct is:
SELECT entidad_id, concepto, sum(importe) as importe
FROM
(SELECT entidad.entidad_id ,'Esportistes' as concepto, 1 as importe
FROM participante
JOIN part_equipo_xref ON participante.participante_id=part_equipo_xref.participante_id
JOIN equipo ON part_equipo_xref.equipo_id = equipo.equipo_id
JOIN entidad ON equipo.entidad_id= entidad.entidad_id
JOIN temporada ON temporada.temporada_id = equipo.temporada_id
WHERE tippart='E'
SELECT entidad.entidad_id ,'Equips' , 1
FROM participante
JOIN equipo ON participante.participante_id = equipo.delegado_id
JOIN entidad ON equipo.entidad_id= entidad.entidad_id
JOIN temporada ON temporada.temporada_id = equipo.temporada_id
UNION ALL
SELECT entidad.entidad_id ,'Entitat', 1
FROM entidad) as entidad_s02
[Updated on: Tue, 09 December 2008 08:36] Report message to a moderator
|
|
|
|
Re: How to make a list2 on a non existing child table [message #1911 is a reply to message #1905] |
Tue, 09 December 2008 10:15 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
bonzo_bcn
Messages: 152 Registered: June 2008
|
Senior Member |
|
|
Thanks,I'm getting closer, so in my inner class table I have:
function _cm_pre_getData ($where, $where_array, $fieldarray=null){
$this->skip_getdata = true;
$this->fieldarray= $this->getData_raw($where);
return $where;
}
and it works great, for every parent it displays the correct child records.
The only problem now is that in the list1 screen, if I only select one parent and click on the list2 button, it retrieves all the parent records instead of retrieving only the selected one.
The generated .sql file has the correct sql statement though.
Do you know where the problem could be?
Thanks!
[Updated on: Tue, 09 December 2008 10:19] Report message to a moderator
|
|
|
|
|
Re: How to make a list2 on a non existing child table [message #1914 is a reply to message #1913] |
Tue, 09 December 2008 11:31 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
If you select several rows in the LIST1 task and then activate a LIST2 task then the LIST2 will show the selected rows in the OUTER area, one row at a time. The INNER area of the LIST2 will only show those rows which are related to the current row in the OUTER area.
When you say the .sql file is correct which one are you talking about? The one in the LIST1 task? The OUTER table for the LIST2 task? Or the INNER table of the LIST2 task?
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
Re: How to make a list2 on a non existing child table [message #1921 is a reply to message #1919] |
Tue, 09 December 2008 19:39 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
You haven't answered the question. I do not want to know what the sql statement is, but the result when it is run on your database. When you run that same statement through your MySQL client program (such as phpMyAdmin) what result does it display? How many rows?
It it produces only a single row then that is your problem. If it produces multiple rows then you will have to step through with your debugger to see why the outer area is not showing the scrolling hyperlinks. All the tests that I run on my code work as expected.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Re: How to make a list2 on a non existing child table [message #1925 is a reply to message #1922] |
Wed, 10 December 2008 04:58 ![Go to previous message Go to previous message](/theme/default/images/up.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
The scrolling area is set up in an XSL stylesheet, not with PHP code, but it uses two particular values which are set within script 'std.list2.inc' at line 223:
// set variables to be included in XML output
$scrolling[$dbouter->getClassName()]['curitem'] = $dbouter->getPageNo();
$scrolling[$dbouter->getClassName()]['lastitem'] = $dbouter->getLastPage();
If both 'curitem' and 'lastitem' are set to 1 then there is only a single item to display, so there will be no need to any scrolling hyperlinks. If your code reads 7 records from the database then 'lastitem' shoud be set to 7.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|