Question 301546 in Laravel Question posted in
The official Laravel Documentation can be found here.

how to get data from other table that is not directly related in laravel?

I’m quite new to Laravel so I don’t quite understand the eloquent and it’s query syntaxes.

I want to get the name field in table products from ProposalController, but the table relationships are like this:

I read that I can use whereHas if there’s a relationship between the wanted table. but can it be used for this case?

tb_proposal
-----------
id // primary key
customer_name
grand_total

tb_detail_proposal
------------------
id // primary key
proposal_id // foreign key to tb_proposal.id
product_id // foreign key to tb_product.id
qty
price

tb_product
----------
id // primary key
product_name
stock
price

this is what I have now in ProposalController.php:

class ProposalController extends Controller
{
    public function index()
    {
        $data = Proposal::latest();
        if (request('search')) {
            $data->where('customer_name', 'like', '%'.request('search').'%')
                ->orWhereHas('tb_product', function (Builder $query) {
                    $query->where('product_name', 'like', '%'.request('search').'%')
                });
        }

        $view_data = [
            'page_title' => 'Browse Proposals',
            'active' => 'proposal',
            'data' => $data->get()
        ];
        return view('proposal.index', $view_data);
    }
}
Tags:

2

Answers


  1. 0

    No , because whereHas method in Laravel is specifically designed for querying based on relationships.

    if you want to query the products table directly without a relationship, you would typically use a join (read this) or a separate query for the products table.

    // DRY: Don't repeat yourself
$searchTerm = request('search');

// Retrieve proposals with a join to related tables
$proposals = Proposal::join('tb_detail_proposal', 'tb_proposal.id', '=', 'tb_detail_proposal.proposal_id')
    ->join('tb_product', 'tb_detail_proposal.product_id', '=', 'tb_product.id')
    ->where('tb_product.product_name', 'like', '%' . $searchTerm . '%')
    ->select('tb_product.*')
    ->get();
    Login or Signup to reply.
  2. 0

    Why don’t you instead move that logic into your model and keep your controller clean?

    Here’s what I would do:

    Define the relationship between Product and Proposal:

    <?php

use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentRelationsBelongsToMany;

class Product extends Model
{
    // your code

    // define the relationship
    public function proposals(): BelongsToMany
    {
        return $this->belongsToMany(Proposal::class)
            ->as('details');
    }
}

    The details inside the relationship will give you the ability to get qty and price, see here.

    Define the inverse relationship in Proposal:

    <?php

use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentRelationsBelongsToMany;

class Proposal extends Model
{
    // your code

    // define the relationship
    public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class)
            ->as('details');
    }
}

    With these changes you can get either collection with the related models as such:

    <?php

$proposal = Proposal::with('products')->latest();

$qty = $proposal->details->qty;
$price = $proposal->details->price;
$products = $proposal->products;

    Now, how about the search term? You can create a scope (either a global or local scope, according to your needs). Here’s the Proposal model, with an added search scope:

    <?php

// previous imports
use IlluminateContractsDatabaseEloquentBuilder;

class Proposal extends Model
{
    // previous code

    public function scopeSearch(Builder $query, string $search): void
    {
        if (trim($search) == '') {
            return;
        }

        $query->where('customer_name', 'LIKE', "%{$search}%")
            ->orWhereHas('products', function (Builder $query) use ($search) {
                $query->where('product_name', 'LIKE', "%{$search}%");
            });
    }
}

    To use this in your controller you simply call:

    <?php

$search = request('search');
$proposal = Proposal::with('products')->search($search)->latest();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search