Как лучше составить SQL запрос?

Question

[Сергей]

Пытаюсь сделать хороший, нормально читаемый SQL запрос для базы следующего вида:

Таблицы:
users [id, name] // больше полей для примера не нужно
users_events [id, timestamp, event_type, event_value, user_id]
users_data [id, timestamp, data_name, data_value, user_id]

В users_events добавляются данные после каждого события у пользователя. Пример события: смена места жительства.
Выглядит как:

{ 
  id: 123, 
  timestamp: '10.03.13', 
  event_type: 'city',
  event_value: 'Moscow', 
  user_id: '888'
}

В users_data хранятся параметры пользователей, например — Имя пользователя (и если оно сменится, то старая запись останется, а последняя будет использоваться и данные из нее будут выводиться). Другой пример — Номера телефонов. Вот их может быть несколько и все могут быть активные.

{
  id:122,
  timestamp: , // некая дата
  data_name: 'last_name',
  data_value: 'Vasechkin', // сначала было старое имя
  user_id: 888
}
{
  id:123,
  timestamp: , // некая дата
  data_name: 'last_name',
  data_value: 'Pupkin', // потом заменили на новое
  user_id: 888
}
{
  id:124,
  timestamp: , // некая дата
  data_name: 'phone',
  data_value: '1234567',
  user_id: 888
}
{
  id:125,
  timestamp: , // некая дата
  data_name: 'phone',
  data_value: '1233214',
  user_id: 888
}

Собственно, какой нужен запрос:
Нужно выбирать из базы таких пользователей, у которых последнее место жительства, например, 'Moscow', а также активное (последнее) имя LIKE %pup% и, в добавок, в одном из номеров есть цифра 7

+ нужно подсчитать количество записей в базе (на join'ах мой COUNT() почему-то ломался).

Сразу скажу, что довольно важна читаемость и доделываемость. Будет просто супер, если запрос впишется в API баз CodeIgnitter'a

PS: Следует упомянуть то, что переделывать структуру базы — не вариант, так как она активно используется и там куча данных, которые никак нельзя терять.

Answer 1

[Sayonji]

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

select users.* from users

inner join user_events cities on users.id = cities.user_id
inner join
(select max(e.id) id, user_id from user_events e where event_type='city' group by user_id)
cities_fresh on cities_fresh.id = cities.id

inner join user_data names on users.id = names.id
inner join
(select max(d.id) id, user_id from user_data d where data_name='last_name' group by user_id)
names_fresh on names_fresh.id = names.id

inner join user_data phones on user.id = phones.user_id

where phone like '%7%' and city = 'Moscow' and last_name like '%uti%'

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

Comments

[Sayonji]

А чтобы узнать количество строчек возвращенных, нужно чуточку изменить запрос: вместо первой строчки пишем
select users.id id, users.name name, users.pass pass ... from users
а после последней добавляем
union select found_rows() id, "totally" name, "totally" pass ...

[Сергей]

Спасибо, все очень наглядно и аккуратно — у меня было почти тоже самое (только не так красиво), вот только я не знал как подсчитать количество записей. Сейчас буду пытаться пробовать использовать. Есть вопросик:

Если в строчке

where phone like '%7%' and city = 'Moscow' and last_name like '%uti%'

нет ошибки, тогда я в упор не понимаю как она работает. Откуда берутся 'phone', 'city', 'last_name'? Или это «доделай сам»?

[Сергей]

И насчет found_rows(), он, насколько я понял, добавляется в конец? Если у меня есть order by users.id desc, то оно не потеряется? Ведь order после union делается. И если у меня есть limit, то как получить из запроса количество записей? Может быть, надо делать второй запрос, похожий на первый, но только чтобы он только выводил количество записей?

[Сергей]

Подсчет количества записей сделал через второй отдельный запрос

select found_rows() total 

И в основном запросе надо изменить первую строчку

select users.* from users

на

select SQL_CALC_FOUND_ROWS users.* from users

[Sayonji]

По поводу, `phone`, `city`, `last_name`, они должны браться из заджойненных phones, cities, names. Я сглупил, нужно через точки писать. phones.phone и т. д.

[Сергей]

Да да, я так и сделал. Только в нашем случае там phones.data_value, cities.event_value и т.п.

[Sayonji]

По поводу второго, я все запихнул в union, потому что пишу на голом php и не умею делать транзакции =) Если на обычном php написать два запроса подряд, то, особенно если первый долгий, между ними вполне может выпасть чей-то еще select, и found_row() вернет не то значение.

[Сергей]

Насчет того что может влезть запрос из другого соединения с базой я не уверен (загуглил — не нашел под наш случай), на всякий случай обернул все в транзакцию, благо у Code Igniter есть для них простейший синтаксис.