Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

Ideas, anybody?

Last updated 2 years ago.
0

I think you need a whereHas()

$non_husbands = Person::whereHas('wife', function($query)
{
    $query->where('husband_person_id', '=', 0);

})->get();
Last updated 2 years ago.
0

Thanks for your help, zenry.

Your code would return "people that have wives with no husbands" which is a paradoxon and returns an empty set in any case.

I think I'm using has() correctly but the implementation of has() doesn't work correctly if the related table is the same table (see the sqls above).

So this is still unsolved...

Last updated 2 years ago.
0

mruoss you example is a bit inconsistent, but you can achieve what you want.

Imagine this setup which is clearer:

table people: id, spouse_id, ...

class Person extends \Eloquent {
    public function spouse()
    {
        return $this->belongsTo('Person', 'spouse_id');
    }
}

Now, this is somewhat tricky

Person::has('spouse', '<', 1, 'and', function ($q) {
    $q->from( DB::raw('people spouses') )
        ->orWhereRaw( 'spouses.id = people.spouse_id' );
    })->get();

but it will return all the people who don't have a spouse, which I suppose is what you want.

And yes, I agree that the table should be aliased by default, so you would not need to use above tweak. However I didn't look at the code to tell what changes it would need.


edit: for two way querying, like you wanted, it will do:

for clarity instead of husband and wife let's go with parent and children (one-to-many, but one-to-one is just the same, and in some cultures you could have more wives at the same time ;) )

table people: id, parent_id, ...

class Person extends \Eloquent {
    public function parent()
    {
        return $this->belongsTo('Person', 'parent_id');
    }

    public function children()
    {
        return $this->hasMany('Person', 'parent_id');
    }

}

Query:

// orphans:
Person::has('parent', '<', 1, 'and', function ($q) {
    $q->from( DB::raw('people parents') )
        ->orWhereRaw( 'parents.id = people.parent_id' );
    })->get();

// poor old people with no children:
Person::has('children', '<', 1, 'and', function ($q) {
    $q->from( DB::raw('people children') )
        ->orWhereRaw( 'people.id = children.parent_id' );
    })->get();
Last updated 2 years ago.
0

Thank you very much, jarektkaczyk, for your workaround. I had to print the resulting query to understand what happens but it makes perfect sense and works.

I also had a brief look at the implementation of has() and it seems they're not set up for table aliases at all... far away from a "few-lines-pull-request". So I will happily use the implementation you provided.

For my initial example, the solution would be:

Person::has('wife', '<', 1, 'and', function ($q) {
    $q->from( DB::raw('people wifes') )
        ->orWhereRaw( 'people.id = wifes.husband_id' );
    })->get();

where the count query would look as follows:

select count(*) from `people` `wifes` where `people`.`husband_id` = `people`.`id` or `people`.`id` = `wifes`.`husband_id`; 

Many thanks again!

Last updated 2 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

mruoss mruoss Joined 8 Apr 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.