Radicore Forum: How To » How to make a list2 on a non existing child table

Home » RADICORE » How To » How to make a list2 on a non existing child table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to make a list2 on a non existing child table [message #1905]

Tue, 09 December 2008 06:59

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?

Report message to a moderator

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

AJM
Messages: 2378
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

Report message to a moderator

Re: How to make a list2 on a non existing child table [message #1907 is a reply to message #1906]

Tue, 09 December 2008 08:11

bonzo_bcn
Messages: 152
Registered: June 2008

Senior Member

Ok, the problem is that my sql_from is like this:

Code: [Select all] [Show/ hide]

(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

so the function extractTableNames returns an incorrect value:'SELECT'
and then qualifyField turns $where to null.

Do you know how can I solve this?

Report message to a moderator

Re: How to make a list2 on a non existing child table [message #1908 is a reply to message #1907]

Tue, 09 December 2008 08:15

AJM
Messages: 2378
Registered: April 2006
Location: Surrey, UK

Senior Member

What is the SQL statement that you are trying to construct? I am assuming that this will retrieve the rows that you want.

Is this statement being corrupted by the framework? If so, then how?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

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

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:

Code: [Select all] [Show/ hide]

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 #1910 is a reply to message #1909]

Tue, 09 December 2008 09:12

AJM
Messages: 2378
Registered: April 2006
Location: Surrey, UK

Senior Member

The standard processing in the getData() method is not designed to deal with such a customised SQL statement, so I suggest the following:

In the _cm_pre_gtData() method you should set $this->skip_getdata to TRUE.
Insert a call to $this->getData_raw() to populate the contents of $this->fieldarray.

This will not try to edit your SQL statement in any way, so it should produce the results you expect.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

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

bonzo_bcn
Messages: 152
Registered: June 2008

Senior Member

Thanks,I'm getting closer, so in my inner class table I have:

Code: [Select all] [Show/ hide]

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 #1912 is a reply to message #1911]

Tue, 09 December 2008 10:43

AJM
Messages: 2378
Registered: April 2006
Location: Surrey, UK

Senior Member

If the LIST2 task is retrieving all available records instead of only those which have been selected in the parent LIST1 task then it sounds like the WHERE string which is being processed in the LIST2 task is inadequate. Take a look at Associated/Related Rows.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: How to make a list2 on a non existing child table [message #1913 is a reply to message #1905]

Tue, 09 December 2008 10:50

bonzo_bcn
Messages: 152
Registered: June 2008

Senior Member

In fact I added this code too to the parent class:

Code: [Select all] [Show/ hide]

	function _cm_pre_getData ($where, $where_array, $fieldarray=null){
		$this->skip_getdata = true;
		$this->fieldarray=	$this->getData_raw($where);
		return $where;
	}

and now it only displays the list2 of the first record selected in list1, allthough the .sql file has the correct where clause in the parent sql sentence.

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

AJM
Messages: 2378
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

Report message to a moderator

Re: How to make a list2 on a non existing child table [message #1915 is a reply to message #1905]

Tue, 09 December 2008 11:37

bonzo_bcn
Messages: 152
Registered: June 2008

Senior Member

The OUTER table for the LIST2 task and the INNER table of the LIST2 task are correct.

Report message to a moderator

Re: How to make a list2 on a non existing child table [message #1916 is a reply to message #1915]

Tue, 09 December 2008 11:52

AJM
Messages: 2378
Registered: April 2006
Location: Surrey, UK

Senior Member

If both are correct then what is the problem?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: How to make a list2 on a non existing child table [message #1917 is a reply to message #1905]

Tue, 09 December 2008 11:59

bonzo_bcn
Messages: 152
Registered: June 2008

Senior Member

The sql sentences are correct, but in the list2 screen, it only displays one outer record even though I have selected several records in the list1 screen.
It displays the first one, and there isn't the possibility to move to the next one.

Report message to a moderator

Re: How to make a list2 on a non existing child table [message #1918 is a reply to message #1917]

Tue, 09 December 2008 12:39

AJM
Messages: 2378
Registered: April 2006
Location: Surrey, UK

Senior Member

How many rows are actually selected by the sql statement which is issued by the outer table? If there is more than one then the outer area should contain scrolling options. It certainly does on all the LIST2 tasks that I have written.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: How to make a list2 on a non existing child table [message #1919 is a reply to message #1905]

Tue, 09 December 2008 15:32

bonzo_bcn
Messages: 152
Registered: June 2008

Senior Member

This are the two sql statements (I removed the

Code: [Select all] [Show/ hide]

from

part to make it comprehensible):

Code: [Select all] [Show/ hide]

Outer:
SELECT SQL_CALC_FOUND_ROWS entidad_id, MAX(noment) as noment, sum(importe) as importe FROM ... as entidad_s01  WHERE ( entidad_id='5' ) OR ( entidad_id='6' )  GROUP BY 1    LIMIT 1 OFFSET 0 =>Count=2

Inner:
SELECT SQL_CALC_FOUND_ROWS entidad_id, concepto, sum(importe) as importe FROM .. as entidad_s02  WHERE entidad_id='5'  GROUP BY 1,2    LIMIT 10 OFFSET 0 =>Count=1

Report message to a moderator

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

AJM
Messages: 2378
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

Report message to a moderator

Re: How to make a list2 on a non existing child table [message #1922 is a reply to message #1905]

Wed, 10 December 2008 04:11

bonzo_bcn
Messages: 152
Registered: June 2008

Senior Member

It produces 7 results.
Where is the scroller built? (Which class should I look at with the debugger?)

Report message to a moderator

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

AJM
Messages: 2378
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:

Code: [Select all] [Show/ hide]

// 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

Report message to a moderator

Previous Topic:	Dynamic Report Column Headings
Next Topic:	get calculated field from parent table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

PDF

]

Current Time: Thu Apr 10 14:26:30 EDT 2025

Total time taken to generate the page: 0.01531 seconds