如何加入与未知条件像 mysql 中的支点

标签: MySQL PHP
发布时间: 2017/4/9 23:08:38
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有下列代码

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();
    }
}

Db 配置文件等含有像数据库名称信息

    $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 然后尝试联接条件,但我得到的错误有任何的建议和帮助

解决方法 1:

我不会帮你整个查询 (我只会列出一些问题),但这"案例-加入"可能解决与 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 太多。它意味着将附加到左无论如果结果正确的比赛 (然后联接 null 列) 上的任何行。它是慢,给大型数据集和用空值的结果,大部分的这些列似乎无用。
  • 加入与子查询 GROUP BY 子句来获取连接的标记
  • 如果此数据大部分将重复行 (见︰ N + 1 问题) 分裂查询 (和合并的子集,对常见的"标题") 可能是个好主意。
  • 一次构建您查询的一步。如果您不确信其结果的,测试子查询。开始与资源消耗部件 — 它可能会慢一点,到头来你会分裂它无论如何。
官方微信
官方QQ群
31647020