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

Home » RADICORE » How To » convert_parent_id_to_child_id
convert_parent_id_to_child_id [message #7253] Wed, 06 February 2019 14:18 Go to next message
pdv is currently offline  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
Re: convert_parent_id_to_child_id [message #7257 is a reply to message #7253] Thu, 07 February 2019 05:10 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
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?

Re: convert_parent_id_to_child_id [message #7261 is a reply to message #7257] Thu, 07 February 2019 08:35 Go to previous messageGo to next message
pdv is currently offline  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
Re: convert_parent_id_to_child_id [message #7262 is a reply to message #7261] Fri, 08 February 2019 04:50 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
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'];
to
$fieldarray[$child_name] = stripOperators($fieldarray['id']);


Re: convert_parent_id_to_child_id [message #7267 is a reply to message #7262] Sat, 09 February 2019 14:23 Go to previous messageGo to next message
pdv is currently offline  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.
Re: convert_parent_id_to_child_id [message #7268 is a reply to message #7267] Sun, 10 February 2019 05:02 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2246
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.

Re: convert_parent_id_to_child_id [message #7276 is a reply to message #7253] Sat, 23 February 2019 05:37 Go to previous messageGo to next message
pdv is currently offline  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.
Re: convert_parent_id_to_child_id [message #7277 is a reply to message #7276] Sun, 24 February 2019 05:12 Go to previous message
AJM is currently offline  AJM
Messages: 2246
Registered: April 2006
Location: Surrey, UK
Senior Member
I will change my code accordingly.

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


Current Time: Sun Jun 13 09:31:44 EDT 2021

Total time taken to generate the page: 0.01081 seconds