Query where has related record that the second item matches

Posted 2 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.

Laracasts Mascot

Hi, Have We Met Yet?

Did you know that, in addition to the forum, Laracasts includes well over 1000 lessons on modern web development? All for the price of one lunch out per month.

Sign Me Up

Channels

Reply to

Use Markdown with GitHub-flavored code blocks.