CODEWITHSUNDEEP
phplaravellaravel-4eloquent
Øystein Amundsen
Øystein Amundsen

Reputation: 4203

Finding identical rows in Eloquent model

I'm trying to find rows with identical data. I can't quite wrap my head around how the sql would work, but I know what the result should look like:

USER__IP (as uip1) [
    id,
    ip,
    user_id,
    USER__IP (as uip2) [
        id,
        ip,
        user_id (where uip2.user_id != uip1.user_id)
    ]
]

To explain; I have a model (USER__IP) which I've simplified above. Many users may share the same ip, and I want to know who. I made some comments in parantheses above which illustrates where I wish to go with this. I hope it is comprehensable.

I guess this needs to be written as two queries, first one that selects all USER__IP, and another which selects it's related entities. I tried with something like this:

class User extends Eloquent {
    public function userIp() {
        return $this->hasMany('UserIp');
    }
}

class UserIp extends Eloquent {
    public function identical() {
        return $this->hasMany('UserIp', 'ip', 'ip')->whereNotIn('user_id', array($this->user_id));
    }
}

// Run from the controller, and return the result.
$user = User::with(array('user_ips' => function ($query) {
    $query->with('identical');
}))->findOrFail($userId);

This does not provide the desired results. Somehow $this->user_id is bound to the query as null, and I don't get why.

The question is; given this model, how can I produce the desired result using either Eloquent or Query builder directly? Thank you.

Edit

I realize that I simplified the procedure too much. I therefore added the real controller code in the code above.

Edit 2

So the best solution to this problem was to do what @c-griffin proposed. Since his solution was in my opinon not quite complete, rather than answering my own question with the working code, I will just place an edit here and mark his answer as the correct one:

class UserIp extends Eloquent {
    public function identical() {
        return $this->hasMany('UserIp', 'ip', 'ip');
    }
}

// Controller code
$user = User::findOrFail($userId);
$user->load(array('user_ips' => function ($query) use ($userId) {
        $query->with(array('identical' => function ($q) use ($userId) {
                $q->whereNotIn('user_id', array($userId));
            }));
    }));

Thank you guys for your help in this.

Upvotes: 0

Views: 64

Answers (2)

Trung
Trung

Reputation: 650

$userIps = $userip = UserIp::get();
foreach ($userIps as $userIp)
{
    $userIp->load('identical');
}

Upvotes: 0

c-griffin
c-griffin

Reputation: 3026

I might be completely missing the boat on what you're trying to do, but this could work.
Leave your relationship as bare-bones as possible, then specify your wheres in with()s closure.

class UserIp extends Eloquent {
    public function identical() {
        return $this->hasMany('UserIp', 'ip', 'ip');
    }
}

// in a controller method
UserIp::with(['identical' => function($query){
    $query->whereNotIn('user_id', array($this->user_id));
}])->get();

Upvotes: 1

Related Questions