List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 17 1999 3:05pm
Subject:Re: complicated query 2
View as plain text  
On Tue, 1999-08-17 09:53:25 +0200, Randy Gerritse wrote:
> forgot to mention that AFTER the described procerure i pull this
> trick once more... for this query only returns the people that match
> your query. after this a similar query will be used to return the
> people that match your query and are actually looking for you as
> well... =)

To find a suitable partner for memberid 4711, I suggested this query:
   SELECT h.memberid
   FROM   datinghavetags AS h
        , datingwanttags AS w
   WHERE  h.tagid    = w.tagid
     AND  h.tagvalue = w.tagvalue
     AND  w.memberid=4711
   GROUP BY h.memberid
   HAVING COUNT(*)=$num;

Now, to discover for which of these members the member 4711 is a
suitable partner, too, we do nearly the same query:

   SELECT w.memberid
   FROM   datingwanttags AS w
        , datinghavetags AS h
   WHERE  w.memberid IN (list of stored ids)
     AND  h.memberid = 4711
     AND  w.tagid    = h.tagid
     AND  w.tagvalue = h.tagvalue
   GROUP BY w.memberid
   HAVING COUNT(*)=$num;  <-- problem

In the having clause we'd need the number of wanted tags for each of
the w.memberids.  That's not easily done in a single query, so my
suggestion is to introduce a new table:
  CREATE TABLE numwanted ( memberid INT PRIMARY KEY, numwantedtags INT );
Now fill it with data:
  INSERT INTO numwanted
    SELECT memberid, COUNT(*) FROM datingwanttags GROUP BY memberid;

Using this new table, the query above becomes:

   SELECT w.memberid      AS wid
        , h.memberid      AS hid
        , n.numwantedtags AS ntags
   FROM   datingwanttags AS w
        , datinghavetags AS h
        , numwanted      AS n
   WHERE  w.memberid IN (list-of-stored-ids)
     AND  h.memberid = 4711
     AND  w.tagid    = h.tagid
     AND  w.tagvalue = h.tagvalue
     AND  w.memberid = n.memberid
   GROUP BY w.memberid, h.memberid
   HAVING COUNT(*)=ntags;

In standard SQL one would have to add n.numwantedtags to the GROUP BY
clause, but in MySQL in this case it's faster (and still giving
correct results) this way.


As a side note, now it's even quite easy to find every suitable
b-fits-a pairs in a single step:

   SELECT w.memberid AS wid
        , h.memberid AS hid
        , n.numwantedtags AS ntags
   FROM   datingwanttags AS w
        , datinghavetags AS h
        , numwanted      AS n
   WHERE  w.tagid    = h.tagid
     AND  w.tagvalue = h.tagvalue
     AND  w.memberid = n.memberid
   GROUP BY wid, hid
   HAVING COUNT(*)=ntags;


Restricting the search to one memberid (on ony side, have or want):

   SELECT w.memberid AS wid
        , h.memberid AS hid
        , n.numwantedtags AS ntags
   FROM   datingwanttags AS w
        , datinghavetags AS h
        , numwanted      AS n
   WHERE  w.tagid    = h.tagid
     AND  w.tagvalue = h.tagvalue
     AND  w.memberid = n.memberid
     AND  (w.memberid=4711 OR h.memberid=4711)
   GROUP BY wid, hid
   HAVING COUNT(*)=ntags;

(Unfortunately, in current MySQL versions using OR disables the use of
 indexes for the query, so it might be quite slow. :( )
Using the last query, you just have to look for pairs of memberids,
that exist in both directions, and you're done.

I'd recommend doing this last step on the application side ...

Note: You could use the ntags value for assing the quality of a match.
(Assuming that couples with lots of matching tags do fit better ...?)

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
complicated query 2Randy Gerritse17 Aug
  • Re: complicated query 2Martin Ramsch17 Aug