posted 11 years ago

Consider the following setup (and let's not discuss wether it makes sense as it's only an example). I have a class person, in the db I have a field husband_person_id. If it is set, it means the person is the wife of some other person which is her husband:

class Person {
  function husband() {
    return $this->belongsTo('Person', 'husband_person_id'); 
  }
  function wife() {
    return $this->hasOne('Person', 'husband_person_id');
  }
}

Now I want to get a list with all people that are not husbands i.e. have no wife:

$non_husbands = Person::has('wife', '=', 0);

However, this doesn't work. The has() function inside the query builder creates an sql query as follows and join it to the select query of persons:

select count(*) from `persons` where `persons`.`husband_person_id` = `persons`.`id`;

I would expect something like this:

select count(*) from `persons` p2 where `p2`.`husband_person_id` = `persons`.`id`;

I see this as a bug in laravel. Am I wrong? Is there a way to achieve what I want?

Thanks a lot!

Cheers, Michael

Last updated 2 years ago.

mruoss

replied 10 years ago

Ideas, anybody?

Last updated 2 years ago.

zenry

replied 10 years ago

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.

mruoss

replied 10 years ago

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.

jarektkaczyk

replied 10 years ago

Solution

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.

mruoss

replied 10 years ago

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.

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

Moderators