Complex SQL Query

[Dave]

Ok, maybe I am just overlooking something simple here but im tired and my brain can't get round this little problem I need to solve.

I have two database tables (blog and exchange), setup like so. The blog table has all the typical usual stuff (id, title, description) etc. The exchange table stores the id of the blog that requested the exchange and the id of the blog that has been requested, plus an enum for checking if the exchange has been accepted/declined/cancelled.

This works fine for reciprocal link building but now I want to do three-way link exchanges for pure one-way organic backlinks. The SQL query will need to select all partners of a blog from the exchange table, then fetch all that blog's partners if that other blog is also linked with the first blog. I have been trying different queries but no luck. I can't seem to get my head around it and I don't know why, maybe tiredness.

I hope I am making sense here. Any help with the query be greatly appreciated.

The exchange table simply has ID, BLOG_ID (the blog), BLOG_EXCHANGE_ID(the blog that made the request).

 

[psalzmann]

Hey Dave,

What does your current SQL query look like?

 

[Jim_]

I don't think you can do this in a single a SQL statement. I'd recommend a recursive function.

http://www.sitepoint.com/article/hierarchical-data-database/

That article has some good examples of a similar situation.

 

[qbert220]

You could use a 3 way self join to do this. This replicates the table 3 times. You can then test for multiple equalities in a single query. The following query should do what you want:

SELECT t1.BLOG_ID AS id1, t2.BLOG_ID AS id2, t3.BLOG_ID AS id3 FROM exchange as t1, exchange as t2, exchange as t3 WHERE t1.BLOG_EXCHANGE_ID=t2.BLOG_ID AND t2.BLOG_EXCHANGE_ID=t3.BLOG_ID AND t3.BLOG_EXCHANGE_ID=t1.BLOG_ID

Since a 3 way link occurs 3 times for 3 blogs (e.g. a->b->c->a, b->c->a->b, c->a->b->c) you'll end up with 3 results for each 3 way link. You could add some further logic to sort the IDs, and therefore prevent duplicate results:

SELECT t1.BLOG_ID AS id1, t2.BLOG_ID AS id2, t3.BLOG_ID AS id3 FROM exchange as t1, exchange as t2, exchange as t3 WHERE t1.BLOG_EXCHANGE_ID=t2.BLOG_ID AND t2.BLOG_EXCHANGE_ID=t3.BLOG_ID AND t3.BLOG_EXCHANGE_ID=t1.BLOG_ID AND t1.BLOG_ID<t2.BLOG_ID AND t2.BLOG_ID<t3.BLOG_ID

If you have large tables, be aware that this may use a lot of memory.

 

[Dave]

Thanks a lot I will give this a try. I cache the data anyway so memory usage will be fine. :tu:

Edit: Thanks a lot it works in general! How would I go about finding just the partners of a partner if I supply a blog id in the where clause?

So lets say I wanted to do three-way exchange just for Bob who owns bobsblog.com. The data stored in the table for bob's blog will be something like.

ID USER_ID TITLE URL
2 1 Bobs Blog bobsblog.com

And here are bobs partners.

ID USER_ID TITLE URL
4 2 Daves Blog davesblog.com
8 7 Garys Blog garysblog.com

Ok assuming they have all accepted exchanges with each other and now a user is visiting bobs blog. On the sidebar there is a plugin that sends a request to my service to fetch reciprocal or one-way backlinks for BOBS BLOG.

How on my server side do I get all of bobs partners and distinguish between whether it requires reciprocal links (in the scenario bob has only 1 partner or his partners have no partners linked with bob) or a one-way backlink (in the scenario 3 or more odd numbered blogs are all exchanging links with each other) without conflicts, duplicate links AND with flagging so we know which way the links should go and if anyone has cancelled the links.

The exchange table for the three blogs would be.

ID BLOG_ID BLOG_EXCHANGE_ID ACCEPTED (0 = no, 1 = yes)
1 2 4 1
2 4 8 1
3 8 2 1

As you can see all the blogs are connecting to each other one way or another. In the first row Daves blog (4) made a request to partner with Bobs blog (2). In the second Garys blog (8) made a request to partner with Daves blog (4). In the last row Bob (2) made a request to partner with Gary (8).

So lets say I am viewing Bobs blog.. how would I go about fetching Bobs partners (assuming we dont know their ids of course!) and determining whether it should be reciprocal or one way backlinks. In this example it will all obviously be one way backlinks, but imagine if BOB also had the following two partners that weren't connected in anyway.

ID BLOG_ID BLOG_EXCHANGE_ID ACCEPTED (0 = no, 1 = yes)
1 2 9 1
2 2 5 1

Now when I am returning the request I will also need to fetch these other partners and display them as reciprocal.

Thanks for any help to this confusing problem. :zzz:

 

[Dave]

To put it more bluntly. It would be similar to how Facebook does the 'friends of friends' and 'people you may know' tools. :hehe:

 

[qbert220]

I'm not sure exactly what results you want. Could you give some examples of a table, and some results that you want? When looking at the table are you looking at it from a particular blog's point of view (e.g. a blog with an id of 7) and trying to work out a relationship between that blog and BOBS blog?

 

[Dave]

I'm not sure exactly what results you want. Could you give some examples of a table, and some results that you want? When looking at the table are you looking at it from a particular blog's point of view (e.g. a blog with an id of 7) and trying to work out a relationship between that blog and BOBS blog?

Thats right. The table information would be how it is set out above. I want to work out the one way backlinks and reciprocal links Bob has appearing on his blog. This would always be the same so it remains a pure backlink (never changing).

 

[qbert220]

Could you give some examples of a table, and some results that you want?

 

[Dave]

Ok I will explain my above example more clearer and provide results I want. Lets say we have the following three blogs. (Seems like I can't do table bbcode at NP so ill do my best).

These rows would go in the BLOG table.

Blog A: #2, Bobs Blog, BobsBlog.com
Blog B: #5, Steves Blog, StevesBlog.com
Blog c: #8, Garys Blog, GarysBlog.com

Let's say a user is viewing Gary's blog. My wordpress plugin displays any reciprocal links that the user has exchanged links with using my service. This is simple to do and simply uses the blog's unique key as an identifier for finding Gary's partners. So on Gary's blog on the sidebar he would have links to BobsBlog and StevesBlog because he has exchanged links with them so far. So his list would like the following (in HTML).

LINKS
Steves Blog
Garys Blog

Now imagine if Steve and Bob both decide to exchange links with each other too. I want to be able to check for this when querying the database and generating the links. This requires getting Gary's partners and finding if any of his partner's are also exchanging links. If they are work out a three-way exchange between 3 or more (odd number only) partners and display this along with reciprocal links on the link list.

So the exchanges table for the Gary, Steve and Bob's blogs would be simple.

Exchange 1: #2, #5
Exchange 2: #5, #8
Exchange 3: #8, #2

These exchanges just relate to who is partnering with who and not the direction for backlinks to go in, this should be worked out when querying the database because a blog could drop out or cancel exchange at any time. There is a flag in the database for whether the exchange is still denied, accepted or cancelled.

So the after querying the database to find Gary's partners, whether reciprocal or one-way backlink, I just want to display the list of links. Remember when querying we only know the id/key of Gary's blog and no others.

Is this any better? I apologize for the confusion.

 

[qbert220]

I don't think your tables contain enough information to do what you want. You are wanting to determine the direction of the links for the 3 way links, right? But you have no way to do so. If you have:

Exchange 1: #2, #5
Exchange 2: #5, #8
Exchange 3: #8, #2

The links could go:

2->5
5->8
8->2

or

2->8
8->5
5->2

Without some extra information or some more rules (such as use ascending ids to determine 3 way links)

 

[qbert220]

I've been thinking a bit more about this. I think I now understand what you want to achieve. It is up to you to decide the rules for which dirtection links go.

With the link table:

Exchange 1: #2, #5
Exchange 2: #5, #8

We have just 2 reciprocal links:

2->5
5->2

5->8
8->5

If we now add a link between 8 and 2 we end up with:

Exchange 1: #2, #5
Exchange 2: #5, #8
Exchange 3: #8, #2

Now we have the following reciprical links:

2->5
5->2

5->8
8->5

8->2
2->8

So each blog has 2 reciprocal links. But we can achieve the same link pattern with only 1 outlink on each blog. We can choose either:

2->5
5->8
8->2

or

2->8
8->5
5->2

So we can make a rule to determine which applies. Have I got the idea now?

 

[Dave]

Yeah thats what I would like to achieve. :tu:

Apologies for the late reply I have been on holiday.

 

[qbert220]

I've been thinking about this some more. I think, although it's going to be possible in MySQL, you'll end up with a long and complicated query.

IMO you probably want to do this in PHP. Use a query to get all the links for a blog and put them in an array. Then use another query to get all the 3 way links. Where a blog is part of a 3 way link, remove one of the links from the array. My queries above are not 100% correct since they do not take into account the fact that each entry in the exchange table is actually a bidirectional link. But you should still be able to do it using a 3 way self join.