0

It's pretty difficult to pick a title, I'm sorry for that!

What I have for a database structure is the following columns:

pages

  • id (int)
  • url (string)
  • content (string)
  • created_at (timestamp)

groups

  • id (int)
  • related_page_ids (json)
  • domain_id (int)
  • created_at (timestamp)

What I want to achieve is to retrieve all groups by a selected domain ID and then retrieve all the related_pages with it. If I should write it in ugly PHP and MySQL:

$groups = SELECT * FROM groups WHERE domain_id = 1;
    
foreach($groups as $group){
    $pages = SELECT * FROM pages WHERE IN id = implode($group['related_page_ids']);
}

I hope that you understand my goals with the ugly example.

user3783243
  • 5,368
  • 5
  • 22
  • 41
Donny van V
  • 921
  • 1
  • 10
  • 22
  • Please provide sample data. This also is likely why storing delimited data is a bad choice. This would be a simple task with a `pages_to_group` table. – user3783243 Jun 13 '21 at 00:28
  • 1
    Out of curiosity, why did you decide to use JSON for the related_page_ids, instead of the standard normalized solution, which should be a third table for the many-to-many relationship? – Bill Karwin Jun 13 '21 at 01:36

1 Answers1

0

If you have access to JSON_TABLE (MySQL 8 / MariaDB 10.6) it can be done with that in a single query:

SELECT * FROM pages 
 WHERE id IN (
  SELECT * FROM JSON_TABLE(
    (SELECT JSON_ARRAYAGG(related_page_ids) FROM groups WHERE domain_id=1),
    '$[*][*]' columns(rel_id INTEGER path '$')) AS jt );

The column needs to actually be of type JSON (not varchar) for this to work.

Tobias K.
  • 2,997
  • 2
  • 12
  • 29