Hello!
I’m developing a web application for keeping track of dogs, their owners etc.
I have met a problem that I can’t seem to solve by myself. I have tried googleing and searching the forums.
Please, if you could guide me in the right direction to solve this, I would be very happy!
My problem is the following:
I have one table for owners and one for the dogs (hounds). I also have a cross table to connect owners with dogs. One dog can be owned by many persons.
When I search for dogs, owned by a certain person, I would like to receive these dogs with all owners. With my current CDbCriteria, I get the dogs I’m looking for, but also just the owners I’m searching for, not all owners.
Example:
Table: OWNER
name id
+---------------+--------------+
| Anna | 1 |
+---------------+--------------+
| John | 2 |
+---------------+--------------+
Table: X
owner_id hound_id
+---------------+--------------+
| 1 | 1 |
+---------------+--------------+
| 1 | 2 |
+---------------+--------------+
| 2 | 2 |
+---------------+--------------+
| 2 | 3 |
+---------------+--------------+
Table: HOUND
name id
+---------------+--------------+
| Buddy | 1 |
+---------------+--------------+
| Holly | 2 |
+---------------+--------------+
| Peggy | 3 |
+---------------+--------------+
| Sue | 4 |
+---------------+--------------+
Task:
Select all hounds that John owns, and display all owners.
Desired result:
hound.name owner.name
+---------------+--------------+
| Holly | John |
+---------------+--------------+
| Holly | Anna |
+---------------+--------------+
| Peggy | John |
+---------------+--------------+
Right now, I'm thinking the SQL should be something along these lines, but clearly, it isn't.
SELECT * FROM hound h
LEFT OUTER JOIN x x ON ( h.id = x.hound_id )
LEFT OUTER JOIN owner o ON ( x.owner_id = o.id)
WHERE o.name = John;
I also would like to translate the correct SQL to settings for CDbCriteria, which I'm using in the model's search().
This is so that I in the view can do something like:
<?php
foreach( $data->owners as $o )
{ echo CHtml::encode($o->name.', '); }
?>
Any help with this would be greatly accepted!
Regards