Radicore Forum: How To » convert_parent_id_to_child

Home » RADICORE » How To » convert_parent_id_to_child_id

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

convert_parent_id_to_child_id [message #7253]

Wed, 06 February 2019 14:18

pdv
Messages: 15
Registered: January 2019

Junior Member

Hi,

I'm accessing some existing PostgreSQL databases on macos. Most of these have a primary key "id" whereas the foreign key is usually object_id; I know that it is preferred using the same name, but that's no option. Luckily include.library.inc contains a function convert_parent_id_to_child_id() to handle this particular case.

The function is called in std.table.class.inc, in the getData() function (line 2551 in the last version).

However for this to work properly the call to where2array($where, $this->pageno, false) a few lines higher should be called with the last parameter set to true.

Regards,

Patrick

Report message to a moderator

Re: convert_parent_id_to_child_id [message #7257 is a reply to message #7253]

Thu, 07 February 2019 05:10

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

Senior Member

Can you provide me with sample code which reproduces this problem so that I can test your solution?

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

Report message to a moderator

Re: convert_parent_id_to_child_id [message #7261 is a reply to message #7257]

Thu, 07 February 2019 08:35

pdv
Messages: 15
Registered: January 2019

Junior Member

Tony,

I have a simple one-to-many relationship like department(id,...) <-->> employee(id,department_id,...).
I inserted a button on the department(list1) to list the employee(list2).

So far so good but this returned nothing, although there are matching rows. When looking at the query:

SET search_path TO 'public';
SELECT count(*) FROM employee
LEFT JOIN department ON (department.id=employee.department_id)
WHERE employee.id= '1' AND employee.department_id= '1'

I conclude that the first employee.id in the WHERE clause should have been department.id and then both clauses are redundant.
By debugging the code I've found that in convert_parent_id_to_child_id a comparison was made between 'department' and "='department'". The latter contains the operator because where2array(....,false) was called.
With where2array(...,true) the proper WHERE clause is returned and it works.

Regards,

Patrick

Report message to a moderator

Re: convert_parent_id_to_child_id [message #7262 is a reply to message #7261]

Fri, 08 February 2019 04:50

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

Senior Member

Unfortunately your suggested fix will break existing applications. When I originally wrote the array2where() function it converted the string "field=value" into an associative array(field => value) which assumes that the operator is '='. In some cases I have to deal with WHERE strings where the operator is LIKE or BETWEEN or IN. This is why I added the extra argument to the where2array() function so that it will not strip the operators out so that they will be preserved when converting the array back into a string.

The correct fix is to strip the operators from the supplied value in the convert_parent_id_to_child_id() function before it is used. You can do this by changing

$fieldarray[$child_name] = $fieldarray['id'];

$fieldarray[$child_name] = stripOperators($fieldarray['id']);

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

Report message to a moderator

Re: convert_parent_id_to_child_id [message #7267 is a reply to message #7262]

Sat, 09 February 2019 14:23

pdv
Messages: 15
Registered: January 2019

Junior Member

My first idea was also to strip the operators but then I discovered the boolean parameter in the function call.
Since I didn't know the stripOperators() that looked easy.
Thanks.

Report message to a moderator

Re: convert_parent_id_to_child_id [message #7268 is a reply to message #7267]

Sun, 10 February 2019 05:02

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

Senior Member

The stripOperators() function is one of those functions available to the framework that only the framework should need to use.

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

Report message to a moderator

Re: convert_parent_id_to_child_id [message #7276 is a reply to message #7253]

Sat, 23 February 2019 05:37

pdv
Messages: 15
Registered: January 2019

Junior Member

I did not check this properly yet and now found out that it does not work.
I think one should strip the operators from all entries in $fieldarray.

I've put

$fieldarray = stripOperators($fieldarray);

at the beginning of the function convert_parent_id_to_child_id($fieldarray, $tablename, $parent_relations)

and that works.

Report message to a moderator

Re: convert_parent_id_to_child_id [message #7277 is a reply to message #7276]

Sun, 24 February 2019 05:12

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

Senior Member

I will change my code accordingly.

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

Report message to a moderator

Previous Topic:	Multi servers and PostgreSQL
Next Topic:	Check for empty $schema in dml.psql.class.inc

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Sat Dec 21 08:22:34 EST 2024

Total time taken to generate the page: 0.01184 seconds