Controller keeps generating SQL query with wrong foreign key name

Question

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()"

check this post for your last error https://stackoverflow.com/questions/47710805/laravel-api-resource-call-to-undefined-method-illuminate-database-query-builder — Giacomo M, Aug 07 '19 at 14:30
Does it work if you do `auth()->user()->listings()->latest()->get()`? `->latest()` doesn't actually execute the query; it's shorthand for `->orderBy('created_at', 'desc')`, so you've got a `Builder` instance instead of a `Collection` at that point. — Tim Lewis, Aug 07 '19 at 14:34
@TimLewis shoot. I missed that small detail. This resolved this issue for me. Thanks a lot! — pu4cu, Aug 07 '19 at 14:42

score 1 · Accepted Answer · answered Aug 07 '19 at 14:49

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.

Controller keeps generating SQL query with wrong foreign key name

1 Answers1