ужасный SQL запрос с множеством таблиц

phillip · 14 Апр 2011

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

PHP:

SELECT * FROM `table1`, `table2`, `table3`  WHERE `id`=2 and `t`=4

ругается что надо писать `table1`.`id` обязательно. указывать таблицу то есть.
но фишка в том что у меня id и t есть в каждой таблице, тогда мне надо писать нечто ужасное типа

PHP:

SELECT * FROM `table1`, `table2`, `table3`  WHERE ( `table1`.`id`=2 or `table2`.`id`=2 or `table3`.`id`=2) and ( `table1`.`t`=4 or `table2`.`t`=4 or `table3`.`t`=4)

и таблицы у меня не 2, а 10 на самом деле. короче получается что-то ужасное! как записать красиво?

CrashX · 14 Апр 2011

это правильно и ничего ужасного тут нет
у меня запросы обычно вида

SELECT news.id, news.cid, news.aid, news.seo, news.title, news.content, news.reference, news.hits, news.public, news.created, users.ulogin, users.unick, users.uname, users.usurname, news_category.alias, news_category.name, news_category.pid FROM news LEFT OUTER JOIN users ON news.aid = users.id LEFT OUTER JOIN news_category ON news.cid = news_category.id WHERE news.show='1' ORDER BY news.public DESC LIMIT 0, 10;

я их даже не пишу они сами генерируются)))

saen · 14 Апр 2011

phillip написал(а):
делаю выборку из нескольких таблиц, допустим

PHP:

SELECT * FROM `table1`, `table2`, `table3` WHERE `id`=2 and `t`=4

ругается что надо писать `table1`.`id` обязательно. указывать таблицу то есть.
но фишка в том что у меня id и t есть в каждой таблице, тогда мне надо писать нечто ужасное типа

PHP:

SELECT * FROM `table1`, `table2`, `table3` WHERE ( `table1`.`id`=2 or `table2`.`id`=2 or `table3`.`id`=2) and ( `table1`.`t`=4 or `table2`.`t`=4 or `table3`.`t`=4)

и таблицы у меня не 2, а 10 на самом деле. короче получается что-то ужасное! как записать красиво?

выборка из нескольких таблиц делается через JOIN путем соотношения полей между таблицами

Код:

SELECT `table1`.*, `table2`.*, `table2`.`id` as `id2`, `table2`.`t` as `t2`, `table3`.*, `table3`.`id` as `id3`, `table3`.`t` as `t3` FROM `table1`
LEFT JOIN `table2` ON `table1`.`id` = `table2`.`id`
LEFT JOIN `table3` ON `table1`.`id` = `table3`.`id`
WHERE `table1`.`id` = 2 OR `table2`.`id` = 2 OR `table3`.`id` = 2
OR `table1`.`t` = 4 OR `table4`.`t` = 4 OR `table3`.`t` = 4

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

phillip · 15 Апр 2011

XSiteCMS написал(а):
у меня запросы обычно вида
я их даже не пишу они сами генерируются)))

ммм а как это они у тебя сами генерируются? фреймворк чтоли используешь, или что =)

CrashX · 15 Апр 2011

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

PHP:

<?php

/**
 * $Revision: 502 $
 * $Author: CrashX $
 * $Date: 2010-02-02 14:27:01 +0600 (Вт, 02 фев 2010)$
 * $LastChangedDate: 2011-04-14 20:54:17 +0700 (Чт, 14 апр 2011) $
 * $Id: mysql.query.php 502 2011-04-14 13:54:17Z CrashX $
 * Copyright © CrashX <XSiteCMS@gmail.com>
 * Всі права захищено © CrashX
 */
if (!defined('_SHELL'))
  die();

/**
 * Построение запросов
 */
class QueryDB {

  /**
   * Создание текста запроса для выборки записи
   * @param поля в виде ассоциативного массива
   * @param таблица имя
   * @param критерии в виде ассоциативного массива
   * @param доплительные части запроса или директивы
   * @return mixed
   */
  function select($fields=array(), $table=null, $criteria=null) {
    $this->type = 'select';
// SELECT *ПОЛЯ* FROM *ТАБЛИЦА* WHERE *УСЛОВИЯ* *ДИРЕКТИВЫ*
    $wheres = null;
    $order = null;
    $sort = null;
    $limit = null;
    $join = null;
    $fields = $this->fields($fields, $table);
    if (is_array($criteria)):
      foreach ($criteria as $type => $data):
        switch ($type):
          case "join":
            $join = array();
            foreach ($data as $value):
              $fields .=", " . $this->fields($value['fields'], $value['table']);
              $join[] = $value['type'] . ' ' . $value['table'] . ' ON ' . $table . '.' . $value['link']['a'] . ' = ' . $value['table'] . '.' . $value['link']['b'];
            endforeach;
            $join = implode(" ", $join);
            break;
          case "order": $order = ' ORDER BY ' . $data;
            break;
          case "sort": $sort = ' ' . $data;
            break;
          case "limit": $limit = ' LIMIT ' . intval($data[0], 0) . ', ' . intval($data[1], 1);
            break;
          case "where":
            $wheres = array();
            foreach ($data as $column => $value):
              $wheres[] = "{$column}='{$value}'";
            endforeach;
            $wheres = implode(" AND ", $wheres);
            break;
          default: break;
        endswitch;
      endforeach;
    endif;
    // Собираем запрос
    $this->query = "SELECT {$fields} ";
    if (!empty($table)):
      $this->query.=" FROM {$table}";
    endif;
    if (!empty($join)):
      $this->query.=" {$join}";
    endif;
    if (!empty($wheres)):
      $this->query.=" WHERE {$wheres}";
    endif;
    if (!empty($order)):
      $this->query.=$order;
    endif;
    if (!empty($sort)):
      $this->query.=$sort;
    endif;
    if (!empty($limit)):
      $this->query.=$limit;
    endif;
    $this->query.=";";
    return true;
  }

  /**
   * Создание текста запроса для добавления записи
   * @param поля в виде ассоциативного массива
   * @param таблица имя
   */
  function insert($fields=array(), $table=null) {
// INSERT INTO *ТАБЛИЦА* *ПОЛЯ* VALUES *ЗНАЧЕНИЯ*
    $this->type = 'insert';
    $values = array();
    foreach ($fields as $field):
      $values[] = "'{$field}'";
    endforeach;
    $columns = array_keys($fields);
    $this->query = "INSERT INTO {$table} (" . implode(", ", $columns) . ")";
    $this->query.=" VALUES (" . implode(", ", $values) . ")";
    $this->query.=";";
    return true;
  }

  /**
   * Создание текста запроса для обновления записи
   * @param поля в виде ассоциативного массива
   * @param таблица имя
   * @param критерии в виде ассоциативного массива
   * @return mixed
   */
  function update($fields, $table, $criteria) {
// UPDATE *ТАБЛИЦА* SET *ПОЛЯ* WHERE *УСЛОВИЯ*
    $this->type = 'update';
    $wheres = array();
    $updates = array();
    foreach ($fields as $column => $value):
      $updates[] = "{$column}='{$value}'";
    endforeach;
    $update = implode(", ", $updates);
    foreach ($criteria as $column => $value):
      $wheres[] = "{$column}='{$value}'";
    endforeach;
    $where = implode(" AND ", $wheres);
    $this->query = "UPDATE {$table}";
    $this->query.=" SET {$update}";
    $this->query.=" WHERE {$where}";
    $this->query.=";";
    return true;
  }

  /**
   * Создание текста запроса для удаления записи
   * @param таблица имя
   * @param критерии в виде ассоциативного массива
   * @return mixed
   */
  function delete($table, $criteria) {
// DELETE *?!ПОЛЯ!?* FROM *ТАБЛИЦА* WHERE *УСЛОВИЯ*
    $this->type = 'delete';
    $wheres = array();
    foreach ($criteria as $column => $value):
      $wheres[] = "{$column}='{$value}'";
    endforeach;
    $where = implode(" AND ", $wheres);
    $this->query.="DELETE FROM {$table}";
    $this->query.=" WHERE {$where}";
    $this->query.=";";
    return true;
  }

  /**
   * Создание текста запроса для выборки записи
   * @param поля в виде ассоциативного массива
   * @param таблица имя
   * @param критерии в виде ассоциативного массива
   * @param доплительные части запроса или директивы
   * @return mixed
   */
  function count($table=null, $criteria=null) {
    $this->type = 'select';
// SELECT *ПОЛЯ* FROM *ТАБЛИЦА* WHERE *УСЛОВИЯ* *ДИРЕКТИВЫ*
    $wheres = null;
    $fields = null;
    if (is_array($criteria)):
      $fields = 'COUNT(' . $criteria['count']['field'] . ') AS `count`';
      $wheres = array();
      foreach ($criteria['count']['where'] as $column => $value):
        $wheres[] = "{$column}='{$value}'";
      endforeach;
      $wheres = implode(" AND ", $wheres);
    endif;
    // Собираем запрос
    $this->query = "SELECT {$fields} ";
    if (!empty($table)):
      $this->query.=" FROM {$table}";
    endif;
    if (!empty($wheres)):
      $this->query.=" WHERE {$wheres}";
    endif;
    $this->query.=";";
    return true;
  }

  function procedure($fields, $procedure, $result='@result') {
//CALL *ИМЯ ПРОЦЕДУРЫ* (*ЗНАЧЕНИЯ*,*РЕЗУЛЬТАТ*)
    $values = array();
    foreach ($fields as $field):
      $values[] = "'{$field}'";
    endforeach;
    $columns = array_keys($fields);
    if ($result && !empty($result))
      $result = ',' . $result;
    $this->query = "CALL " . $procedure . "(" . implode(", ", $values) . $result . ");";
  }

}

?>

пример описания полей

PHP:

$metadata = array(
// TABLE
    'table' => 'news',
// FIELD
    'field' => array(
        'id' => null,
        'cid' => null,
        'aid' => null,
        'seo' => null,
        'title' => null,
        'content' => null,
        'reference' => null,
        'hits' => null,
        'public' => null,
        'created' => null,
    ),
    'directive' => array(
        'count' => array(
            'field' => 'news.id',
            'where' => array('news.show' => 1),
        ),
        'join' => array(
            array(
                'fields' => array(
                    'ulogin' => '',
                    'unick' => '',
                    'uname' => '',
                    'usurname' => '',),
                'type' => 'LEFT OUTER JOIN',
                'table' => 'users',
                'link' => array(
                    'a' => 'aid',
                    'b' => 'id')
            ),
            array(
                'fields' => array(
                    'alias' => '',
                    'name' => '',
                    'pid' => '',),
                'type' => 'LEFT OUTER JOIN',
                'table' => 'news_category',
                'link' => array(
                    'a' => 'cid',
                    'b' => 'id')
            ),
        ),
        'where' => array('news.show' => '1'),
        'order' => 'news.public',
        'sort' => 'DESC',
        'limit' => array(0, 10),
    ),

Генерация

PHP:

   $engine->db->select($this->metadata['field'], $this->metadata['table'], $this->metadata['directive']);

так писать не стоит для удобства пишите текстом, что бы было вам и другим понятнее.

а это пример того как можно, но возможно ненужно) просто у меня цель немного другая динамические формы поля и тп те контруктор конструктора.

phillip · 15 Апр 2011

ок, спасибо! я вот чего понять не могу... условно говоря, мне надо сделать чтобы в одной из таблиц и id и t был нужный, и тогда мы берем строку из этой таблицы целиком. Нельзя чтобы случайно сделалась такая выборка что id возмет из первой таблицы, а t из второй

ААааа не знаю как выразиться понятнее :-]

chang · 15 Апр 2011

phillip
он же saen написал как это делается ... просто конкретизирую "переменную" названием таблицы
`table2`.`id` as `id2`, `table2`.`t` as `t2`,

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

типа

PHP:

SELECT  t1.id  as id_t1,  t2.id as id_t2
FROM  table1  as t1
LEFT JOIN   table2 as t2  ON  t1.id = t2.to_id

таким макаром данные никогда не будут пересекаться или взаимозаменяются

phillip · 16 Апр 2011

Блин все равно туплю, не понимаю я с этими джойнами. Помогите плиз разобраться раз и навсегда, чето я все медлил с этим, надоело!

Допустим так... Есть таблица users в ней
1 Маша
2 Петя
3 Света
_______

есть таблицы potato (картошка) tomato (помидоры) bananos (бананы)

При этом, у картошки, томатов, бананов разные характеристики совершенно: допустим у картошки это цена и сорт. У помидоры- размер и цвет, у банана сколько часов пролежал на складе. НО одно поле у всех совпадает, это поле ownerid (владелец данной картошки или помидорки или банана) и поле exist (наличие на складе).

Зная что у Маши айди=1, как собрать воедино все все фрукты и овощи из всех таблиц, у которых ownerid=1 и exist=Y?

Я счас делаю тупо три разных запроса. Сперва ковыряю таблицу помидор, и достаю все ownerid=1 c exist=Y, затем то же проделываю с бананами и картошкой

Попробовал составить как вы научили, возвращает пустой массив
нид хелп! надоело тупить :tut:

CrashX · 16 Апр 2011

Для просмотра скрытого содержимого вы должны войти или зарегистрироваться.

наглядный пример что с чем и как пересекается когда делаешь join
для отладки запросов рекомендую
dbForge Studio (для русских бесплатно)

Для просмотра скрытого содержимого вы должны войти или зарегистрироваться.

ужасный SQL запрос с множеством таблиц

phillip

Полезный

CrashX

В прошлом XSiteCMS

saen

Постоялец

phillip

Полезный

CrashX

В прошлом XSiteCMS

phillip

Полезный

chang

Постоялец

phillip

Полезный

CrashX

В прошлом XSiteCMS