can couchdb do loops

Question

Can couchdb do loops?

Let's say I have a database of interests that have 3 fields subject1,subject2,subject3. example, cats,nutrition,hair or space,telescopes,optics etc.

A person (A) has 10 interests composed of 3 fields each.

10 more people B,C,D...have 10 interests each composed of 3 subjects each.

When person A logs in I want the system to search for all people with matching interests.

In javascript I would normally loop through all the interests and then find matching ones I guess using two loops. Then store the matches in another database for the user like "matchinginterests".

Is there any easy way to do this in couchdb compared to mysql -- which seems very complicated.

Thanks, Dan

Answer 1

When person A logs in I want the system to search for all people with matching interests.

SELECT i_them.* FROM interests AS i_me
INNER JOIN interests AS i_them ON (i_them.person != i_me.person) AND
 ((i_them.subject1 IN (i_me.subject1, i_me.subject2, i_me.subject3)) OR
  (i_them.subject2 IN (i_me.subject1, i_me.subject2, i_me.subject3)) OR 
  (i_them.subject3 IN (i_me.subject1, i_me.subject2, i_me.subject3)))
WHERE i_me.person = 'A' 

Is that what you wanted to do?

If you design your tables a little smarter though you'd do it like

SELECT DISTINCT them.* FROM person AS me
INNER JOIN interest AS i_me ON (i_me.person_id = me.id)
INNER JOIN interest AS i_them ON (i_them.subject = i_me.subject)
INNER JOIN person AS them ON (them.id = i_them.person.id AND them.id != me.id)
WHERE me.name = 'A'

Using the following tables

table interest
  id integer primary key autoincrement
  person_id integer //links to person table
  subject varchar //one subject per row.
  +-----+-----------+---------+
  | id  | person_id | subject |
  +-----+-----------+---------+
  | 1   | 3         | cat     |
  | 2   | 3         | stars   |
  | 3   | 3         | eminem  |
  | 4   | 1         | cat     |
  | 5   | 1         | dog     |
  | 6   | 2         | dog     |
  | 7   | 2         | cat     | 

table person
  id integer primary key autoincrement
  name varchar
  address varchar
  +-----+------+---------+
  | id  | name | address |
  +-----+------+---------+
  | 1   | A    | here    |
  | 2   | Bill | there   |
  | 3   | Bob  | everyw  |

result
+-----+------+---------+
| id  | name | address |
+-----+------+---------+
| 2   | Bill | there   |
| 3   | Bob  | everyw  |

This is how (what you call) 'looping' in SQL works...

First you take person with name 'A' from the table.

me.id me.name me.address
| 1   | A    | here    |

You look up all the interests

me.id me.name me.address i_me.subject
| 1   | A    | here      | cat
| 1   | A    | here      | dog

Then you match everyone elses interests

me.id me.name me.address i_me.subject i_them.subject i_them.person_id
| 1   | A    | here      | cat        | cat          | 3
| 1   | A    | here      | cat        | cat          | 2
| 1   | A    | here      | dog        | dog          | 2

And then you match the person to them's interest (except for me of course)

me.id me.name me.address i_me.subject i_them.subject i_them.person_id them.name
| 1   | A    | here      | cat        | cat          | 3              | Bob 
| 1   | A    | here      | cat        | cat          | 2              | Bill 
| 1   | A    | here      | dog        | dog          | 2              | Bill

Then you return only the data from them and 'throw' the rest away, and remove duplicate rows DISTINCT.

Hope this helps.

Answer 2

I think I understand what you are asking. The answer is pretty straightforward with Map/Reduce.

Say you have the following people documents:

{
   "name": "Person A",
   "interests" [ "computers", "fishing", "sports" ]
}
{
   "name": "Person B",
   "interests" [ "computers", "gaming" ]
}
{
   "name": "Person C",
   "interests" [ "hiking", "sports" ]
}
{
   "name": "Person D",
   "interests" [ "gaming" ]
}

You would probably want to emit your key as the interest, with the value as the person's name (or _id).

function (doc) {
   for (var x = 0, len = doc.interests.length; x < len; x++) {
      emit(doc.interests[x], doc..name);
   }
}

Your view results would look like this:

• computers => Person A
• computers => Person B
• fishing => Person A
• gaming => Person B
• gaming => Person D
• hiking => Person C
• sports => Person A
• sports => Person C

To get a list of people with computers as an interest, you can simply send key="computers" as part of the query string.

If you want to add a reduce function to your map, you can simply use _count (shortcut to use a compiled reduce function) and you can retrieve a count of all the people with a particular interest, you can even use that to limit which interests you query to build your relationships.