Concatenate data from multiple tables in codeigniter

Question

How do you guys get the data from two or more tables and manipulate it in codeigniter?

Let's assume that we have 2 tables questions and answers. A question can have one or more answer. Now let's assume that an answer can have likes and unlikes. How do you people get all the questions with answers for display them in a view?. I will show you my solution but I'm curious about yours.

I make use of libraries like so:

class Questions_library {
    var $CI = &get_instance();

    function get_questions_with_answers(){    
        $data = array();
        $questions = $this->CI->questions_model->get_questions(); 

        if ($questions):
            foreach ($questions as $q): 
                $temp = array();
                $temp = $q;
                $temp['answers'] = $this->CI->answerslibrary->get_answers($q['id']);
                $data[] = $temp;         
            endforeach;    
        endif; 

        return $data ? $data : false;
    }
}

Now in the answerslibrary i can take the same aproach to get the answers and answerlikes and unlikes.

A draw back in my aproach is that if I want to display my questions with pagination sorted by number of answers or likes/unlikes etc i have to use join or nested queries inside my model and i canot make use of the power of my libraries.

How do you manage this task ?

Not sure about tables/queries complexity, but i guess that you could JOIN tables (in one query), and return results inside one function? — sinisake
– sinisake, Commented Mar 25, 2014 at 17:51
I think this question will be more well localized on programmers.stackexchange.com — Guerra
– Guerra, Commented Mar 25, 2014 at 17:52
Well, joining them in one function you will get something like question1|answer1, question1|answer2, question1|ansser3,...,question2|answer1 , then how do you iterate trough questions ? Or how do you get the total number of questions or how do you get the number of answers or answers votes (likes, unlikes)....? How do you create your data structure so it can be easy to iterate or sort....? — adrian F
– adrian F, Commented Mar 25, 2014 at 18:29

Federico J. · Accepted Answer · 2014-03-26 19:29:49Z

As @nevermind points out, the only way is by JOIN in your SQL. Any PHP loop of data as you do is just going to be a bottleneck, in both PHP and MySQL. With an only query with a JOIN you'll get all the results, (use the GROUP BY clause with SUM and COUNT to get the number of the elements) and you'll be able of use ORDER BY. And you only use a query against the DDBB, while with your method you use a lot of queries (1 per row) to get the same result. Believe me, I've seen code in system as which you proposed above, and it's like a straitjacket.

If you wanted more help about using the MySQL clauses, post your code and I'll point you about how to do it.

UPDATE

Lets try a bit with SQLFiddle:

Starting point: Two tables, question and answer, with the following structure:

CREATE TABLE IF NOT EXISTS `answer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question_id` int(11) DEFAULT NULL,
  `title` tinytext COLLATE utf8_spanish_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

INSERT INTO `answer` (`id`, `question_id`, `title`) VALUES
    (1, 1, 'a1'),
    (2, 1, 'a2'),
    (3, 1, 'a3'),
    (4, 1, 'a4'),
    (5, 1, 'a5'),
    (6, 2, 'a3'),
    (7, 2, 'a2'),
    (8, 2, 'a1');    

CREATE TABLE IF NOT EXISTS `question` (
  `id` int(11) DEFAULT NULL,
  `title` tinytext COLLATE utf8_spanish_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;

INSERT INTO `question` (`id`, `title`) VALUES
    (1, 'question 1'),
    (2, 'question 2');

http://sqlfiddle.com/#!2/400994/6

As you see, in the fiddle we get the INNER JOIN with the proper data, as many rows as the JOIN of both table.

With a GROUP BY and a LIMIT, we can get all the values, with no worries:

SELECT 
  q.id qId
, q.title qTitle
, GROUP_CONCAT( a.id SEPARATOR " ")

FROM question q

INNER JOIN answer a 
ON a.question_id = q.id

GROUP BY qId 
LIMIT 3

http://sqlfiddle.com/#!2/400994/8

The query above will join the tables, group the values, and then, and only then, limit the number of the resultant rows. As you see, you get all the values, and you can even count the values, sum them, concatenate them... As much as you want:

SELECT 
  q.id qId
, q.title qTitle
, GROUP_CONCAT( a.id SEPARATOR " ")
, SUM( a.id )
, COUNT( a.id )
FROM question q

INNER JOIN answer a 
ON a.question_id = q.id

GROUP BY qId 
LIMIT 3

http://sqlfiddle.com/#!2/400994/11

And of course, you may wish order them by number of answers, ASC or DESC:

SELECT 
  q.id qId
, q.title qTitle
, GROUP_CONCAT( a.id SEPARATOR " ")
, SUM( a.id ) sumIdsValues
, COUNT( a.id ) totalAnswers
FROM question q

INNER JOIN answer a 
ON a.question_id = q.id

GROUP BY qId 
ORDER BY totalAnswers ASC
LIMIT 3

http://sqlfiddle.com/#!2/400994/12

Other thing is getting the values of the different answers, to create an array to export. In that case, you may compound a JSON from the values with CONCAT:

SELECT 
  q.id qId
, q.title qTitle
, CONCAT( "[", GROUP_CONCAT( a.id SEPARATOR ", "), "]" ) idArrays
, SUM( a.id )
, COUNT( a.id )
FROM question q

INNER JOIN answer a 
ON a.question_id = q.id

GROUP BY qId 
LIMIT 3

http://sqlfiddle.com/#!2/400994/14

And you get the field idArrays = [1, 2, 3, 4, 5] as a JSON array you may decode with json_decode() and assign to a var. You may use even more complicated structure, the important thing is knowing what you want to compound, and then write the MySQL clause with CONCAT to compound it, XD

I get your point. My aproach is ok when I have few records in the database cause I have a query inside another query. That is well known as the "N+1 problem". However if i use pagination (limit) in the first query then I will significantly reduce the number of queries for the second one (N=limit). Im not so good at MySql and im courious how do you get all the questions and answers in one query and let's say put them in a multidimensional array so you can easily iterate trough them so finally you can have array(joke1=>array(answer1,answer2,etc),joke2=>array(answe3,answe4),etc) ?

Collectives™ on Stack Overflow

Concatenate data from multiple tables in codeigniter

1 Answer 1

UPDATE

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related