Ну и запросы у вас...
Dec. 29th, 2006 03:12 amНаписал тут небольшой SQL запросик. Написав охренел от написанного. Интересно сможет ли кто-нибудть кроме меня и постгресса это прочитать? А понять?
select Digest.ITEM_ID as DIGEST,
Digest.MESSAGE_FORMAT as FORMAT,
SubscriptionLink.ACTIVE as SUBSCRIPTION,
Digest_VariantLink.ACTIVE as VARIANT,
Digest_TemplateLink.ACTIVE as TEMPLATE,
AuthorTable.ITEM_ID as ADDRESSEE
from Digest,
Item_Link as SubscriptionLink,
Item_Link as Digest_VariantLink,
Item_Link as Digest_TemplateLink,
Item_Link as AddresseeLink,
Item as AuthorTable
where Digest.PERIOD=?
and SubscriptionLink.PASSIVE=Digest.ITEM_ID
and SubscriptionLink.LINKTYPE_ID='SUBSCRIPTION'
and Digest_VariantLink.PASSIVE=Digest.ITEM_ID
and Digest_VariantLink.LINKTYPE_ID='DIGEST_VARIANT'
and Digest_TemplateLink.PASSIVE=Digest.ITEM_ID
and Digest_TemplateLink.LINKTYPE_ID='DIGEST_TEMPLATE'
and AddresseeLink.PASSIVE=Digest.ITEM_ID
and AddresseeLink.LINKTYPE_ID='ADDRESSEE'
and AuthorTable.ITEM_ID=AddresseeLink.ACTIVE
and AuthorTable.TYPE_ID='AUTHOR')
union
(select Digest.ITEM_ID as DIGEST,
Digest.MESSAGE_FORMAT as FORMAT,
SubscriptionLink.ACTIVE as SUBSCRIPTION,
Digest_VariantLink.ACTIVE as VARIANT,
Digest_TemplateLink.ACTIVE as TEMPLATE,
AuthorTable.ITEM_ID as ADDRESSEE
from Digest,
Item_Link as SubscriptionLink,
Item_Link as Digest_VariantLink,
Item_Link as Digest_TemplateLink,
Item_Link as AddresseeLink,
Item as UserGroupTable,
Item_Rel as Included_User_GroupLink,
Item as AuthorTable
where Digest.PERIOD=?
and SubscriptionLink.PASSIVE=Digest.ITEM_ID
and SubscriptionLink.LINKTYPE_ID='SUBSCRIPTION'
and Digest_VariantLink.PASSIVE=Digest.ITEM_ID
and Digest_VariantLink.LINKTYPE_ID='DIGEST_VARIANT'
and Digest_TemplateLink.PASSIVE=Digest.ITEM_ID
and Digest_TemplateLink.LINKTYPE_ID='DIGEST_TEMPLATE'
and AddresseeLink.PASSIVE=Digest.ITEM_ID
and AddresseeLink.LINKTYPE_ID='ADDRESSEE'
and UserGroupTable.ITEM_ID=AddresseeLink.ACTIVE
and UserGroupTable.TYPE_ID='USER_GROUP'
and Included_User_GroupLink.PASSIVE=UserGroupTable.ITEM_ID
and Included_User_GroupLink.LINKTYPE_ID='INCLUDED_USER_GROUP'
and AuthorTable.ITEM_ID=Included_User_GroupLink.ACTIVE
and AuthorTable.TYPE_ID='AUTHOR'
)
order by SUBSCRIPTION,VARIANT,TEMPLATE,FORMAT