Laravel Eloquent returns an empty collection on belongsToMany relationship

Question

UPDATE: The issue mentioned here has caused by the use of MariaDB instead of MySQL by XAMPP. I have followed the answer here to switch it to MySQL and it works like a charm.

This is regarding an e-commerce platform.

I have 2 data tables with 1 joining table for a many-to-many join. The idea is for Products to have many Special Offers running at any given time.

Tables

products

+-------+-------------------------------+
| id    | name                          |
+-------+-------------------------------+
| 10001 | Apple iPhone 11               |
| 10002 | Samsung Galaxy S11            |
+-------+-------------------------------+

special_offers

+----+-------------------------------+
| id | name                          |
+----+-------------------------------+
|  1 | Awesome Offer                 |
|  2 | Year End Offer                |
+----+-------------------------------+

product_special_offer

+------------+------------------+----------+
| product_id | special_offer_id | discount |
+------------+------------------+----------+
| 10001      | 1                | 10.0     |
| 10002      | 2                | 12.5     |
+------------+------------------+----------+

Models

Since the requirement is for a many-to-many relationship, I am using belongToMany method in my models.

Product

class Product extends Model
{
    public function specialOffers()
    {
        return $this->belongsToMany(SpecialOffer::class)->withPivot('discount');
    }
}

SpecialOffer

class SpecialOffer extends Model
{
    public function products()
    {
        return $this->belongsToMany(Product::class)->withPivot('discount');
    }
}

Controller

The following is the controller snippet.

ProductController

class ProductController extends Controller
{
    public function index()
    {
        $product = Product::find(10001);

        dd($product->specialOffers);
    }
}

Results

The following is what Laravel returns.

Collection {#610 ▼
  #items: []
}

The query it runs is mentioned below.

select `special_offers`.*, `product_special_offer`.`product_id` as `pivot_product_id`, `product_special_offer`.`special_offer_id` as `pivot_special_offer_id`, `product_special_offer`.`discount` as `pivot_discount` from `special_offers` inner join `product_special_offer` on `special_offers`.`id` = `product_special_offer`.`special_offer_id` where `product_special_offer`.`product_id` = 10001

Change this `withPivot('product_id ','special_offer_id ' );` — Zain Farooq, Oct 04 '19 at 06:18
try this `$this->belongsToMany(SpecialOffer::class, 'product_special_offer');` and `$this->belongsToMany(Product::class, 'product_special_offer');` — Zain Farooq, Oct 04 '19 at 06:33
@ZainFarooq I tried specifying the table name before and did the same now after you mentioned. Still the same. — Prasad N, Oct 04 '19 at 06:36
@ZainFarooq Just to clarify, I am not running a raw query. Instead I am using Eloquent relationships. It is done exactly as I mentioned in the post. — Prasad N, Oct 04 '19 at 06:40
@ZainFarooq Controller snippet added to the post as requested. — Prasad N, Oct 04 '19 at 06:52
Use the specialOffers as function. `$product->specialOffers()` this will work. — Bálint Bakos, Oct 04 '19 at 13:37
MySQL and MariaDB are interchangeable, swapping one for the other would not change anything, especially with a very basic query like the one you were running. — miken32, Mar 18 '20 at 22:14
@miken32 I thought the same. But sadly, I have encountered the same issue the day I edited the question. It was a new model, a table and a query but the issue was the same. So I tried the same query with the same set of data on sqlfiddle (using MySQL) and it was working fine. I switch to MySQL on XAMPP bam - working. — Prasad N, Mar 27 '20 at 18:36

score 0 · Answer 1 · answered Oct 04 '19 at 07:00

0

This could work

class SpecialOffer extends Model
{
    public function products()
    {
        return $this->belongsToMany(Product::class, 'product_special_offer','special_offer_id','product_id');
    }
}

answered Oct 04 '19 at 07:00

HieuMinh

41
5

score 0 · Answer 2 · answered Oct 04 '19 at 07:20

Make a third model to the connection table, and add the two relation. And it's will work.

class ProductSpecialOffer extends Model
{
   public function products() {
       return $this->belongsTo(Product::class);
   }

   public function specialOffers() {
       return $this->belongsTo(SpecialOffer::class);
   }
}

Laravel Eloquent returns an empty collection on belongsToMany relationship

Tables

Models

Controller

Results

2 Answers2