How to use Left join in eloquent

You should use relations if you don't want to use joins. If you need joins, then this is the correct way.

Documentation: http://laravel.com/docs/5.1/eloquent-relationships

@bobbybouwmann Thanks. Can you give one sample of using the left join I applied the with() method. And i get a bunch of results and it's working as the sample code below. However I want to use left join. What constraint should I used?

e.g

 $reports = CrimeReport::with('crimeName','crimeType')->get();

@bobbybouwmann a relation is a join.

@jlrdw yeah that's why I got confused when he mention that statement. Can you give example of using left join using eloquent. I just know how to implement a inner join.

See http://dsmithweb.com/itemized.png and read http://laravel.io/forum/04-04-2015-looping-through-collection I wouldn't mess with the orm for a report.

For a custom report I usually use a single page coding with pdo and MySql direct. Much easier.

The whole reason I gave that example was because the person asking wanted to get everything in one query, if you use relationships the do this same thing its join to make 5 queries instead of one. The only option you have to make it reusable is to use query scopes instead ie:

public fucntion scopeWithCrimeName($query)
{
    $query->leftjoin('crime_name as cn','cn.id', '=', 'crime_reports.crime_name_id')
        ->addSelect('cn.came as crime_name');
}

and use it on the query

CrimeReport::withCrimeName()->get()

If your original query works, use it.

@rdelorier Thanks for your response. Appreciate it. So it seems that it's the same with the first solution I provided. I just need to use a function. But I have a problem Isnt it that laravel is an MVC but how come we are using an query in the controller it should be in the model. Do you have some solution with this?

Look at examples in docs. Don't over complicate it.

From docs

<?php

namespace App\Http\Controllers;

use DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->get();

        return view('user.index', ['users' => $users]);
    }
}

This is a controller.

The model is where you set up relations for example a one-to-many or a many-to-many but the controller is where you call those methods in the model, they look like queries but they're not. Whereas querybuilder is used in the controller to query the table model you set up. But don't over complicate things just go with the flow of the docs and you'll be fine.

The has() instead of with() is a left join in eloquent. It only returns the result if the has relationship is true where with eager loads the relationship like a join and still grabs all rows from the main model.

@jekinney i tried has() and it's not doing a left join in laravel 5.5

is scope the only option?

i have a hasMany relationship i'm trying to do a inRandomOrder() but only if it exists. but i want the records to display even if the relationship doesn't exist.

sorry to bring an old topic back to life but i didn't want to post a new topic. seems my question is relevant to this, no?

okay i played with it more. in laravel 5.5 with() actually works for me

so my situation is as follows

a shoe hasMany() shoe_images

shoe_images belongsTo() a shoe

// model
$shoe = Shoe::with('shoeImages')->get();

// view
// if shoeImages relationship exist, display random image
@if ($shoe->shoeImages->count())
    <img src="{{ $shoe->shoeImages->random()->filename }}">
@else
    <img src="/images/stock_image.jpg">
@endif 

@w1n78 WOW, 2 years old..

The has() method will not return a Shoe object (in your case) if it doesn't have a image. So it still works as intended. Looks like your case you want the Shoe no matter what. So different use case.