grouping of results from a query with multiples join and many to many relationships

Question

I have this database layout:

CREATE TABLE `articles` (
  `articleId` binary(16) NOT NULL,
  `filename` varchar(55) NOT NULL,
  `usrType` int(1) NOT NULL,
  `creationTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `author` varchar(55) NOT NULL,
  `pathname` varchar(500) NOT NULL
)

CREATE TABLE `topics` (
  `topic` varchar(50) NOT NULL
);

CREATE TABLE `keywords` (
  `keyword` varchar(50) NOT NULL
);

CREATE TABLE `articlesTopics` (
  `articleId` binary(16) NOT NULL,
  `topic` varchar(50) NOT NULL
);

CREATE TABLE `articlesKeywords` (
  `articleId` binary(16) NOT NULL,
  `keyword` varchar(50) NOT NULL
);

There are many-to-many relationships between articles and keywords, and between articles and topics. These relationship are expressed in the articlesKeywords and articlesTopics, respectively.

I want to write a query that would get me for a given usrType the values HEX(articleId), filename, usrType, pathname, creationTime + all the keywords and all the topics associated with a each articleId.

The best I have been able to do so far is the following query:

$query = "SELECT HEX(articleId), filename, usrType, pathname, creationTime, keyword, topic FROM articles LEFT JOIN articlesKeywords USING (articleId) LEFT JOIN articlesTopics USING (articleId) WHERE usrType = ? ORDER BY creationTime DESC";

which I process with PHP like this:

mysqli_stmt_prepare($stmt, $query);
    mysqli_stmt_bind_param($stmt,'s', $usrType);
    mysqli_stmt_execute($stmt);

    mysqli_stmt_bind_result($stmt, $articleId, $filename, $perm, $path, $creationTime, $keywords, $topics);

    $articlesData = [];

    while(mysqli_stmt_fetch($stmt)){

      if(!$keywords){
       $keywords = [""];
      }

      if(!$topics){
       $topics = [""]; 
      } 

      array_push($articlesData, [ 'id' => $articleId
                                , 'filename' => $filename
                                , 'path' => $path
                                , 'perm' => $perm 
                                , 'keywords' => $keywords
                                , 'topics' => $topics
                                , 'creationTime' => $creationTime
                                ]);
    }

Right now I get duplicate articles for each keyword or topic. I would like to group - idealy as a php array, but concatenating would be fine too - all the keywords associated with one article in one field, idem for the topics.

I tried using GROUP BY but got a syntax error.

Answer 1

Then use group by and group_concat(). To avoid duplicates, you probably want to put these in subqueries:

SELECT HEX(a.articleId), a.filename, a.usrType, a.pathname, a.creationTime,
       ak.keyword, ak.topic 
FROM articles a LEFT JOIN
     (SELECT ak.articleId, group_concat(ak.keywords) as keywords
      FROM articlesKeywords ak
      GROUP BY ak.articleId
     ) ak
     USING (articleId) LEFT JOIN
     (SELECT t.articleId, group_concat(t.topics) as topics
      FROM articlesTopics t
      GROUP BY t.articleId
     ) t
     USING (articleId)
WHERE usrType = ?
GROUP BY a.articleId
ORDER BY creationTime DESC"