php - 如何在mysql中加入像pivot这样的未知条件

Question

我有以下代码

public function get_posted_questions($data) {

    include dirname(__FILE__) . "/database.php";
    $user_db_name = $dbconfig[$data['college_id']]['database'];

    if (isset($data['start'])) {
        $start = $data['start'];
    } else {
        $start = 0;
    }
    if (isset($data['end']) and ! empty($data['end'])) {
        $end = $data['end'];
    } else {
        $end = 30;
    }

    /*
     * multiple college database funda here goes
     */
    $max_college_id = "
        SELECT DISTINCT college_id
        FROM just_ask_question
        WHERE
            status = '1'
            AND isDeleted = '0'
            AND college_id !='0'
        ORDER BY id DESC
        LIMIT $start, $end
    ";
    $max_college_id_run = mysql_query($max_college_id);



    $question_data = array();
    $question_query = "
        SELECT
            Q.id,
            Q.title,
            Q.description,
            Q.user_id,
            Q.college_id,
            Q.datetime,
            IFNULL(GROUP_CONCAT(DISTINCT T.name),'') AS tags,
            IFNULL(CONCAT_WS(' ',U.firstName,U.lastName),'') AS user_name
            IFNULL(U.image,'') AS image,
            IFNULL(V.id,'') AS no_of_view,
            IFNULL(Vote.upvote,'') AS up_vote,
            IFNULL(answer.id,0) AS no_of_answer,
            IFNULL(is_upvote,0) AS upvote_status,
            category_name
        FROM just_ask_question AS Q 
        LEFT JOIN just_ask_question_tag Qt
            ON Qt.question_id = Q.id 
        LEFT JOIN just_ask_tag T
            ON T.id = Qt.tag_id 
        LEFT JOIN just_ask_category 
            ON just_ask_category.id = Q.category_id
        LEFT JOIN
            (
                SELECT COUNT(id) as id, question_id FROM just_ask_answer
            ) AS answer ON answer.question_id = Q.id
    ";

    while ( $row = mysql_fetch_assoc($max_college_id_run) ) {
        $user_db_name = $dbconfig[$row['college_id']]['database'];
        $question_query .= "
            CASE WHEN Q.college_id = '".$row['college_id']."'
                THEN LEFT JOIN $user_db_name.users U ON U.id = Q.user_id
        ";
    }

    $question_query .= "
        LEFT JOIN
            (
                SELECT count(id) AS id, question_id FROM just_ask_view
            ) AS V ON V.question_id = Q.id
        LEFT JOIN
            (
                SELECT
                    COUNT(upvote) as upvote,
                    question_id
                FROM just_ask_upvote_downvote
                WHERE upvote = '1' AND is_question = '1'
                GROUP BY question_id
            ) AS Vote 
            ON Vote.question_id = Q.id 
        LEFT JOIN
            (
                SELECT
                    IF(COUNT(id) > 0,1,0) AS is_upvote,
                    question_id
                FROM just_ask_upvote_downvote
                WHERE
                    upvote = '1'
                    AND college_id = '" . $data['college_id'] . "'
                    AND user_id = '" . $data['user_id'] . "'
                    AND is_question = '1'
            ) AS Is_Vote
            ON Is_Vote.question_id = Q.id
        WHERE
            Q.status = '1'
            AND Q.isDeleted = '0'
            AND CASE
                WHEN Q.visibility = 0 AND Q.college_id != 0 THEN Q.college_id = '" . $data['college_id'] . "'
                ELSE true
                END
    ";




    if (!empty($data['search_text'])) {
        $search_text = $data['search_text'];
        $question_query .= " and (Q.title like '%$search_text%' or Q.description like '%$search_text%' or T.name like '%$search_text%')";
    }

    $question_query .= " group by Q.id order by Q.id desc limit $start,$end";

    $question_query_run = mysql_query($question_query);

    /* get weather question exist */
    $check_num_rows = mysql_num_rows($question_query_run);
    if ($check_num_rows > 0) {
        while ($row = mysql_fetch_assoc($question_query_run)) {
            if ($row['image'] != '') {
                $row['thumbnail'] = USER_THUMBNAIL_URL . $row['image'];
                $row['image'] = IMAGE_URL . $row['image'];
            } else
                $row['thumbnail'] = '';
            $question_data[] = $row;
        }
        $status['statuscode'] = "1";
        $status['statusmessage'] = "ok";
        $status['question_data'] = $question_data;
        $response['response'] = $status;
        echo json_encode($response);
        die();
    }else {
        $status['statuscode'] = "2";
        $status['statusmessage'] = "There is no record found";
        $response['response'] = $status;
        echo json_encode($response);
        die();
    }
}

以及数据库配置文件，其中包含数据库名称信息就像

    $dbconfig['1'] = array(
        'host' => 'localhost',
        'user_name' => 'root',
        'password' => 'test123*',
        'database' => 'staging_myuniversity'
    );


$dbconfig['2'] = array(
        'host' => 'localhost',
        'user_name' => 'root',
        'password' => 'test123*',
        'database' => 'staging_myuniversity_dias'
);

数据库驻留在同一服务器上并具有完全权限。

查询像这样生成

select  Q.id,Q.title,Q.description,Q.user_id,Q.college_id,
       Q.datetime,
       ifnull(group_concat(distinct T.name),'') as tags,
       ifnull(CONCAT_WS(' ',
                       U.firstName,U.lastName),'') as user_name,
       ifnull(U.image,
               '') as image,ifnull(V.id,'') as no_of_view,
       ifnull(Vote.upvote,
               '') as up_vote,
       ifnull(answer.id,0) as no_of_answer,
       ifnull(is_upvote,
               0
             ) as upvote_status,
       category_name
    from  just_ask_question as Q
    left join  just_ask_question_tag Qt on Qt.question_id = Q.id
    left join  just_ask_tag T on T.id = Qt.tag_id
    left join  just_ask_category on just_ask_category.id = Q.category_id
    left join  
      ( SELECT  count(id) as id,question_id
            from  just_ask_answer
      ) as answer on answer.question_id = Q.id case when Q.college_id = '1' then
    left join  staging_myuniversity.users U on U.id = Q.user_id case when Q.college_id = '12'then
    left join  campify_solutions_mathura.users U on U.id = Q.user_id case when Q.college_id = '4' then
    left join  staging_myuniversity_nit_kkr.users U on U.id = Q.user_id case when Q.college_id = '2' then
    left join  staging_myuniversity_dias.users U on U.id = Q.user_id
    left join  
      ( SELECT  count(id) as id,question_id
            from  just_ask_view 
      ) as V on V.question_id = Q.id
    left join  
      ( SELECT  count(upvote) as upvote,question_id
            from  just_ask_upvote_downvote
            where  upvote = '1'
              and  is_question = '1'
            group by  question_id
      ) as Vote on Vote.question_id = Q.id
    left join  
      ( SELECT  if(count(id) > 0,1,0) as is_upvote,question_id
            from  just_ask_upvote_downvote
            where  upvote = '1'
              and  college_id = '1'
              and  user_id = '1'
              and  is_question = '1'
      ) as Is_Vote on Is_Vote.question_id = Q.id
    where  Q.status = '1'
      and  Q.isDeleted = '0'
      and  case when Q.visibility = 0
      and  Q.college_id != 0 then Q.college_id = '1' else true end
    group by  Q.id
    order by  Q.id desc
    limit  0,30

我需要根据大学ID连接不同的数据库首先我从问题中获取不同的大学 ID，然后进行循环对于大学 ID 并尝试制定加入条件，但我收到错误，有任何建议和帮助

Answer 1

我不会帮助您完成整个查询(我只会列出一些问题)，但是这个“case-join”可以通过这样的 UNION ALL 子查询来解决:

INNER JOIN (
    SELECT 1 AS college_id, id, firstName, lastName, image
    FROM staging_myuniversity.users
    UNION ALL
    SELECT 12 AS college_id, id, firstName, lastName, image
    FROM campify_solutions_mathura.users
    UNION ALL
    ...
) AS U ON U.college_id = Q.college_id AND U.id = Q.user_id

• 当您将计数器与子查询​​(例如 SELECT count(), some_id)连接时，也使用 GROUP_BY some_id。否则，您将计算表中的所有行，并仅针对一个(第一个/随机)ID 连接此结果(如果它通过且没有错误)。
• 看起来您使用LEFT JOIN太多了。这意味着无论右侧的任何行是否匹配，都附加到左侧的结果(然后连接空列)。它很慢，提供大量数据集，并且大多数列的结果为空，似乎毫无用处。
• 使用 GROUP BY 子句连接子查询以获取连接标签
• 如果大部分数据在行中重复(请参阅:N+1 问题)，则拆分查询(并将子集合并到公共(public)“ header ”)可能是个好主意。
• 一次一步构建您的查询。如果您不确定其结果，请测试子查询。从资源消耗部分开始 - 它可能会在某些时候变慢，而且无论如何你最终都会将其拆分。