Unique key combination

donboe · November 4, 2018, 2:15pm

I’m working on a community portal. The portal has members (tbl members) and members can has friends (tbl member_friends), as on most social networks. In order to avoid double entries in the member_friends table I altered this table the following way:

ALTER TABLE `member_friends` ADD UNIQUE KEY `friend_combination` (`member_one_id`, `member_two_id`);

I thought by doing so I would avoid double entries for similar id combinations

But when I add a friendship combination:

INSERT INTO `member_friends` (`member_one_id`, `member_two_id`) VALUES
(1, 2);

and after that doing the same but the other way arround:

INSERT INTO `member_friends` (`member_one_id`, `member_two_id`) VALUES
(2, 1);

The row is still added. What should I change or update to avoid double entries for two id’s no matter in which order.

Thank you in advance

rpkamp · November 4, 2018, 3:59pm

I’m not convinced there is an issue here. The fact that I consider someone to be my friend does not mean they consider me to be their friend as well.

If that is the case in your application though then:

You should not call it a friendship, because it and doesn’t behave like one, and
Whenever a user A friends a user B, add both tuples (A, B) and (B, A) in the database. That way it can not be inserted again and it makes your queries a) a lot easier to understand and b) more efficient

donboe · November 4, 2018, 4:18pm

@rpkamp. It works with friend requests, so it needs to be accepted first!

That is what I tried to avoid. I had/have it like that. The double insert.

r937 · November 4, 2018, 4:24pm

that sounds very much like a directional relationship

if some idiot wingnut with outrageous views sent me a friend request, i would not want the database to show us as friends

i don’t really know what your “friendship” relationship means, but i have a suspicion that you to not want to automatically store both (1,2) and (2,1)

donboe · November 4, 2018, 4:37pm

@r937 In this case It’s not really about friendship, but more about having similar interests. And indeed I don’t want to store both (1,2) and (2,1). So what are my options in this Rudi?

Edit. I know I can avoid multiple id couples with scripting but I’m just wondering if this could be done in the database allready

rpkamp · November 4, 2018, 4:47pm

So basically your work flow is:

User A invites B to be their friend, this adds a “friendship request” to the database, but not a friendship

2a. User B accepts the friendship request
3a. The friendship is added to the database

2b. User B rejects the friendship request
3b. The friendship request is deleted

Correct?

benanamen · November 4, 2018, 4:52pm

What this really needs is using a Graph Database. Some info…

https://en.wikipedia.org/wiki/Graph_theory

https://en.wikipedia.org/wiki/Graph_database

https://neo4j.com/

Example
https://neo4j.com/developer/

<?php
/**
 * To install Neo4j-PHP-Client, we use Composer
 *
 * $ curl -sS https://getcomposer.org/installer | php
 * $ php composer.phar require graphaware/neo4j-php-client
 *
 */

require __DIR__.'/vendor/autoload.php';

use GraphAware\Neo4j\Client\ClientBuilder;

// change to your hostname, port, username, password
$neo4j_url = "bolt://neo4j:password@localhost";

// setup connection
$client = ClientBuilder::create()
    ->addConnection('default', $neo4j_url)
    ->build();

// setup data
$insert_query = <<<EOQ
UNWIND {pairs} as pair
MERGE (p1:Person {name:pair[0]})
MERGE (p2:Person {name:pair[1]})
MERGE (p1)-[:KNOWS]-(p2);
EOQ;

// friend data to insert
$data = [["Jim","Mike"],["Jim","Billy"],["Anna","Jim"],
          ["Anna","Mike"],["Sally","Anna"],["Joe","Sally"],
          ["Joe","Bob"],["Bob","Sally"]];

// insert data
$client->run($insert_query, ["pairs" => $data]);


// friend of friend: query
$foaf_query = <<<EOQ
MATCH (person:Person)-[:KNOWS]-(friend)-[:KNOWS]-(foaf)
WHERE person.name = {name}
  AND NOT (person)-[:KNOWS]-(foaf)
RETURN foaf.name AS name
EOQ;

// friend of friend: build and execute query
$params = ['name' => 'Joe'];
$result = $client->run($foaf_query, $params);

foreach ($result->records() as $record) {
  echo $record->get('name') . PHP_EOL;
}


// common friends: query
$common_friends_query = <<<EOQ
MATCH (user:Person)-[:KNOWS]-(friend)-[:KNOWS]-(foaf:Person)
WHERE user.name = {user} AND foaf.name = {foaf}
RETURN friend.name AS friend
EOQ;

// common friends: build and execute query
$params = ['user' => 'Joe', 'foaf' => 'Sally'];
$result = $client->run($common_friends_query, $params);

foreach ($result->records() as $record) {
  echo $record->get('friend') . PHP_EOL;
}


// connecting paths: query
$connecting_paths_query = <<<EOQ
MATCH path = shortestPath((p1:Person)-[:KNOWS*..6]-(p2:Person))
WHERE p1.name = {name1} AND p2.name = {name2}
RETURN [n IN nodes(path) | n.name] as names
EOQ;

// connecting paths: build and execute query
$params = ['name1' => 'Joe', 'name2' => 'Billy'];
$result = $client->run($connecting_paths_query, $params);

foreach ($result->records() as $record) {
  print_r($record->get('names'));
}

SamA74 · November 4, 2018, 5:08pm

It happened in your test, but would it ever happen out in the wild?

If I understand how your friendship system will work, I presume something like:-

Then in the instance that the request is accepted and the pair are added to the table; would you then not have some system whereby the friends would no longer see the option to add each other as a friend, as that relationship has already been established and therefore it is unlikely the inverse pair would ever be added?
And if by some chance/bug it did, what’s the worst that could happen?

Edit: Another thought for a simple solution.
When adding the pair, have a function to always sort the pair into lowest number first before insertion, that way duplicate pairs will always be “carbon copy” duplicates that the database can refuse.

donboe · November 4, 2018, 6:46pm

@rpkamp That is correct

donboe · November 4, 2018, 7:12pm

@SamA74. I have seen that suggestion somewhere else as as well and have been thinking on how I could accomplisch that, but havent come up with a sollution as yet. Do you have any suggestions?

rpkamp · November 4, 2018, 7:37pm

Assuming you use PDO:

$stmt = $pdo->prepare(
    'INSERT INTO member_friends (member1, member2) VALUES (?, ?)'
);
$stmt->execute(
    $member1id < $member2id ? $member1id : $member2id,
    $member1id < $member2id ? $member2id : $member1id
);

donboe · November 4, 2018, 7:44pm

@rpkamp Yes I use PDO. That sounds like a sollution. but should the second option not be:

$member1id > $member2id ? $member2id : $member1id

And secondly how do I deal with this within the Controller?

Thank you in advance

rpkamp · November 4, 2018, 8:24pm

No. Just try it with some example numbers.

You don’t. You deal with it in a repository, you call the repository from your controller.

SamA74 · November 4, 2018, 8:42pm

Another option would be sort()

$pair = [$memberA, $memberB];
sort($pair);
$stmt = $pdo->prepare(
    'INSERT INTO member_friends (member1, member2) VALUES (?, ?)'
);
$stmt->execute($pair);

r937 · November 4, 2018, 10:56pm

it sounds to me very much like the relationship is bidirectional, so i can see why you’d want to store only one of the two rows

your options on this are either a whole bunch of duck tape as the last few dozen replies have proffered, or the simplest solution – store both

i believe this was recommended earlier, yes? i just wasn’t sure about the directionality

storing both also allows indexes to be used in queries

nope, not without more duct tape

benanamen · November 5, 2018, 12:46am

Any reason you are using a tool that was not made for this instead of one that is specifically made for it? I guarantee you facebook and twitter are not using Mysql or any other RDMS to model their relationships. You can hammer a nail with the heel of your shoe but why not just use a hammer which was designed specifically to hammer nails. Things will start getting complicated when you start asking questions like who are my friends friends or who are my friends friends that are not my friends and so on.

r937 · November 5, 2018, 4:02am

but OP admitted this isn’t about friendships…

look at all the time we’re wasting on that red herring

benanamen · November 5, 2018, 4:49am

I missed that post. I was going off what OP said in the first post.

members can has friends (tbl member_friends), as on most social networks

Oh well, I ended up getting freshened up on Graph DB’s.

rpkamp · November 5, 2018, 7:06am

(emphasis mine)

This. Exactly this is why I recommend to store both.

Let’s work out an example here shall we?

First let’s assume we’re only storing one direction of the “friendship”.

Let’s start with a simple query and find all friends of a certain user A.

You’d think the query is

SELECT
    m.name
FROM
    members AS m
INNER JOIN
    member_friends AS f
    ON m.id = f.member_one_id
    AND f.member_two_id = ?

Right? Wrong!

The current member can either be in member_one_id or in member_two_id and you can’t know upfront, so you have to query both ways. So your query has to account for both and it would actually become:

SELECT
    m.name
FROM
    members AS m
INNER JOIN
    member_friends AS f
    ON m.id = f.member_one_id
    AND f.member_two_id = ?
UNION ALL
SELECT
    m.name
FROM
    members AS m
INNER JOIN
    member_friends AS f
    ON m.id = f.member_two_id
    AND f.member_one_id = ?

And that is just for a simple query. Just imagine what happens when things get more complex. Indeed, they will explode in your face, and you will start pulling your hair out in the horror of it all.

Yes, there are other ways of writing this using multiple joins, but those would be even more horrible.

Instead, if you store both (A, B) and (B, A) in the database the first query just works. Add to that an index on member_two_id and it will performant to boot.

Sure, it is a bit weird to store it that way, but for the sake of your database and that of your database, please go that route.

donboe · November 5, 2018, 7:50am

@r937 Not sure what you mean with this Rudi?