Query - Left Join - Expire Post after 30 days

Pretty new to Laravel and I'm trying to update the code of an old project to Laravel. My question is how should I go about writing a LEFT JOIN query? In this example I will use a classifieds page. I understand how to show my classified listings from the database. I would write my classified controller like so:

    public function index()
    {
        $classifieds=Classified::all();
        return view('classifieds.index',compact('classifieds'));
    }

Then in my view call the database like this:

@foreach($classifieds as $cls)
Call Title{{$cls->title}}
Call Description{{$cls->description}}
Call Image<img src="{{$cls->listing_image}}">
ETC...
@endforeach

In this case I'm having issues with a LEFT JOIN query. I'm wanting the classified listing to expire after 30 days.

This is my old working code, but how would I rewrite this in Laravel? And would I put this in my controller? How can I use it to extend the above public function? Query Code:

$classifieds = DB::getInstance()->query("SELECT users.id, users.first_name, users.last_name,  users.avatar,
classifieds.title, classifieds.dates, classifieds.file_name, classifieds.description
FROM users LEFT JOIN classifieds ON users.id = classifieds.user_id
WHERE classifieds.dates > DATE_SUB(NOW(), INTERVAL 43200 MINUTE) AND classifieds.user_id=$userid");

Any help would be greatly appreciated. Thank you!

Sanctuary

8 years ago

Best Answer

Level 12

I think you would do something like this (assuming you have a User model):

User::leftJoin('classifieds', 'users.id', '=', 'classifieds.user_id')
    ->whereRaw("classifieds.dates > DATE_SUB(NOW(), INTERVAL 43200 MINUTE) AND classifieds.user_id = $userid")
    ->select(
        'users.id', 'users.first_name', 'users.last_name', 'users.avatar',
        'classifieds.title', 'classifieds.dates', 'classifieds.file_name', 'classifieds.description'
    )
    ->get();

Untested, so someone please correct me if I'm wrong.

You could either do this directly in your controller (nothing really wrong with that), but if you think you're gonna need it in multiple places, maybe you wanna look into local query scopes. For example, on your User model:

public function scopeSomeScope($query, $userID) {
    return $query::leftJoin('classifieds', 'users.id', '=', 'classifieds.user_id')
        ->whereRaw("classifieds.dates > DATE_SUB(NOW(), INTERVAL 43200 MINUTE) AND classifieds.user_id = $userID")
        ->addSelect(
            'users.id', 'users.first_name', 'users.last_name', 'users.avatar',
            'classifieds.title', 'classifieds.dates', 'classifieds.file_name', 'classifieds.description'
        );
}

And then you'd call it as:

User::someScope()->get();

Do note the use of ->addSelect() instead of just ->select(). This allows you to add onto your select statement instead of overriding it entirely, if you choose to add more to it when interacting with the method. For example:

User::select('created_at')->someScope()->get();

You can read more about the syntax under the Joins section on Query Builder docs page: https://laravel.com/docs/5.6/queries#joins

daugaard47

8 years ago

Level 7

@Sanctuary Thank you for the help and the detailed explanation . That worked great, much appreciated!

Please or to participate in this conversation.