current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
up vote 0 down vote favorite

I'm trying to sort an array in to a three-deep array. This is my current query:

SELECT * FROM question 
INNER JOIN category ON question.category_id = category.id
INNER JOIN difficulty ON question.difficulty_id = difficulty.id

Expected result is something like:

array(
    '1' => array( // category id 1
        '1' => array( // difficulty id 1
            '1' => array('...'), // question id 1
            '2' => array('...') // question id 2
        ),
        '2' => array(
            '3' => array('...'),
            '4' => array('...')
        )
    )
)

I did have the following:

foreach($categories as $category) {
    foreach($difficulties as $difficulty) {
        foreach($questions as $question) {
            if ($question['category_id'] == $category['id'] && $question['difficulty_id'] == $difficulty['id']) {
                $feed[$category['id']][$difficulty['id']][$question['id']] = $question;
            }
        }
    }
}

But there will be 10,000+ questions and performance will be bad so is there a way I can do this with one query and fewer loops?

share|improve this question
1  
It seems unlikely that you would need to dump all the data from these three tables into php for processing. You should probably be doing more of the data processing work in the database, rather than in php. – Gordon Linoff Apr 19 '15 at 14:21
    
Essentially my goal is to achieve all the question data sorted in their related category and difficulties. – Sygon Apr 19 '15 at 14:25
    
well that update came too late. What purpose? Storing in local storage (retrieved by ajax call) for offline apps or just display? – maraca Apr 19 '15 at 14:38
    
It is an AJAX request I then json encode the dataset and return it to my application for localStorage storing. – Sygon Apr 19 '15 at 14:45
    
ok I added mysql specific information and how to build a structure in local storage, but i'm not good with JSON – maraca Apr 19 '15 at 15:19
up vote 2 down vote

Basically you could just return your query and order by the ids like so:

Category_ID      Difficulty_ID      Question_ID
     0                0                 0
     0                0                 1
     1                0                 2
     1                3                 3
     1                3                 4
     2                0                 5
     2                1                 6

Then parse everything in a while:

  1. each time the category_ID changes add a new category with empty difficulty and reset previous difficulty
  2. each time the difficulty changes add new difficulty to category with empty question
  3. each time add the question to current difficulty.

To store this structure performantly in local storage:

  1. define a unique delimiter (note: IE doesn't support control characters, this also means you can't store binary data without encoding it before, e.g. base64)

  2. load each row of each table like this:

    key: unique table prefix + id
value: columns (delimited with the delimiter defined before)

    The easiest way to return a whole table at once is to define a second delimiter and then have some slightly ugly query in the form of:

    SELECT id||delimiter||col1||delimiter||...||colN FROM ...

    And then put it all together with a list aggregation using the second delimiter (group_concat() in mysql).

  3. Sometimes you need maps (for N to M relations or also if you want to search questions by difficulty or category), but because each question only has one category and difficulty you are already done.

Alternative

If the data is not too big and doesn't change after login, then you can just use the application cache and echo your stuff in script tags.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.