Problems with Contact Table

I have something similar to friends table. Its called contacts. It has:

• user_id is ID for user that's like owner of that contact,
• contact_id is ID for that user that's in friends with user_id;

There is the second table called events. It has:

• user_id is ID of user that's creator of that event.

I need to select events created by my friends. So if I have John and Anna in my conta开发者_如何学Cct list... I need to display events of them.

Here is solution:

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  JOIN `bio_contacts` ON (`bio_contacts`.`contact_id` = `bio_community_events`.`user_id`)
 WHERE `bio_contacts`.`user_id` = '33'

I.e., my ID is 33. It gives me events of my friends. And here comes the problem...

There are situations when I'm not the one who make contacts with my friend. Vice versa, Anna did it. This query would simply ignore that and don't display results for Anna.

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  JOIN `bio_contacts`
    ON (`bio_contacts`.`contact_id` = `bio_community_events`.`user_id`)
 WHERE `bio_contacts`.`user_id` = '33'

UNION ALL                                 --- or UNION if this gives you
                                          --- duplicate row
SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  JOIN `bio_contacts` 
    ON (`bio_contacts`.`user_id` = `bio_community_events`.`user_id`)
 WHERE `bio_contacts`.`contact_id` = '33'

or like this:

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  JOIN 
    ( SELECT `bio_contacts`.`contact_id` AS id
        FROM `bio_contacts`
       WHERE `bio_contacts`.`user_id` = '33'
    UNION ALL                               
      SELECT `bio_contacts`.`user_id` AS id 
        FROM `bio_contacts`                                  
       WHERE `bio_contacts`.`contact_id` = '33'
    ) AS un
    ON ( un.id = `bio_community_events`.`user_id`)

To to set limit to all returned rows in example #1 use:

( SELECT ... )
UNION ALL
( SELECT ... )

ORDER BY ?        --- optional
LIMIT x ;

Using ORDER BY there can be quite costly in such a query. You can also have this (different) query which can use indexes:

( SELECT ... 
  ORDER BY ?
  LIMIT a
)
UNION ALL
( SELECT ...
  ORDER BY ?
  LIMIT b
)

LIMIT x ;         --- with or without this LIMIT

Another way to solve the original problem is using EXISTS :

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  WHERE EXISTS  
      ( SELECT *
          FROM `bio_contacts`
         WHERE `bio_contacts`.`user_id` = '33'
           AND `bio_contacts`.`contact_id` = `bio_community_events`.`user_id`
      )
     OR EXISTS
      ( SELECT *
          FROM `bio_contacts`
         WHERE `bio_contacts`.`contact_id` = '33'
           AND `bio_contacts`.`user_id` = `bio_community_events`.`user_id`
      )

or:

SELECT `bio_community_events`.`id`, 
       `bio_community_events`.`begin_on`, 
       `bio_community_events`.`name`
  FROM `bio_community_events`
  WHERE EXISTS  
      ( SELECT *
          FROM `bio_contacts`
         WHERE ( `bio_contacts`.`user_id` = '33'
             AND `bio_contacts`.`contact_id` = `bio_community_events`.`user_id` )
           OR  ( `bio_contacts`.`contact_id` = '33'
             AND `bio_contacts`.`user_id` = `bio_community_events`.`user_id` )
      )

If your plan is to find the most efficient query, try all that work correctly (using IN, using UNION, using EXISTS) - adding the ORDER BY you want off course - and checking their speed and execution plans.

I would at least have:

• in table bio_community_events
  • an index on user_id
  • an index on the field(s) used for ORDER BY

and

• in table bio_contacts, two compound indexes
  • on (contact_id, user_id) and
  • on (user_id, contact_id)

And post another question if you can't make it run in less than X milliseconds (X decided by your boss :)

SELECT id, begin_on, name 
FROM bio_community_events 
WHERE user_id IN ( SELECT user_id FROM bio_contacts WHERE contact_id = '33')
OR user_id IN ( SELECT contact_id FROM bio_contacts WHERE user_id = '33')

You need to get the user ids of your friends as well:

SELECT `bio_community_events`.`id`, `bio_community_events`.`begin_on`, `bio_community_events`.`name`
FROM `bio_community_events`
JOIN `bio_contacts`
    ON (`bio_contacts`.`contact_id` = `bio_community_events`.`user_id`)
WHERE `bio_contacts`.`user_id` = '33'
OR `bio_contacts`.`user_id` IN (SELECT `contact_id` FROM `contacts` WHERE `user_id` = 33)