where_join_field not adding anything to query - El Forum - 08-08-2011
[eluser]swatkins[/eluser]
I can't seem to get the where_join_field method to alter the query at all. The query just goes about its business as if the 'where_join_field' method wasn't even there. I can get join fields to save, so I'm assuming my relationships are correct. I just can't seem to query against them. Please help!
I have the following query:
Code: function get_inbox($id, $offset = 0, $limit = 10)
{
$recipient = new User($id);
$messages = new Message();
$messages->where_related_recipient('id', $id);
$messages->where('active', 1);
$messages->where('deleted', 0);
$messages->where('status', 'sent');
$messages->recipient->where_join_field('message', 'recipientdeleted', 0);
$messages->order_by('created', 'desc');
$messages->get($limit, $offset);
$messages->check_last_query();
return $messages->all;
}
My join table looks like this:
Code: 'id' => array('type'=>'INT', 'constraint'=>5, 'unsigned'=>TRUE, 'auto_increment'=>TRUE),
'message_id' => array('type'=>'INT', 'constraint' => 5, 'unsigned' => TRUE),
'recipient_id' => array('type'=>'INT', 'constraint'=>5, 'unsigned'=>TRUE),
'sender_id' => array('type'=>'INT', 'constraint'=>5, 'unsigned'=>TRUE),
'isread' => array('type'=>'INT', 'constraint'=>1, 'unsigned'=>TRUE, 'default'=>0),
'recipientdeleted' => array('type'=>'INT', 'constraint'=>1, 'unsigned'=>TRUE, 'default'=>0),
'senderdeleted' => array('type'=>'INT', 'constraint'=>1, 'unsigned'=>TRUE, 'default'=>0),
'datesent' => array('type'=>'DATETIME')
My Models:
Code: class Message extends DataMapper {
var $has_one = array(
'sender' => array(
'class' => 'user',
'other_field' => 'message'
)
);
var $has_many = array(
'recipient' => array(
'class' => 'user',
'other_field' => 'message'
)
);
...
}
class User extends DataMapper {
var $has_many = array(
'message' => array(
'class' => 'message',
'other_field' => 'recipient'
),
'message' => array(
'class' => 'message',
'other_field' => 'sender'
),
...
}
EDIT:
I've also tried this query (tried to match the documentation) and it seems to be doing what it should, yet it isn't creating the table name correctly:
Code: function get_inbox($id, $offset = 0, $limit = 10)
{
$recipient = new User($id);
$messages = new Message();
$messages->where_related_recipient('id', $id);
$messages->where('active', 1);
$messages->where('deleted', 0);
$messages->where('status', 'sent');
$messages->where_join_field('recipient', 'recipientdeleted', FALSE);
$messages->order_by('created', 'desc');
$messages->get($limit, $offset);
$messages->check_last_query();
return $messages->all;
}
as it gives me the following error:
Code: A Database Error Occurred
Error Number: 1054
Unknown column 'messages_users.recipientdeleted' in 'where clause'
SELECT `messages`.* FROM (`messages`) LEFT OUTER JOIN `messages_users`
recipient_messages_users ON `messages`.`id` = `recipient_messages_users`.`message_id`
WHERE `recipient_messages_users`.`recipient_id` = 13724 AND `messages`.`active` = 1
AND `messages`.`deleted` = 0 AND `messages`.`status` = 'sent' AND
`messages_users`.`recipientdeleted` = 0 ORDER BY `messages`.`created` desc LIMIT 10
If I manually add 'recipient_' to the 'messages_users.recipientdeleted' part in a manual query, it works. Could this be a bug? or am I just missing some setup somewhere?
|