Converting join sql statement to eloquent.

Here's my two cents for what it's worth... the result from this query is not Eloquent, i.e. it is not a Model instance nor a Collection of Model instances, it is something different. Use query builder instead.

Thank you tykus... Let try and convert it using query builder.

Let's! This should be the equivalent of your raw query above:

DB::table('subscribed_services as a')
    ->join('scheduled_messages b', 'a.id', '=', 'b.subscribed_services_id')
    ->join('subscription_services_msisdnas c', 'a.id', '=', 'c.subscribed_services_id')
    ->whereRaw('DATE(b.scheduled_at) = CURDATE()')
    ->selectRaw('b.message, c. phone_number, a.username, a.password')->distinct()
    ->get();

Eloquent doesn't do joins. It breaks your query into smaller queries. In theory smaller more efficient queries. What have you tried? Translating it for you doesn't really help you learn.

I am going to assume you have the relationships set on each model. And I abbreviated the model names: WARNING NOT TESTED and may need some tweaking.

 Service::with(['messages' => function($query) => {
    $query->whereDate('scheduled_at', CURDATE());
}], 'msisdn')->get();

https://laravel.com/docs/5.4/eloquent-relationships Relashionships (also get started and basic videos here)

https://laravel.com/docs/5.4/queries#where-clauses scroll to whereDate()

https://laravel.com/docs/5.4/eloquent-relationships#constraining-eager-loads Constraining eager loading

@tykus Honestly, no joke intended, what's the benefit of that query over the raw sql? You pretty much just wrapped in in raw().

@jekinney there is none - I was being facetious.

@tykus lol, dang it.

If your query works, use as is laravel has getPdo(). To run normal, regular queries.

$sql = "SELECT * from pets where petname like :petname";
        $sth = DB::connection()->getPdo()->prepare($sql);
        $sth->execute(array(':petname' => 'c%'));
        //$sth->execute();
        $results = $sth->fetchAll(\PDO::FETCH_ASSOC);
        print_r($results);
        foreach ($results as $row) {
            echo $row['petname'] . '<br>';
        }

Now of course this

        foreach ($results as $row) {
            echo $row['petname'] . '<br>';
        }

would not go in a controller, but view, this was just quick example of usage.