There are two tables I'm working with: one called "meeting" and the other called "attendance". The "attendance" table is a many-to-many relational database structured as follows:
Attendance:
user_id | meeting_id | invited
--------+------------+--------
1 | 5 | 1
2 | 5 | 0
3 | 4 | 0
3 | 5 | 1
3 | 6 | 0
The "meeting" table has the following format:
Meetings:
meeting_id | meeting_name | owner_id
-----------+--------------+----------
3 | Awesome | 2
4 | Boring | 2
5 | Cool | 5
9 | Sexy | 3
In this setup, each meeting can only have one row in the table, but there can be multiple attendance rows corresponding to every possible user for each meeting.
My goal is to list all meetings where a provided userid matches either the owner_id in the "meetings" table OR where the userid is listed as attending in the "attendance" database.
For instance, when searching for userid 3, the expected result should be as follows:
Result for userid 3:
meeting_id | meeting_name | owner_id
-----------+--------------+----------
5 | Cool | 5 - Userid 3 is attending meeting 5
9 | Sexy | 3 - Userid 3 owns meeting 9
I've tried implementing the above logic using SQL and Propel, but my current approach results in duplicate rows per meeting due to the multiple occurrences of the meeting in the attendance database.
$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;
This translates into the following SQL query:
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)
Thank you for your assistance,