CODEWITHSUNDEEP
phpmysqllaravel
Choy
Choy

Reputation: 482

how to retrieve data using relationship in laravel

I'm trying to retrieve data using relationship in laravel and I'm getting this error all the time.

Column not found: 1054 Unknown column 'orders.customers_id' in 'where clause' (SQL: select * from orders where orders.customers_id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10))

before this I was using these codes:

 $data = DB::table('customers')
        ->join('orders', 'orders.customer_id', 'customers.id')
        ->get();

    // convert to json string
    $data = json_decode(json_encode($data), true);
    return $data;

and it return the exactly result I want which is: enter image description here

Here is my customers table: enter image description here

orders table enter image description here

Orders

    class Orders extends Model
{
    public function customer(){
        return $this->belongsTo(Customers::class);
    }
}

Customers

 class Customers extends Model
{
    public function order(){
        return $this->hasMany(Orders::class);
    }

DataController

class DataController extends Controller
{
    public function all()
    {

        $All = Customers::with('order','order.customer_id')->paginate(10);

        return response()->json([
            'code' => 0,
            'success' => true,
            'data' => $All,
            'pagination' => [
                'current_page' => $All->currentPage(),
                'last_page' => $All->lastPage(),
                'prev_page_url' => $All->previousPageUrl(),
                'next_page_url' => $All->nextPageUrl(),
                'per_page' => $All->perPage(),
                'total' => $All->total(),
                'count' => $All->count(),
            ]
        ], 200);

Upvotes: 0

Views: 69

Answers (3)

Choy
Choy

Reputation: 482

In my controller

    $All = Customers::with('order')->paginate(10);

    return response()->json([
        'code' => 0,
        'success' => true,
        'data' =>$All

    ], 200);

Customers model

   class Customers extends Model
{
    public function order(){
        return $this->hasMany(Orders::class,'customer_id','id');
    }
}

Orders model

class Orders extends Model
{
    public function customers(){
        return $this->belongsTo(Customers::class,'customer_id','id');
    }
}

and its works. But there is still got one thing which I'm not understand. Its works either I define the relationship in Customers model or Orders model or define the relationship in both

Upvotes: 1

chanafdo
chanafdo

Reputation: 5124

Remove order.customer_id from Customers::with('order','order.customer_id')->paginate(10);

So it should be

Customers::with('orders')->paginate(10);

Also as a customer can have many orders it is best to name your relation as orders

class Customers extends Model
{
    public function orders()
    {
        return $this->hasMany(Orders::class);
    }
}

Upvotes: 1

Jovs
Jovs

Reputation: 892

Could you try this one

Order Model

class Orders extends Model
{
    public function customer(){
        return $this->belongsTo(Customers::class, 'customer_id');
    }
}

DataController

class DataController extends Controller
{
    public function all()
    {

        $All = Customers::order()->paginate(10);

        return response()->json([
            'code' => 0,
            'success' => true,
            'data' => $All,
            'pagination' => [
                'current_page' => $All->currentPage(),
                'last_page' => $All->lastPage(),
                'prev_page_url' => $All->previousPageUrl(),
                'next_page_url' => $All->nextPageUrl(),
                'per_page' => $All->perPage(),
                'total' => $All->total(),
                'count' => $All->count(),
            ]
        ], 200);

I just put the foreign key in the model already. I'm not entirely sure if its supposed to be in Order Model or in Customer Model either way try those both.

Hope it helps!

Upvotes: 1

Related Questions