75€ BOUNTY - Massively Complicated Query Required

[Jason Brower]

I have the event management tool I have been working on for 2 years now and I have made some changes to the code that require my most complicated query to be rebuilt.
After thinking about it for several hours I figure I should throw it at you guys to see what you can come up with.
The winner of this query will get 75� with paypal or other means after I have it implemented it in the code.
I have the following model:
model_diagram.png
event_attender.py
tag.py
tag_link.py
Description:
I am building a list of connections based on relevance by tag.
Tags have two states, looking for and attribute.
Users attach themselves to these tags with the two attributes.
"I am looking for someone with this tag."
and/or
"This tag is something about me that I want to offer."

Now when they do this a list of users will show on the screen.

ID	Nickname	Tags your looking for that they have	Your Relevance	Tags they are looking for that you have	Their Relevance	Total Relevance	Image	city
101	encompass	Tag1, Tag2, Tag4	3	Tag4, Tag5, Tag 6	3	10	http://....	Oulu
102	encompass	Tag1, Tag2, Tag4	3	Tag4, Tag 6	3	8	http://....	Oulu
150	encompass	Tag1, Tag2, Tag4	3	Tag 6	3	7	http://....	Helsinki

This is build with the following:
UserA is the user logged in using the system
If another user has a tag that UserA is looking for, then relevance is +2 to UserA
If another user is looking for something UserA has, then relevance is +1 to UserA
If another user lives in the same city as UserA and has something they are looking for or something to offer then relevance is +1
Does this make sense? I just need to query that will happen.� I can handle the view just fine. Feel free to comment or ask questions.
If you can think of a good way to connect people better with the data in the model diagram attached do tell.� I will give you another 25� for that implementation as well. :)
---
BR,
Jason Brower

[Marin Pranjić]

I am not sure if I understand your model. Can you explain "tag_link" ?

Marin

On Thu, May 17, 2012 at 6:28 PM, Jason Brower <enco...@gmail.com> wrote:

I have the event management tool I have been working on for 2 years now and I have made some changes to the code that require my most complicated query to be rebuilt.
After thinking about it for several hours I figure I should throw it at you guys to see what you can come up with.

The winner of this query will get 75€ with paypal or other means after I have it implemented it in the code.

Does this make sense? I just need to query that will happen.  I can handle the view just fine. Feel free to comment or ask questions.
If you can think of a good way to connect people better with the data in the model diagram attached do tell.  I will give you another 25€ for that implementation as well. :)
---
BR,
Jason Brower

[Jason Brower]

It is a "many-to-many" but it can link to multiple tables.
event is the event that a user can attend
table_link is the ID that is used in a particular table.
tag is the tag.id that this row is linking to.
intent is as described before.
tag_type is the table that the system will link to.
Notice there is a booth, auth_user, and happening table.� All of those support tags and instead of needing to create a table for each one, I combined them. Aslo comes in handy in other ways too. Like if I want to create tag relations on other parts of my database.� I could even create tag relations on tags or other tag relations.� Very powerful.
br,
Jason

On 05/17/2012 08:32 PM, Marin Pranjić wrote:

I am not sure if I understand your model. Can you explain "tag_link" ?

Marin

On Thu, May 17, 2012 at 6:28 PM, Jason Brower <enco...@gmail.com> wrote:

I have the event management tool I have been working on for 2 years now and I have made some changes to the code that require my most complicated query to be rebuilt.
After thinking about it for several hours I figure I should throw it at you guys to see what you can come up with.

The winner of this query will get 75� with paypal or other means after I have it implemented it in the code.

Does this make sense? I just need to query that will happen.� I can handle the view just fine. Feel free to comment or ask questions.
If you can think of a good way to connect people better with the data in the model diagram attached do tell.� I will give you another 25� for that implementation as well. :)
---
BR,
Jason Brower

[Andrew]

If another user has a tag that UserA is looking for, then relevance is +2 to UserA
If another user is looking for something UserA has, then relevance is +1 to UserA
If another user lives in the same city as UserA and has something they are looking for or something to offer then relevance is +1

So, is the third similar to the first, but with a city clause.   Sounds like it's a +2 if you have something UserA is looking for, but it's only +1 if you live in the same city ?

Andrew W

[Andrew]

"So, is the third similar to the first, but with a city clause.   Sounds like it's a +2 if you have something UserA is looking for, but it's only +1 if you live in the same city ?
"

Oh, I see from your example that it is an additional +1 if you are in the same city.   Got it.

[Jason Brower]

For the third one this is to further show the relevance of a user because they are living near you, but they should be someone that at least has something in common with you.
BR,
Jason Brower

[Carlos Costa]

I found a way quite simple but It does not include the Relevance columns.

2012/5/17 Andrew <awill...@gmail.com>

--
Carlos J. Costa
Cientista da Computação
Esp. Gestão em Telecom

EL MELECH NEEMAN!
אָמֵן

[Jason Brower]

On 05/17/2012 09:29 PM, Andrew wrote:

"So, is the third similar to the first, but with a city clause.   Sounds like it's a +2 if you have something UserA is looking for, but it's only +1 if you live in the same city ?
"

Oh, I see from your example that it is an additional +1 if you are in the same city.   Got it.

Yes, that is correct.
BR,
Jason Brower

[Jason Brower]

The relevance columns are vital as I need to know how strong the relation is to UserA.
It's used in a tag cloud module that I have built.
BR,
Jason

[Marin Pranjić]

and why both tag and tag_link have an event.id field? is this redundant?

On Thu, May 17, 2012 at 7:56 PM, Jason Brower <enco...@gmail.com> wrote:

It is a "many-to-many" but it can link to multiple tables.
event is the event that a user can attend
table_link is the ID that is used in a particular table.
tag is the tag.id that this row is linking to.
intent is as described before.
tag_type is the table that the system will link to.

Notice there is a booth, auth_user, and happening table.  All of those support tags and instead of needing to create a table for each one, I combined them. Aslo comes in handy in other ways too. Like if I want to create tag relations on other parts of my database.  I could even create tag relations on tags or other tag relations.  Very powerful.
br,
Jason

On 05/17/2012 08:32 PM, Marin Pranjić wrote:

I am not sure if I understand your model. Can you explain "tag_link" ?

Marin

On Thu, May 17, 2012 at 6:28 PM, Jason Brower <enco...@gmail.com> wrote:

I have the event management tool I have been working on for 2 years now and I have made some changes to the code that require my most complicated query to be rebuilt.
After thinking about it for several hours I figure I should throw it at you guys to see what you can come up with.

The winner of this query will get 75€ with paypal or other means after I have it implemented it in the code.

Does this make sense? I just need to query that will happen.  I can handle the view just fine. Feel free to comment or ask questions.
If you can think of a good way to connect people better with the data in the model diagram attached do tell.  I will give you another 25€ for that implementation as well. :)
---
BR,
Jason Brower

[Jason Brower]

Yes, it can be redundant. Ignore it if you like.
BR,
Jason

[Jason Brower]

This bounty has, thankfully, been solved.� Thank you to all those that have participated!� It has saved me so much time!
BR,
Jason Brower

[Carlos Costa]

The answer?

2012/5/18 Jason Brower <enco...@gmail.com>

This bounty has, thankfully, been solved.  Thank you to all those that have participated!  It has saved me so much time!
BR,
Jason Brower

On 05/17/2012 07:28 PM, Jason Brower wrote:

I have the event management tool I have been working on for 2 years now and I have made some changes to the code that require my most complicated query to be rebuilt.
After thinking about it for several hours I figure I should throw it at you guys to see what you can come up with.

The winner of this query will get 75€ with paypal or other means after I have it implemented it in the code.

Does this make sense? I just need to query that will happen.  I can handle the view just fine. Feel free to comment or ask questions.
If you can think of a good way to connect people better with the data in the model diagram attached do tell.  I will give you another 25€ for that implementation as well. :)
---
BR,
Jason Brower

[Jason Brower]

It is not totally implemented or verified at this point.  But so far it seems to be working.  I will post the answer later along with a special module I also made that uses this method.  I think you guys will like it.
BR,
Jason Brower