I have two tables one called meeting and one called attendance, attendance is a many to many relational database in the following format:
Attendance:
user_id | meeting_id | invited
--------+------------+--------
1 | 5 | 1
2 | 5 | 0
3 | 4 | 0
3 | 5 | 1
3 | 6 | 0
Meetings are in the following format:
Meetings:
meeting_id | meeting_name | owner_id
-----------+--------------+----------
3 | Awesome | 2
4 | Boring | 2
5 | Cool | 5
9 | Sexy | 3
There can only be one meeting row per meeting, but unlimited attendance rows per meeting (limited to for every possible user for every meeting).
How in SQL and/or Propel do I create something that would list all meetings where the (provided) user_id is either the owner_id in meetings OR were the user_id and invited in the attendance database.
I am looking for a result (based on the above data) when searching for userid 3 of:
Result for userid3:
meeting_id | meeting_name | owner_id
-----------+--------------+----------
5 | Cool | 5 - Because userid 3 is attending meeting 5
9 | Sexy | 3 - Because userid 3 owns meeting 9
I currently have the following which doesn't work really, and produces multiple rows per meeting (because the meeting exists more than once in the attendance DB).
$criteria->addJoin(MeetingMeetingsPeer::ID, MeetingAttendancePeer::MEETING_ID, Criteria::LEFT_JOIN);
$criterion = $criteria->getNewCriterion(MeetingMeetingsPeer::OWNER_ID, Meeting::getUserId());
$criterion->addOr($criteria->getNewCriterion(MeetingAttendancePeer::USER_ID, Meeting::getUserId()));
$criteria->add($criterion);
return $criteria;
Which is something like the below in SQL:
SELECT meeting_meetings.ID, meeting_meetings.OWNER_ID, meeting_meetings.GROUP_ID, meeting_meetings.NAME, meeting_meetings.COMPLETED, meeting_meetings.LOCATION, meeting_meetings.START, meeting_meetings.LENGTH, meeting_meetings.CREATED_AT, meeting_meetings.UPDATED_AT FROM `meeting_meetings` LEFT JOIN meeting_attendance ON (meeting_meetings.ID=meeting_attendance.MEETING_ID) WHERE (meeting_meetings.OWNER_ID=1 OR meeting_attendance.USER_ID=1)
Thanks for your time,