executing this kind of query possible?

Use getPdo, https://laracasts.com/discuss/channels/guides/getpdo-usage

You should be able to write a regular query you familiar with.

I use regular queries quite often.

Just quick example

    public function getOne($dogid)
    {
        $sql = "SELECT * FROM " . PREFIX . "dogs WHERE dogid = :dogid";
        $sth = DB::getPdo()->prepare($sql);
        $params = [':dogid' => $dogid];
        $sth->execute($params);
        return $sth->fetch(\PDO::FETCH_OBJ);
    }

Make sure you import the DB facade.

Laravel DB facade return a collection, so instead of using ->get() you can use ->first() , example:

$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;

In your case should be something like:

$users = DB::table('project_stream')
            ->leftJoin('stream_users', 'stream_users.stream_id', '=', 'project_stream.stream_id')
            ->where('project_stream.project_id', $project->id)
            ->first();

Reference in doc:

https://laravel.com/docs/8.x/queries#joins

https://laravel.com/docs/8.x/queries#retrieving-results

this is very similar to what I wound up with!

thank you to both responses, I am very grateful!