如何填写中间表？

Question

I have Two tables in ManyToMany relation:

Table Molécules:

id  | main_name | others …
--- | --------- | ------
1   | caféine   | others …

Table jsonTextMining:

id | title  | molecule_name                       | others …
---|------- |-------------------------------------|------
1  | title1 | colchicine, cellulose, acid, caféine| others …

and 1 intermediate table:

Table json_text_mining_molecule (it's an exemple i don't succeed to fill it):

json_text_mining_id      | molecule_id
------------------------ | ---------------
1                        | corresponding molecule id's
1                        | corresponding molecule id's
2                        | corresponding molecule id's

My problem is that molecule_name in jsonTextMining are a string, i need to separate them before anything.

I tried this :

$molecules = explode (', ', $jsonTextMining→getMoleculeName());
foreach ($molecules as $molecule) {
$jsonTextMining->setMolecule($molecule);
}
$em->persist($jsonTextMining);
$em->flush;

But i think i should loop on jsonTexMining too and to be honnest i'm not sure where to put this part of code. Is it on a random page and the code will execute, should i do a button ?

I know exactly how to fill table with id's when there is a OneToMany relation, i use sql like this :

UPDATE table1 SET id_relation1 = table2.id
FROM table2 
WHERE table1.main_name = table2.main_name

But this code fill only one column with id and there's always the matter of string. Is there really a way to get these id's linked so every molecule will have several jsonTextMining ?

Answer 1

You can first split the string using regexp_split function:

select id, regexp_split_to_table(molecule_name,', ') as m_name from jsonTextMining

That will give you a table of ids and names:

 id |    name
----+------------
  1 | acid
  1 | caffeine
  1 | cellulose
  1 | colchicine

Next, you can read from the above, match the names to the ids in the molecule table and aggregate the ids. All put together would result in this:

select s.id, string_agg(m.id::text, ', ') 
from (select id, regexp_split_to_table(molecule_name,', ') as m_name 
    from jsonTextMining) as s, molecules m 
where m.main_name = s.m_name group by s.id;

Which gives this result:

 id | string_agg
----+------------
  1 | 4, 1, 3, 2
(1 row)

If you don't want to aggregate the results and display them one row per molecule then just get rid of string_agg and the group by:

select s.id, m.id
from (select id, regexp_split_to_table(molecule_name,', ') as m_name 
    from jsonTextMining) as s, molecules m 
where m.main_name = s.m_name;