Оптимизация цикла while, содержащего запросы PDO


Информация

В настоящее время строится страница уведомлений, на которой перечислены все зарегистрированные уведомления пользователей, содержащие информацию об уведомлении на каждом из них.

Например, без информации

You have an unread message

С информацией

<Sarah> Sent you an message

Задача

Поскольку уведомления требуют таких данных, как имя пользователя (для уведомлений о сообщениях) или название статьи (скажем, ваш следующий автор, и они выпускают новый пост в блоге, один уведомление должно было бы вытащить таблицу username form users, а затем также заголовок блога из таблицы блога) это приводит к тому, что моя страница отстает даже на localhost, который, как я предполагаю, станет значительно хуже после загрузки и тестирования в дикой природе.

Текущий Код

function showNotifications($userid){
    $STH = $this->database->prepare('SELECT * FROM notifications WHERE user_id = :userid ORDER BY timestamp DESC');
    $STH->execute(array(':userid' => $userid));
    while($row = $STH->fetch(PDO::FETCH_ASSOC)){
        $this->sortNotif($row);
}

Краткое описание функции ниже, поскольку у меня есть различные типы уведомлений, я создал кучу идентификаторов для определенных типов, например, тип 1 = новое сообщение, тип 2 = Новый блог post

function sortNotif($notif){

    switch ($notif['type']) {

        case "1":
            $msg = $this->getMessageData($notif['feature_id']);
            $user = $this->userData($msg['sender']);
            echo '<li><i>'.timeAgo($notif['timestamp']).'</i><a href="user.php?username='.$user['username'].'">'.$user['first_name'].'</a> sent you a <a href="inbox.php?message='.$msg['id'].'">message</a></li>';
            break;

    }

}

Как вы можете видеть, просто показывая, что у пользователя есть новое сообщение, он создает 2 запроса и после того, как петля через 40 или около того уведомлений, более 100 или около того пользователей становится нагрузкой на сервер.

Заключительные Слова

Если кому-то нужна дополнительная информация, пожалуйста, спросите, и я обязательно обновлю этот вопрос как можно скорее, спасибо!

Edit

Ниже приведены табличные структуры, как указано ниже. комментарии.

Уведомления

id | user_id | feature_id | type | timestamp | read

Пользователи

id | username | password | first_name | last_name | email | verify_hash | avatar | type 

Сообщения

id | receiver | sender | replying_to | deleted | body | timestamp | read
1   2   2013-10-08 19:16:18

1 ответ:

Изменение плана, так как я неправильно понял установку.

Вы захотите извлечь все данные из каждой таблицы "type" за один раз, а не на основе каждого уведомления. Это означает, что вам нужно будет дважды просмотреть уведомления, один раз, чтобы захватить все идентификаторы и соответствующие типы, а затем второй раз, чтобы вывести результаты.

function showNotifications($userid){
    $STH = $this->database->prepare('SELECT * FROM notifications WHERE user_id = :userid ORDER BY timestamp DESC');
    $STH->execute(array(':userid' => $userid));

    // Centralized Book keeping for the types.
    // When you add a new type to the entire system, add it here as well.
    $types = array();

    // Add the first notification type
    $types["1"] = array();
    // "query" is pulling all the data you need concerning a notification
    $types["1"]["query"] = "SELECT m.id, u.username, u.firstname FROM messages m, users u WHERE m.sender = u.id AND m.id IN ";
    // "ids" will hold the relevant ids that you need to look up.
    $types["1"]["ids"] = array();

    // A second type, just for show.
    // $types["2"] = array();
    // $types["2"]["query"] = "SELECT a.id, u.username, u.firstname FROM articles a, users u WHERE a.sender = u.id AND a.id IN ";
    // $types["2"]["ids"] = array();

    // Use fetchAll to gather all of the notifications into an array
    $notifications = $STH->fetchAll();

    // Walk through the notifications array, placing the notification id into the corret
    // "ids" array in the $types array.
    for($i=0; $i< count($notifications); $i++){
        $types[$notifications[$i]['type']]["ids"][] = $notifications[$i]['feature_id'];
    }

    // Walk through the types array, hit the database once for each type of notification that has ids.
    foreach($types as $type_id => $type){
        if(count($type["ids"]) >  0){
            $STH = $this->database->prepare($type["query"] . "( " . implode(",", $type["ids"]) . " )");
            $STH->execute();
            // Creates a hash table with the primary key as the array key
            $types[$type_id]['details'] = $STH->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC); 
            $types[$type_id]['details'] = array_map('reset', $types[$type_id]['details']);
            // run array_map to make it easier to work with, otherwise it looks like this:
            // $results = array(
            //     1234 => array(0 => array('username' => 'abc', 'firstname' => '[...]')),
            //     1235 => array(0 => array('username' => 'def', 'firstname' => '[...]')),
            // );
        }
    }

    // Now walk through notifications again and write out based on notification type,
    // referencing $types[<notification type>]["details"][<message id>] for the details
    for($i=0; $i< count($notifications); $i++){

        // check to see if details for the specific notification exist.
        if(isset($types[$notifications[$i]['type']]["details"][$notifications[$i]['feature_id']])){
            $notification_details = $types[$notifications[$i]['type']]["details"][$notifications[$i]['feature_id']];

            switch ($notifications[$i]['type']) {

                case "1":
                    echo '<li><i>'.timeAgo($notifications[$i]['timestamp']).'</i><a href="user.php?username=' . $notification_details['username'] . '">' . $notification_details['first_name'].'</a> sent you a <a href="inbox.php?message='.$notifications[$i]['feature_id'].'">message</a></li>';
                break;

            }
        }
    }
}

Update : добавлена логика для пропуска уведомления, если никакие детали не извлекаются (ex. либо сообщение, либо пользователь были исключено)

я думаю, что вы хотите запустить один запрос, который собирает всю информацию либо через соединения, либо через более сложный оператор where.

Вариант 1: это может потребоваться изменить, чтобы не декартово произведение таблиц

SELECT n.id, n.type, m.id, m.body, u.username, u.first_name 
FROM notifications n, messages m, users u
WHERE n.user_id = :userid AND m.id = n.feature_id AND u.id = m.sender

Вариант 2: если псевдонимы таблиц не работают, то вам нужно заменить их полным именем таблицы

SELECT SELECT n.id, n.type, m.id, m.body, u.username, u.first_name 
FROM notifications n
    JOIN messages m
        ON n.feature_id = m.id
    JOIN users u
        ON m.sender = u.id
WHERE n.user_id = :userid