Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

orest's avatar
Level 13

select on relationship

I have the following models and tables

users
- id
conversation_participants
- id
- conversation_id
- user_id
conversations
- id
- title
class User
{
     public function conversations()
     {
               return $this->belongsToMany(Conversation::class, 'conversation_participants')->wherePivot('hidden', false)
            ->wherePivot('left', false);;
     }
}
class Conversation
{
     public function scopeWithHasBeenUpdated($query, $authUser = null)
    {
        $authId = $authUser ? $authUser->id : auth()->id();

        $readable = get_class($this);
        $readableTable = $this->getTable();

        $read = '(
            SELECT reads.read_at
            FROM   `reads`
            WHERE  reads.readable_id = ' . $readableTable . '.id
                AND reads.readable_type = ?
                AND reads.user_id = ?
        )';

        return $query->selectRaw(
            'CASE
                WHEN ' . $read . ' >= ' . $readableTable . '.updated_at THEN 0
                WHEN ' . $read . ' IS NULL THEN 1
                ELSE 1
            END as has_been_updated'),
            [
                $readable,
                $authId,
                $readable,
                $authId,
            ]
        );

}

If I want to use the scope above I have to use also the select method, because otherwise all the previous selects are overridden

$user->conversations()->select()->withHasBeenUpdated();
select *, CASE\r\n
                WHEN (\r\n
            SELECT reads.read_at\r\n
            FROM   `reads`\r\n
            WHERE  reads.readable_id = conversations.id\r\n
                AND reads.readable_type = ?\r\n
                AND reads.user_id = ?\r\n
        ) >= conversations.updated_at THEN 0\r\n
                WHEN (\r\n
            SELECT reads.read_at\r\n
            FROM   `reads`\r\n
            WHERE  reads.readable_id = conversations.id\r\n
                AND reads.readable_type = ?\r\n
                AND reads.user_id = ?\r\n
        ) IS NULL THEN 1\r\n
                ELSE 1\r\n
            END as has_been_updated from "conversations" inner join "conversation_participants" on "conversations"."id" = "conversation_participants"."conversation_id" where "conversation_participants"."user_id" = ? and "conversation_participants"."hidden" = ? and "conversation_participants"."left" = ?
"""
array:7 [
  0 => "App\Models\Conversation"
  1 => 3
  2 => "App\Models\Conversation"
  3 => 3
  4 => 3
  5 => false
  6 => false
]

The problem with the code above is that for some reason the id of the conversations that are returned, are wrong, while the title of the conversations is correct.

Then I tried to specify the table name inside the select, which produces the following query, and the results are correct.

$user->conversations()->select('conversations.*')->withHasBeenUpdated();
select "conversations".*, CASE\r\n
                WHEN (\r\n
            SELECT reads.read_at\r\n
            FROM   `reads`\r\n
            WHERE  reads.readable_id = conversations.id\r\n
                AND reads.readable_type = ?\r\n
                AND reads.user_id = ?\r\n
        ) >= conversations.updated_at THEN 0\r\n
                WHEN (\r\n
            SELECT reads.read_at\r\n
            FROM   `reads`\r\n
            WHERE  reads.readable_id = conversations.id\r\n
                AND reads.readable_type = ?\r\n
                AND reads.user_id = ?\r\n
        ) IS NULL THEN 1\r\n
                ELSE 1\r\n
            END as has_been_updated from "conversations" inner join "conversation_participants" on "conversations"."id" = "conversation_participants"."conversation_id" where "conversation_participants"."user_id" = ? and "conversation_participants"."hidden" = ? and "conversation_participants"."left" = ?
"""
array:7 [
  0 => "App\Models\Conversation"
  1 => 3
  2 => "App\Models\Conversation"
  3 => 3
  4 => 3
  5 => false
  6 => false
]

P.S the scope does not seem affect the query. Only the select affects the query.

Any idea why this happens ?

0 likes
8 replies
Sinnbeck's avatar

To append selects you use ->addSelect()

If it does not work, try showing your actual code

orest's avatar
Level 13

@Sinnbeck

thanks, I didn't know that.

however, I can't use addSelect because I have bindings that I want to use and DB::raw() does not work with bindings.

Is there a different way to achieve that ?

Sinnbeck's avatar

@orest Can you try this? I think it just needs the id column for the conversations() binding to work.

$user->conversations()->select('conversations.id')->withHasBeenUpdated();

Or in the scope

$query->addSelect($readableTable . '.id');
 return $query->selectRaw(
            'CASE

Please or to participate in this conversation.