Level 102
Show your current query that isn't working
Jan 18, 2022
14
Level 1
Joining Two Tables using Query Builder in Laravel
Hello I want to join two tables in laravel using query builder. I want to display the sku of particular product on the product page. I have tried but only first row is showing. Please can anyone help me in this issue. Below are the tables
products(id, name, price)
products_attributes(id, product_id(FK), sku)
products(1, toy1, 10) (2, toy2, 12)
products_attributes( 1, 1, 7866)(2, 2, 7767)
Level 1
@Sinnbeck $result['productattr']=DB::table('products_attr') ->select('products_attr.', 'products.') ->join('products','products.id','=','products_attr.product_id') ->get();
Level 102
@jawi289p that select looks weird? The rest looks fine
select('sku', 'products.*')
Level 1
@Sinnbeck I have used this but I am getting only the first row of the products_attributes
using this in blade view {{$productattr[0]->sku}}
How can I show the sku of the particular product?
Level 102
@jawi289p yes? You are asking for just index 0?
@foreach ($productattr as $prod)
{{$prod->sku}}
@endforeach
Level 1
@Sinnbeck you didn't understood what I wanted to say.
Here is the products table https://ibb.co/gW9nykX
Here is the products_attributes table https://ibb.co/FsYTzZF
Front view of a product https://ibb.co/JKyffkH
See the front view. The sku is displayed wrong (It should have displayed 005423). How can I correct it (for other products it should display their sku's)
Level 102
@jawi289p sorry still confused. Show what you are getting vs what you are expecting. You can just write it in a code block
Or show the surrounding code in that view
Level 1
@Sinnbeck thank you it is solved now
Level 73
So in your Product model you should have a relationship like this:
public function productAttributes()
{
return $this->hasOne(ProductAttribute::class);
}
then it is easy:
$products = Product::with('productAttributes')->get();
foreach($products as $product)
{
// $product->productAttributes->sku;
}
That's in case there is only one product attributes row. Make sure if your table is named products_attributes
in your ProductAttribute class add protected $table = 'products_attributes';
Level 1
@Nakov I am using query builder not models
Level 73
@jawi289p then add .* so it selects the columns:
DB::table('products_attr')
->select('products_attr.*', 'products.*')
->join('products','products.id','=','products_attr.product_id')
->get();
but that will return a collection of all product attributes.. if you want a specific one you'll need to add a where clause and use the product id
Level 1
@Nakov I have used this but I am getting only the first row of the products_attributes
using this in blade view {{$productattr[0]->sku}}
How can I show the sku of the particular product?
Level 73
The reason why you are getting the first row is because of this [0] you see.. that will always return the first row no matter how many you have. I showed you a good way above, and I don't see a reason why not to use the models but the query builder instead.. if you want to use the query builder you still can, just reverse the joins:
$products = DB::table('products')
->select('products.id as product_id, products.name, products.price', 'products_attr.sku')
->join('products_attr','products.id','=','products_attr.product_id')
->get();
$products->firstWhere('product_id', 1)->sku ?? null;
Level 1
@Nakov thank you it is solved now
Please or to participate in this conversation.