Как выбрать связные записи из таблиц?

Question

[Евгений]

Здравствуйте,
Есть три связные таблицы для документов, их версий и статусов: Document(id, title), DocumentRevision(id, time, documentId) и RevisionStatus (id, time, revisionId, status). Статусы могут быть: 0-новый, 1-отправлен на проверку, 2-отклонен, 3-принят. Соответственно для документа текущим является последний статус последней ревизии. И если по отдельности выбрать документ, его версии и статусы не составляет проблемы, то подскажите, пожалуйста, как выбрать все документы, отправленные на проверку?

upd:
Всем спасибо, хоть и можно решить задачу и с помощью сложного запроса, решил денормализовать базу — проще работать и отслеживать.

Answer 1

[truezemez]

select d.* from Document as d
join DocumentRevision as dr on d.id = dr.documentId
join RevisionStatus as rs on dr.id = rs.revisionId
where rs.status = 1

Comments

[truezemez]

Я ошибся. Пропустил это:

Соответственно для документа текущим является последний статус последней ревизии.

Answer 2

[rakeev]

На текущей схеме примерно так:

SELECT d.* FROM Document d JOIN DocumentRevision r ON r.id = (SELECT MAX(id) FROM DocumentRevision WHERE documentId = d.id) JOIN RevisionStatus s ON s.id = (SELECT MAX(id) FROM RevisionStatus WHERE revisionId = r.id) AND s.status = 1

А вообще денормализуйте — копируйте последний статус и номер ревизии в Document, триггером или еще как.

Comments

[Евгений]

Добавить поле, скажем lastRevisionId, lastStatusId не долго. А как именно лучше будет поддерживать их в актуальном состоянии?

[rakeev]

Триггерами на insert в соответствующие таблицы.

[Евгений]

А будет ли нормальным вариантом, скажем, описать все это дело в модели данных (проект на yii)? Насколько это чревато будет? Понимаю, что будут лишние запросы сначала на выборку, а потом на сохранение, но с другой стороны про код не забудешь в базе и на небольшой (меньше 1000 человек в сутки) нагрузке не сильно скажется, верно?

// В DocumentRevision
function afterSave{
  if ($this->scenario == 'create'){
    $document->lastRevisionId = $this->id;
    $document->save(false);
  }
}

[rakeev]

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

Answer 3

[servekon]

Как то так:

SELECT title FROM document AS d
INNER JOIN documentrevision AS dr ON dr.documentid=d.id
WHERE dr.id = (SELECT revisionid FROM revisionstatus WHERE status=1);

Answer 4

[bo2]

Поддерживаю, обязательно денормализуйте. Триггеры в приложении — абсолютно нормально, в базе — скорее экзотика.
А вот вариант без подзапросов (подразумевается тип datetime для полей времени):

select
	substr(max(concat(
		DocumentRevision.time, ":",
		RevisionStatus.time, ":",
		RevisionStatus.status, ":",
		Document.id
	)), 43) docid
from
	Document, DocumentRevision, RevisionStatus
where
	DocumentRevision.documentId = Document.id and
	RevisionStatus.revisionId = DocumentRevision.id
group by
	Document.id
having
	substr(max(concat(
		DocumentRevision.time, ":",
		RevisionStatus.time, ":",
		RevisionStatus.status, ":",
		Document.id
	)), 41, 1) = "1"