To get many tags for a question effectively from Postgres by PHP

Question

How can you get many tags for a question effectively from Postgres database by PHP?

I have tables tags and questions where I have the variables questions_question_id and question_id, respectively, for instance.

One question may have many tags. I can select question_id, title and a tag from the tables. However, I do not know how to get many tags for one question out of the database effectively. PHP restricts me in manipulating the data as matrices.

I can read question_id, title and tag to the variable $result. The data is a matrix for me in the variale $result. However, I cannot refer to the data like to a matrix.

I have only found the functions pg_fetch_row and pg_fetch_all_columns in PHP. The former gets the data as arrays for rows, while the latter as arrays for columns.

My problem with LEFT JOIN suggests me that it may not be possible to get the data as follows

1. get the columns for *title, question_id* and tag
2. group the data by question_id such that I can put tags to my questions: I have tried to manipulate the data by PHP, but I get only this far.

Answer 1

I think what you want is a second query that gets all the tags for a given question. Trying to join them together is gonna be cumbersome. It can be done but you'll end up with several rows with the same question data but different tag data.

So first do something like this (I don't know the structure of your database so this is just an example):

SELECT * FROM QUESTIONS WHERE ID = <id>;

Then you do a query right after that one, which asks for all the tags:

SELECT * FROM TAGS WHERE QUESTION_ID = <question_id>;

Answer 2

If you are bold, you can implement an aggregate function in PostgreSQL which concatenates all the relevant tags together for you, and then a single query will Do What You Want (tm).

pg=> create aggregate ARRAY_ACCUM (
  sfunc = array_append,
  basetype = anyelement,
  stype = anyarray,
  initcond = '{}'
);

pg=> select QUESTIONS.ID as "QID",
pg-> array_to_string(array_accum(TAG), ',') as "TAGS"
pg-> from QUESTIONS
pg->   left join TAGS on QUESTIONS.ID = TAGS.QID;
 QID |     TAGS      
-----+---------------
   1 | foo, bar, baz
   2 | foo, bar
   3 | foo
   4 | 

-- Reference, for completeness --
pg=> select * from QUESTIONS;
 id 
----
  1
  2
  3
  4
(4 rows)

pg=> select * from TAGS;
 qid | tag 
-----+-----
   1 | foo
   1 | bar
   1 | baz
   2 | foo
   2 | bar
   3 | foo

Under MySQL, this functionality is called GROUP_CONCAT. Under pg, it's called "Do It Yourself." :) Now, there are even subtler ways to go about it under pg, too. Check out this clever blog post, and its comments, from which I stole^Wadapted the create aggregate solution proposed above. :)