DataMapper ORM - selecting where there is no relationship

[eluser]NeilyM[/eluser]
Hi Guys

I have been staring at this for a few hours today and I'm hoping that I'm simply not seeing the wood for the trees now.

I have contacts and emails. A contact "has_many" emails. An email "has_many" contacts.

I can easily select contacts that have been sent a particular email (eg the one with id 5) like this:

$contact = new Contact;
$contact->where_related("email", "id", 5);
$contact->get();

But what I actually need is a way to select contacts who have not received the email with id 5.

I guess it would be something like:

$contact = new Contact;
$contact->where_not_related("email", "id", 5);
$contact->get();

...but of course there is no "where_not_related" method.

This sounds so simple but I'm really tearing my hair out.

Can anyone help me out?

Cheers,

Neil

[eluser]WanWizard[/eluser]
You can simply include the operator in the field definition:

Code:

$contact = new Contact;
$contact->where_related(“email”, “id !=”, 5);
$contact->get();

[eluser]NeilyM[/eluser]
Thanks WanWizard.

I tried that but unfortunately it doesn't return the contacts who have not received email with id 5

What it does do is return contacts who have received emails with other ids.

However, I need to be able to select all contacts who have not received email with id=5 so that I can send that email to them.

Again, thanks.

Neil

[eluser]WanWizard[/eluser]
True. Let's try something else then

Code:

// create the objects
$contact = new Contact();
$email = $contact->email;

// compose the subquery
$email->select_func('COUNT', '*', 'count');
$email->where('id', 5);
$email->where_related('contact', 'id', '${parent}.id');

// run the query
$contact->select()->select_subquery($email, 'email_count')->get_iterated();

// loop over the result
foreach($contact as $addr)
{
    if ($addr->email_count == 0)
    {
        // contact didn't receive it, send the email out
    }
}

You can probably create something similar using where_not_in() combined with a subquery.

[eluser]NeilyM[/eluser]
Thanks again WanWizrd.

I also thought of something else based on the success of that was posted here:

http://ellislab.com/forums/viewthread/170368/

...and I'm wondering if this will work:

$contact = new Contact;
$contact->where_related(“email”, “id !=”, 5);
$contact->or_where_related(“email”, “id”, NULL);
$contact->get();

I'll try these when I get into work tomorrow.

Thanks again.

Cheers,

Neil

[eluser]WanWizard[/eluser]
That's not going to work, but this should:

Code:

// create the objects
$contact = new Contact();
$email = $contact->email;

// compose the subquery (get all contacts who have received email 5)
$email->select('contact_id');
$email->where('email_id', 5);
$email->where_related('contact', 'id', '${parent}.id');

// run the query
$contact->where_not_in_subquery('id', $email)->get_iterated();

// loop over the result
foreach($contact as $addr)
{
    // contact didn't receive it, send the email out
}

This generates

Code:

SELECT *
    FROM (`contacts`)
    WHERE contacts.id NOT IN (
        SELECT `contact_id`
            FROM (`emails`)
            LEFT OUTER JOIN `contacts_emails` contacts_emails ON `emails`.`id` = `contacts_emails`.`email_id`
            WHERE `email_id` = 5
            AND contacts_emails.contact_id = contacts.id
    )

[eluser]NeilyM[/eluser]
Thanks WanWizard.

I'll get on the case tomorrow and post back.

Thank you for your time.

[eluser]NeilyM[/eluser]
WanWizard, your solution works a treat.

Thank you :-)

Cheers,

Neil