1
\$\begingroup\$

I have 3 tables like this:

CREATE TABLE IF NOT EXISTS blog_post (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title varchar(100) NOT NULL,
  image varchar(250),
  content TEXT NOT NULL,
  created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  edited DATETIME ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS blog_category (
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(100) NOT NULL UNIQUE,
  icon varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS blog_post_blog_category (
  blog_post_id int(11) NOT NULL,
  blog_category_id int(11) NOT NULL,
  CONSTRAINT blog_post_blog_category_pkey PRIMARY KEY (blog_post_id, blog_category_id),
  CONSTRAINT blog_post_blog_category_fkey FOREIGN KEY (blog_post_id) REFERENCES blog_post (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT blog_category_blog_post_fkey FOREIGN KEY (blog_category_id) REFERENCES blog_category (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET ascii COLLATE ascii_general_ci;

So basically one blog post can be part of many categories and one category can be part of many posts, pretty straight forward, my question is what is best way to get list of all posts with their categories. As result I'm expecting array of posts and I want every post to have array of categories like this.

{
  "4": {
    "id": "4",
    "title": "first post",
    "image": "https://image.flaticon.com/icons/svg/102/102833.svg",
    "content": "this should be some content",
    "created": "2017-09-24 16:21:28",
    "edited": null,
    "categories": [{
      "id": 1,
      "icon": 'www.url.com',
      "name": 'category name'
    }, {
      "id": 5,
      "icon": 'www.url1.com',
      "name": 'category6 name'
    }]
  },
  "6": {
    "id": "6",
    "title": "second post",
    "image": "https://image.flaticon.com/icons/svg/102/102833.svg",
    "content": "this is some text from post",
    "created": "2017-09-24 16:29:53",
    "edited": null,
    "categories": [{
      "id": 5,
      "icon": 'www.url.com',
      "name": 'category name'
    }, {
      "id": 3,
      "icon": 'www.url1.com',
      "name": 'category6 name'
    }]
  }
}

This is how i did it, is this correct and good solution i feel like i'm missing something and i'm pretty sure this could be done way better? Also i would like to get this list with correct indexes starting from 0 and not array with IDs like i have in my solution.

$app->get('/blog/posts', function (Request $request, Response $response, array $args) {
    $QUERY = 'SELECT bp.id, bp.title, bc.name FROM blog_post bp 
              LEFT JOIN blog_post_blog_category bpbc ON bpbc.blog_post_id = bp.id 
              LEFT JOIN blog_category bc ON bc.id = bpbc.blog_category_id ORDER BY bp.id';

    $result = R::getAll( $QUERY );
    $resultMap = array();

    foreach ($result as $value) {
        if(empty($resultMap[$value['id']])) {
            unset($value['name']);
            $resultMap[$value['id']] = $value;
            $resultMap[$value['id']]['categories'] = array();
        }
    }

    foreach ($result as $value) {
        $category = array();
        $category['name'] = $value['name'];
        array_push($resultMap[$value['id']]['categories'], $category);
    }

    echo json_encode($resultMap);
});
\$\endgroup\$
1
\$\begingroup\$

I am not sure why you are using 2 foreach loops, you should be able to combine them.

foreach ($result as $value) {
    if(empty($resultMap[$value['id']])) {
        unset( $value['name'] );
        $resultMap[ $value['id'] ] = $value;
        $resultMap[$value['id']]['categories'] = array();
    }

    $resultMap[$value['id']]['categories'][] = array(
        'name' => $value['name'],
    );
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.