For every term to you want to match, you left join and build the query the same way. It's pretty easy to automate actually. Building sql in php is easy and fun, Well, at least easy really. But first, how does it work? OK, remember this bit of code I posted before?:
select
o1.id,
o2.id,
case when o1.dat='old' and o2.dat='fish' then '_'||o1.dat||'_' else o1.dat end,
case when o1.dat='old' and o2.dat='fish' then '_'||o2.dat||'_' else o2.dat end
from oldfish o1
join oldfish o2
on (o1.id=o2.id) ;
What that does is it makes two copies of the table oldfish. So, from the db's perspective, it now has two sets of data, identical to each other, multiplies them, and produces the output in the previous post (the one with underlined terms)
Imagine the two sets laid out with one set vertical on the left (the y axis) and one along the bottom, going to the right, (the x axis).:
1 | new
2 | old
3 | new
3 | old
3 | fish
4 | old
4 | new
4 | fish
5 | old
5 | fish
----------- 1 | new, 2 | old, 3 | new, 3 | old, 3 | fish, 4 | old, 4 | new, 4 | fish, 5 | old, 5 | fish
The on clause in our query tells us which rows across match up with with colums coming up from the bottom. If we say where y.id=x.id Then we connect them like so:
1 | new +
2 | old +
3 | new + + +
3 | old + + +
3 | fish + + +
4 | old + + +
4 | new + + +
4 | fish + + +
5 | old + +
5 | fish + +
----------- 1 | new, 2 | old, 3 | new, 3 | old, 3 | fish, 4 | old, 4 | new, 4 | fish, 5 | old, 5 | fish
Those rows look like this:
id | dat | id | dat
----+------+----+------
1 | new | 1 | new
2 | old | 2 | old
3 | old | 3 | old
3 | old | 3 | new
3 | old | 3 | fish
3 | new | 3 | old
3 | new | 3 | new
3 | new | 3 | fish
3 | fish | 3 | old
3 | fish | 3 | new
3 | fish | 3 | fish
4 | old | 4 | old
4 | old | 4 | new
4 | old | 4 | fish
4 | new | 4 | old
4 | new | 4 | new
4 | new | 4 | fish
4 | fish | 4 | old
4 | fish | 4 | new
4 | fish | 4 | fish
5 | old | 5 | old
5 | old | 5 | fish
5 | fish | 5 | old
5 | fish | 5 | fish
Note that 3 has both old fish and fish old. We don't need to match on both, so we constrain the selection in the where clause to just old fish, and in case there are still > 1 matches, we use distinct to eliminate dupes.
select distinct o1.id from oldfish o1 join oldfish o2 on (o1.id=o2.id) where o1.dat='old' and o2.dat='fish';
id
----
3
4
5
So that's the query for two terms. for old, fish, heads it would would look like this:
select distinct o1.id
from oldfish o1
join oldfish o2 on (o1.id=o2.id)
join oldfish o3 on (o1.id=o3.id)
where
o1.dat='old'
and o2.dat='fish'
and o3.dat='heads';
So, programmatically, we're adding a join on and an and clause to the where clause, right?
For homework, write sql for a fourth term, and implement in php how to make an n-term query in a function.
