CODEWITHSUNDEEP
phpmysqllaravellaravel-5
user11710915
user11710915

Reputation: 434

Integrity constraint violation joining tables MySql

I have three tables recommends, products and products_photos. In recommends table and products_photos table, they both have product_id column. I want to join them(three) together but I'm getting this error Integrity constraint violation: 1052 Column 'product_id' in field list , how can I join them?

Route::get('/info', function(){
$products = DB::table('recommends')
->leftJoin('products','recommends.product_id','products.id')
->join('products_photos','products_photos.product_id','products.id' 
)
->select('product_id','name','price', DB::raw('count(*) as total'))
->groupBy('product_id','name','price')
->get();
//dd($products);

});

Upvotes: 0

Views: 163

Answers (2)

Muhammad Bilal
Muhammad Bilal

Reputation: 527

The problem is in your Select statement You are selecting product_id which is available in both recommends and products when selecting such column you have to give table name from which you are going to fetch that column Like:

...
->select('recommends.product_id','name','price', DB::raw('count(*) as total'))
...

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

You never specified the equality/inequality operator to be used in your join call. Try doing that, and also qualify all columns you select with the appropriate alias:

Route::get('/info', function() {
    $products = DB::table('recommends AS r')
        ->leftJoin('products AS p', 'r.product_id', '=', 'p.id')
        ->join('products_photos AS pp', 'pp.product_id', '=', 'p.id')
        ->select('p.id', 'p.name', 'p.price', DB::raw('COUNT(*) AS total'))
        ->groupBy('p.id', 'p.name', 'p.price')
        ->get();
});

I assumes that products has the id, name, and price columns.

Upvotes: 2

Related Questions