CODEWITHSUNDEEP
mysqllaraveleloquentrelational-database
swatantra
swatantra

Reputation: 483

wrong sql queries with laravel relation

Here are my tables

Schema::create('badge_user', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->references('id')->on('users')->onUpdate('cascade')->onDelete('cascade');
            $table->foreignId('badge_id')->references('id')->on('badges')->onUpdate('cascade')->onDelete('cascade');
            $table->timestamps();
            $table->softDeletes();
        });

Schema::create('badges', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->text('description')->nullable();
            $table->string('image')->nullable();
            $table->integer('condition');
            $table->timestamps();
            $table->softDeletes();
        });

Here are relationships In BagdeUser modal

public function badge()
    {
        return $this->hasMany(Badge::class);

    }

In Badge modal

 public function badgeUser()
    {
        return $this->belongsTo(BadgeUser::class , 'badge_id');

    }

In my resource

I have fetched all the data from the badge_user table and passed it in the resource

public function toArray($request)
    {
        return [
            'badges' => new BadgeResource($this->badge),
        ];
    }

BadeResource

public function toArray($request)
    {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'description' => $this->description,
            'image' => new MediaResource($this->getMedia('badge')->first()),
            'condition' => $this->condition,
        ];

While fetching data o got this

Column not found: 1054 Unknown column 'badges.badge_user_id' in 'where clause' (SQL: select * from `badges` where `badges`.`badge_user_id` = 1 and `badges`.`badge_user_id` is not null and `badges`.`deleted_at` is null)

Now I want the badges associate with the user

Upvotes: 0

Views: 166

Answers (1)

Lukas Grofcik
Lukas Grofcik

Reputation: 498

The problem is that in your badge_user migration, you create foreign key badge_id which would mean that there is a relation Badge User N:1 Badge

But in your models you assign that BadgeUser has many Badges and Badge belongs to BadgeUser (which is Badge User 1:N Badge)

That is why laravel is looking for badge_user_id in query, because you defined the relationship the other way around.

Still tho you are probably doing M:N relations which you don't need to do manually.

You should use something like this (from Laravel docs)

return $this->belongsToMany(Role::class);

Upvotes: 1

Related Questions