whereHas constraints not being followed

How can I reference the data in the messages_threads (Thread model) table while inside the whereHas clause?

Let me clarify. You have the following defined in your Thread class correct?

public function participants()
{
    return $this->asMany('Participant');
}

Then in your Participant model you have this defined

public function thread()
{
    return $this->belongsTo('Thread')
}

So the above sets up your PHP relationship. But it does not set up your SQL relationship. The database needs to have the relationship setup. In your message_threads table you need to set creator to be foreign index that references id on message_participants

However, I see one problem...the hasMany function is expecting the column to be message_participants_id and you are calling it creator. So in order to make the relationship work properly you would need to tell Eloquent the name of the column you are trying to relate them on.

public function threads()
{
    return $this->asMany('Participant', 'message_threads', 'creator');
}

Your relationships should be setup properly at this point.

UPDATE I made a few changes from some typos I just noticed.

that creator column is for something else, pretend it doesn't exist. My issue is, how do I reference the Thread model data while inside the whereHas clause?

The only way I can see this happening is using some joins with the query builder. Can't see how I can do it eloquently.

Ah, I see. Sorry I misread what you were asking. Maybe something like this? I have no idea if this will work, but you can either use $this (which may not work inside the closure) or you can pass a use statement and pass a variable in containing it or perhaps use a parent? A little above my head right now.

$threads = Thread::whereHas('participants', function($q) 
{
      $q->where('messages_participants.user_id', 3)
        ->where('messages_participants.last_read', '<', parent::updated_at);
})->get();

UPDATE After posting this I am sure the parent isn't going to work. I'll bow out and let someone more experienced take a stab.

this is crazy. I wrote a raw query

        $threads = \DB::
                    select(\DB::raw(
                    'select t.id as id, t.updated_at as updated_at, p.last_read as last_read
                     from messages_threads t
                     left join messages_participants p
                     on t.id = p.thread_id
                     where p.user_id = ' . auth()->user()->getAuthIdentifier()
                    ));


        return $threads;

$threads returns

    object(stdClass)[398]
      public 'id' => int 2
      public 'updated_at' => string '2015-06-05 00:06:38' (length=19)
      public 'last_read' => string '2015-06-05 00:06:48' (length=19)

I just cannot compare updated_at and last_read while inside the query. :\

ok, this works

        $query = "select GROUP_CONCAT(t.id) as id " .
                 "from messages_threads t, messages_participants p " .
                 "where t.id = p.thread_id " .
                 "and t.updated_at > p.last_read " .
                 "and p.user_id = :userid";

        $threads = \DB::select(\DB::raw($query), ['userid' => auth()->user()->getAuthIdentifier()]);
        
        return $threads;

I guess I can just grab the ids returned from this and perform an eloquent find.

Any idea if this can be converted to eloquent?

also, it is returning an array mixed with an object. Is this normal?

array (size=1)
  0 => 
    object(stdClass)[398]
      public 'id' => string '1,2,3' (length=5)

I have to use $threads[0]->id to get the data. kinda weird.

\DB::select() returns an array of results. If there is only 1 result, it will return an array with 1 item in it. So yes, it's normal.