fayong lin fayong lin -3 years ago 53
MySQL Question

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

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 Source

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"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download