Select multidimensional in mysql?

Question

I'm sure I seen 'multidimensional' and 'mysql' used together. Lets take this simple example.

Lets say I want to select 20 threads and i'll make what i need simple. I'll want the id, author_id, subject and body. I'll need to filtering out deleted threads in a where statement. I'd like to select all the post in these threads. I want post_id, author, body and filter out deleted, etc.

I'd need to know which row belongs to what thread. Is there a way to select this from mysql in one go? Or do I need to make both of them have the same column amount, put IsThread (false if its a post), have each post carry its thread# + null or empty string subject, etc. That sounds like it may perform worse? (although i'd need thread# when I toss it a list of threads to collect from)

Is there a way to get a well performing simple to maintain select statement that gets me all of this data? or do I need two statements?

-edit- a comment made me want to say this. Could I get List<Tuple<int, int, string, string, List<Tuple<int, int, int, string>> >> from mysql. Yeah that could hurt your brain but just remember I want post and thread data which i normally get in two queries, but in one

Answer 1

Unless I'm missing something, this sounds like a typical JOIN query.

Let's say you have two tables as follows:

CREATE TABLE Threads (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  originalAuthorId INTEGER,
  subject VARCHAR(100),
  created DATETIME,
  deleted INTEGER );

CREATE TABLE Posts (
  id INTEGER PRIMARY KEY AUTO_INCREMENT,
  threadId INTEGER,
  authorId INTEGER,
  body TEXT,
  created DATETIME,
  deleted INTEGER,
  FOREIGN KEY (threadId) REFERENCES Threads (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE );

Your query to select everything in one go would look something like this:

SELECT
  t.id AS "threadId",
  t.originalAuthorId,
  t.subject AS "threadSubject",
  t.created AS "threadCreated",
  p.id AS "postId",
  p.authorId,
  p.body,
  p.created AS "postCreated"
FROM
  Threads t JOIN Posts p
    ON t.id = p.threadId
WHERE
  t.deleted = 0 AND
  p.deleted = 0;

That query would generate a table with eight columns: threadId, originalAuthorId, threadSubject, threadCreated, postId, authorId, body, and postCreated.

If that's not what you're looking for, drop a comment or update your original question and I'll try to help further.

Edit:

Per comment below, here's how you could optimize the queries for a large amount of data. I'll use PHP as the sample script language.

// Set $dbh to a PDO object pointing to your database.
$sql = "SELECT id, originalAuthorId, subject, created FROM Threads ".
    "WHERE deleted = 0";
$sth = $dbh->query($sql);
$threads = array( );
while ($row = $sth->fetch(PDO::FETCH_ASSOC))
  $threads[$row['id']] = $row;

$sql = "SELECT id, threadId, authorId, body, created FROM Posts ".
    "WHERE deleted = 0 ORDER BY threadId";
$sth = $dbh->query($sql);
while ($row = $sth->fetch(PDO::FETCH_ASSOC))
  $posts[$row['id']] = $row;

// At this point, you can view your post information, sort it using whatever
// sort mechanism you want, etc. You can get access to the thread information
// in the thread array. For example:

foreach ($posts as $k => $v)
    echo "Post ".$k." created at ".$v['created']." belongs to thread ".
        $v['threadId']." by author id ".
        $threads[$v['threadId']]['originalAuthorId']. " created ".
        $threads[$v['threadId']]['created']."\n";

If you have a really large amount of data, you could get really fancy by actually having two statement handles open to the database at the same time and, through clever ordering, iterating through them simultaneously. Here's a bit more complicated example:

// Set $dbh to a PDO object pointing to your database.
$sql = "SELECT id, originalAuthorId, subject, created FROM Threads ".
    "WHERE deleted = 0 ORDER BY id";
$sthThreads = $dbh->query($sql);

$sql = "SELECT id, threadId, authorId, body, created FROM Posts ".
    "WHERE deleted = 0 ORDER BY threadId";
$sthPosts = $dbh->query($sql);

$rowThread = null;
while ($rowPost = $sthPosts->fetch(PDO::FETCH_ASSOC)) {
    // You REALLY want a foreign key constraint for the following statement!
    while ($rowThread === null || $rowThread['id'] !== $rowPost['threadId'])
        $rowThread = $sthThreads->fetch(PDO::FETCH_ASSOC);
    echo "Post ".$rowPost['id']." created at ".$rowPost['created'].
        " belongs to thread ".
        $rowPost['threadId']." by author id ".
        $rowThread['originalAuthorId']. " created ".
        $rowThread['created']."\n";
}

(Keep in mind that I'm just winging this off the cuff, so apologies for any syntax errors or grammos in advance.)

Answer 2

I am assuming thread has many realtion with posts. In that case, you can use group_concat

eg:

CREATE TABLE services (
id INT UNSIGNED NOT NULL, 
client_id INT UNSIGNED NOT NULL, 
KEY (id));
INSERT INTO services 
VALUES (1,1),(1,2),(3,5),(3,6),(3,7);

SELECT id,client_id FROM services WHERE id = 3;
+----+-----------+
| id | client_id |
+----+-----------+
|  3 |         5 | 
|  3 |         6 | 
|  3 |         7 | 
+----+-----------+

SELECT id,GROUP_CONCAT(client_id) FROM services WHERE id = 3 GROUP BY id;     
+----+-------------------------+
| id | GROUP_CONCAT(client_id) |
+----+-------------------------+
|  3 | 5,6,7                   | 
+----+-------------------------+