CODEWITHSUNDEEP
phpmysqllaravellaravel-5.8
pu4cu
pu4cu

Reputation: 165

Controller keeps generating SQL query with wrong foreign key name

I have a database tables Users, Listings Users table:

id, name, email, password

Listings table:

id, title, seller_id

Listing migration:

public function up()
    {
        Schema::create('listings', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->bigInteger('seller_id')->unsigned();
            $table->timestamps();

            $table->foreign('seller_id')->references('id')->on('users')->onDelete('cascade');
        });
    }

User model:

public function listings()
    {
        return $this->hasMany(Listing::class);
    }

Listing model:

public function seller()
    {
        return $this->belongsTo(User::class, 'seller_id', 'id');
    }

ListingResource:

namespace App\Http\Resources;

use Illuminate\Http\Resources\Json\JsonResource;

class ListingResource extends JsonResource
{
    /**
     * Transform the resource into an array.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return array
     */
    public function toArray($request)
    {
        return parent::toArray($request);
    }
}

ListingsController:

public function index()
    {
        return ListingResource::collection(auth()->user()->listings()->latest());
    }

I keep getting this error:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'listings.user_id' in 'where clause' (SQL: select * from listings where listings.user_id = 1 and listings.user_id is not null order by created_at desc limit 1)"

How come it returns the query with user_id as the foreign key, even though I specifically put 'seller_id' as the foreign key inside the User.php model?

I have tried to put:

    public function listings()
    {
        return $this->hasMany(Listing::class, 'seller_id');
    }

As I have read that this could work, but this generates the following error:

"Call to undefined method Illuminate\Database\Eloquent\Relations\HasMany::mapInto()"

Upvotes: 1

Views: 169

Answers (1)

Tim Lewis
Tim Lewis

Reputation: 29258

While the error message isn't the most clear for this, what is essentially happening is that {Resource}::collection($collection); requires a Collection to function, but at the current point in it's lifecycle, your argument is a Builder instance. To fix this, simply pass a closure to convert your Builder to a Collection:

public function index(){
    return ListingResource::collection(auth()->user()->listings()->latest()->get());
}

->latest() is shorthand for ->orderBy('created_at', 'DESC'); (or id, not sure which it uses internally), but doesn't actually execute the query. Simply adding ->get() will convert the Builder to a Collection and allow this resource to work.

On another note, the original error was being caused by missing seller_id in your listings() function on your User model. The foreign id of any relationship is guessed by Laravel based on the Model name (User translates to user_id), but since you're using seller_id, you need to specify that in both the original relationship and in the inverse definition. You figured that out, but a quick explanation is always helpful.

Upvotes: 1

Related Questions