0

I have an application using Codeigniter/Datamapper. There is a table for Doctors, and a table for Specialties and the models are set up to have a many-to-many relationship.

I noticed that trying to query the doctors by specialty is resulting in it just querying for all of the doctors. Has anyone had this issue before? Here is the code I'm using:

$s = new Specialty();
$s->where('id',$id)->get(); //thought maybe get_by_id($id) was causing the issue, it wasnt...
$this->out['query'] = $s->doctor->order_by('last_name','asc')->get_sql();
$docs = $s->doctor->order_by('id')->get_iterated();

The $this->out['query'] responds with the following sql query:

"SELECT * FROM (`doctors`) ORDER BY `doctors`.`last_name` asc"

Another weird thing is that the results aren't coming back ordered by last_name, but I'm assuming its something in how the array gets passed to the json_encode function.

Xylude Xaalud
  • 388
  • 1
  • 4
  • 11
  • Weirdly enough, it seems to be an issue with get_iterated(). Which sucks, because some of those record sets have more than 1k entries. – Xylude Xaalud Dec 14 '12 at 18:58
  • Can you try $s->doctor->where('id', $id); instead of $s->where('id,$id)->get(); ? – Karan Ashar Dec 14 '12 at 19:17
  • $s->doctor->where('id',$id) would look up a single doctor by the specialty id, whereas I need to look up a set of doctors based on the specialty id. My assumption at this point is that the get_iterated() method doesn't work properly with many to many relationship calls. – Xylude Xaalud Dec 14 '12 at 20:26

1 Answers1

0

You need to add some parameters to the get_sql method because you are using this on a related object.

From the docs:

$object->get_sql()

This method returns the SQL for the currently built query, as if get() was called. It clears the current query immediately.

Arguments

$limit: (Optional) Sets the limit on the query.
$offset: (Optional) Sets the offset on the query.
$handle_related: (Optional) If TRUE, and this object is referenced from a parent object, the parent object will automatically to the where statement.


$u = new User();
$u->where('name', 'Bob');
echo $u->get_sql();
// outputs the raw SQL
Example with relationship
$group = new Group(1); // load Group #1

echo $group->user->get_sql();
// SELECT `users`.*
// FROM `users`

echo $group->user->get_sql(NULL, NULL, TRUE);
// SELECT `users`.*
// FROM `users`
// LEFT OUTER JOIN `groups` groups ON `users`.`group_id` = `groups.id`
// WHERE `groups`.`id` = 1

So you need to do this to output the correct query:

$this->out['query'] = $s->doctor->order_by('last_name','asc')->get_sql(NULL, NULL, TRUE);
swatkins
  • 13,530
  • 4
  • 46
  • 78