alexleonard
3 years ago

Query where has related record that the second item matches

Posted 3 years ago by alexleonard

I'm trying to work out if there's an eloquent way to run the following query.

I have a deals table and a deal_activities table. Deals are assigned to a user. Each time a deal is assigned, or reassigned to a user, a record is made in the deal_activities table recording the new user assignment.

This way I can show the assignee history by doing

$deal->activities()->whereKey('assigned')->orderBy('created_at', 'desc')->get()

However I also need to be able to query deals where the penultimate user was a specific user.

In psuedo code: Get deals where has activities where the second most recent record = $userId

However using things like skip() and take() in the relationship query don't seem to be the way to go about it.

eg

        $deals = $deal->whereHas(
            'activities',
            function ($q) use ($lastAssignedId) {
                $q->skip(1)->whereAssigneeId($lastAssignedId)->take(1);
            }
        );

As soon as I wrote that, I realised that wasn't the way to go about it at all. Plus we can't match assignees earlier than the penultimate either:

  1. Deal 1 was assigned to User X, then User Y then User Z.
  2. A search for deals last assigned to User X would not return Deal 1, but a search for deals last assigned to User Y would return Deal 1

Any ideas?

Please sign in or create an account to participate in this conversation.