laravel join table if data exists

Question

What is the best way to join table A with table B, if table B has data and if not just give me data from table A? because if I do it in this way, and there are no photos in table B I don't get data from that row from table A.

$data =  Category::join('photos', 'categories.cover_id', '=', 'photos.id')
    ->get(['categories.id',
           'categories.position', 
           'categories.visible', 
           'categories.created_at', 
           'categories.updated_at', 
           'categories.title', 
           'photos.filename']);
    return $data;

my idea is just to make another request to get all data from table A where categories.cover_id is 0 (without join)

my tables are just

table A (categories)
-------------------------------
| id | title | cover_id | ... |
-------------------------------
| 1  | lorem |    1     | ... |
-------------------------------
| 2  | ipsum |    12    | ... |
-------------------------------
| 3  | dolor |    0     | ... |
-------------------------------

table B (Photos, there is no data for dolor, because i created dolor recently in table A)
---------------------------------
| id | title |  filename  | ... |
---------------------------------
| 1  | lorem |  lorem.jpg | ... |
---------------------------------
| .. | ..... |  ...jpg    | ... |
---------------------------------
| 12 | ipsum |  ipsum.jpg | ... |
---------------------------------

Answer 1

You should be fine by just using a leftJoin(). A normal ("inner join") will only return results from both tables. But a left join returns all results from the left table (in this case categories) and everything that exists from the other table.

$data =  Category::leftJoin('photos', 'categories.cover_id', '=', 'photos.id')
->get(['categories.id',
       'categories.position', 
       'categories.visible', 
       'categories.created_at', 
       'categories.updated_at', 
       'categories.title', 
       'photos.filename']);

Or you could...

Use the power of Eloquent

You only need to define the relationship (I assume you already have a Photo model) and this gets a lot easier

class Category extends Eloquent {
    public function photos(){
        return $this->hasMany('Photo', 'cover_id');
    }
}

And then...

$data = Category::with('photos')->get();

And you will have the photos model nested inside the category models. Accessible like this:

foreach($data as $category){
    foreach($category->photos as $photo){
        echo $photo->filename;
    }
}