From: Peter Brawley (peter.brawleyearthlink.net)
Date: Mon Feb 11 2008 - 04:18:54 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Yves

>it will rather find messages that have no tag with a keylist
>which does not include the currently logged in user's UserId
>or one of this user's additional keys, which are again stored
>in a keylist.

Unclear.

PB

Yves Goergen wrote:
> On 11.02.2008 00:29 CE(S)T, Peter Brawley wrote:
>> message (messageID)
>> keylist (keylistID)
>> tag ( tagID, readaccesskeylist references keylist(keylistID) )
>> message_revision_tag ( ???, messageID references message(messageID),
>> tagID references tag(tagID))
>
> Another table:
> message_revision(MessageId references message, RevisionNumber)
>
> Correction:
> message_revision_tag(MessageId, RevisionNumber, TagId)
> (MessageId, RevisionNumber) references message_revision
>
>> (i) Finding messages which have a deny-access tag looks like a simple
>> join:
>>
>> SELECT DISTINCT messageID
>> FROM message_revision_tag AS mrt
>> JOIN tag AS t ON mrt.tagID=t.tagID
>> WHERE t.readaccesskeylist IS NOT NULL;
>>
>> (ii) We get the messages not in the above result with a simple
>> exclusion join:
>>
>> SELECT messageID
>> FROM message m
>> LEFT JOIN (
>> SELECT DISTINCT messageID
>> FROM message_revision_tag AS mrt
>> JOIN tag AS t ON mrt.tagID=t.tagID
>> WHERE t.readaccesskeylist IS NOT NULL
>> ) AS banned USING (messageID)
>> WHERE banned.messageID IS NULL,
>>
>> Or did I miss something?
>
> I'm afraid I cannot integrate this in my large query. It looks too
> simple and I don't know where to put its parts. Maybe I'll really have
> to show the full schema and the complete query...
>
> It's not only that my entire query will find messages that have no tag
> with a ReadAccessKeylist assigned; it will rather find messages that
> have no tag with a keylist which does not include the currently logged
> in user's UserId or one of this user's additional keys, which are
> again stored in a keylist.
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]