Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> How to do tags?
 

Pages: 1
LLBLGen Pro Runtime Framework
How to do tags?
Page:1/1 

  Print all messages in this thread  
Poster Message
Pablo
User



Location:
Sneek
Joined on:
21-Mar-2005 01:17:52
Posted:
81 posts
# Posted on: 28-Jun-2007 10:40:23.  
Hello you all,
I could use some help. I have a small 'address'-book application where I offer the feature of assigning tags to contacts. The flexibility this tags give to the user is absolutely wonderfull..Regular Smiley

The tags are being used for a search-command that includes contacts based on some basic search criteria and that have one or more tags assigned. Example:
Give me
- all contacts that have a name="bla",
- AND have assigned the following tags: "press" and "online".

I've got this working fine...

However, we now need to enhance the search-feature, where we can also exclude contacts that are assigned one or more tags. Example:
Give me
- all contacts that have a name="bla",
- AND have assigned the following tags: "press" and "online",
- AND do not have assigned the tags "to_be_deleted" and "no_more_mail"

The problem is that I don't see how to solve this new construct.
(A simplified version of) the solution I have sofar contains the following tables:

Table: contact
Fields: contactid (PK), firstname, lastname

Table: tag
Fields: tagid (PK), name

Table: contact_tag
Fields: contactid (PK), tagid (PK)

Anyone have any ideas on how to do this? Any help much appreciated!

Paul







  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14470 posts
# Posted on: 28-Jun-2007 11:08:19.  
God, I love these tricky questions. Regular Smiley
Quote:

Give me
- all contacts that have a name="bla",
- AND have assigned the following tags: "press" and "online",
- AND do not have assigned the tags "to_be_deleted" and "no_more_mail"

Table: contact
Fields: contactid (PK), firstname, lastname

Table: tag
Fields: tagid (PK), name

Table: contact_tag
Fields: contactid (PK), tagid (PK)


Check the following query:
Code:
SELECT *
FROM contact
WHERE firstname = "bla"
AND contactid IN
(SELECT contactid FROM contact_tag INNER JOIN tag ON ... WHERE name = "press" )
AND contactid IN
(SELECT contactid FROM contact_tag INNER JOIN tag ON ... WHERE name = "online" )
AND contactid NOT IN
(SELECT contactid FROM contact_tag INNER JOIN tag ON ... WHERE name = "to_be_deleted" )
AND contactid NOT IN
(SELECT contactid FROM contact_tag INNER JOIN tag ON ... WHERE name = "no_more_mail" )


If the above query is the correct one, then you can use FieldCompareSetPredicate to implement the IN & NOT IN predicates.


  Top
Pablo
User



Location:
Sneek
Joined on:
21-Mar-2005 01:17:52
Posted:
81 posts
# Posted on: 28-Jun-2007 11:59:21.  
Walaa,
I'm glad you like those tricky questions AND come up with a perfect solution ... subqueries!!

I did not come further then just liking them Wink

I tested it in SQL ... works super and it easy to understand as well.
I'll go on and get it done in LLBLGEN code.

Thanx!!!

Paul
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14470 posts
# Posted on: 28-Jun-2007 14:54:34.  
Quote:
I'm glad you like those tricky questions AND come up with a perfect solution ... subqueries!!

I like puzzles, and the thing about SQL questions, is that they force me to use my brain .
Sometimes I succeed and sometimes I fail, so I seek help. Regular Smiley


  Top
Pablo
User



Location:
Sneek
Joined on:
21-Mar-2005 01:17:52
Posted:
81 posts
# Posted on: 28-Jun-2007 16:06:32.  
Walaa,
I've got it done in LLBLGEN code as well. It's working perfectly!!!
Thanx again for the help!!
Paul
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.