CODEWITHSUNDEEP
mysqllaravelleft-join
TJ Sherrill
TJ Sherrill

Reputation: 2645

Laravel 5, left join returns all items from right side of join

I have 3 tables: "items", "faq" and a join table: "item_faq". The item_faq table holds the relationship as well as an order column to help order faqs per item.

Currently in the database there are a total of 5 faqs. There are a total of 9 rows in the item_faq table relating those 5 faqs to multiple items.

The context is that the admin can create/edit an item and select which faq's will be included, and their order.

On the item admin create/edit view I need to display all faqs and be able to checkbox which ones I want to include.

In my controller I am doing a left join like this:

$faqs = \App\Faq::leftJoin('item_faq', 'faq.id', '=', 'item_faq.faq_id')->get();

I am dd($faqs) to test what is returned and I am getting 9 objects. This is where my issue is: I need it to return the 5 faqs and if there is a item_faq that is related to this item and faq then the faq checkbox is checked.

Schema:

Item (40+ rows in db): id, other fields

Faq (there are 5 existing rows in db): id, question, answer, other fields

item_faq (9 rows in db): item_id, faq_id, order, date fields

Both Item and FAQ are in a many_to_many with eachother

What I want to get back

5 faq's and if there is a item_faq listing for the item and faq, that it would check that box.

Upvotes: 2

Views: 1643

Answers (3)

João Pinho
João Pinho

Reputation: 365

Let me see if i get it: You want the faq with their items (if it exists), right? If that's what you want, you can use the with method like this:

$faqs = \App\Faq::with('items')->get();

On your Faq model you should have a relationship with Item

public method Items() {
    return $this->hasMany(Item::class);
}

Now you can get items from faqs with

foreach($faqs as $faq) {
    var_dump($faq->items);
}

EDITED:

I think you need to create a function to check every checkbox and see if it should be checked like this: In your Faq model create this method:

public function hasItem($item) {
    return in_array($item, $this->items()->toArray());
}

In your blade for each item, you check if it's from FAQ. Like this:

<input type="checkbox" name="items[]" id="{{ $item-id }}" {{ $faq->hasItem($item-id) ? 'checked' : '' }}>

Try this, please.

Upvotes: 2

Nour
Nour

Reputation: 1497

DB::table('faq')
->leftjoin('item_faq','faq.id','=','item_faq.faq_id')
->leftjoin('item','item_faq.item','=','item.id')
->select('faq.*','item.id as iId')->get();

Upvotes: 1

santosh
santosh

Reputation: 118

It seems like multiple item_faq rows has same faq_id, that is why it is joining all item faq rows with item_faq.

Only if it has one to one relationship, you can get exactly as many rows as faq has.

If you want a latest faq to be returned for each item, order by faq_id desc and group by item id. returns 5 rows with latest faq for each item

Upvotes: 1

Related Questions