CODEWITHSUNDEEP
phplaravellaravel-5
シェバイクン
シェバイクン

Reputation: 125

Laravel How to join table and merge columns, then set a 'LIKE' query on Eloquent?

ENV:

Laravel 5.7.28

Database mysql

CentOS 7.2

I have 3 table like as below, I need join this 3 table and merge columns (customer.first_name,customer.last_name,customer.address,job.name,job.address,job.date) to set 'like' query.

For example, After coulms merge customer.first_name,customer.last_name,customer.address,job.name,job.address,job.date customer.first_name + customer.last_name + customer.address + job.name + job.address +job.date is 'TOMSMITHCecilia ChapmanABC.LtdIris Watson2019-01-10', so

 when $text = 'MS';
set  'like' '%'.$text.'%' will return below result


customer.first_name = TOM

customer.last_name = SMITH

customer.address = Cecilia Chapman

job.name = ABC.Ltd

job.address = Iris Watson

job.date = 2019-01-10
  1. id table (relation belongs To table customer and job)
    • id
    • customer_id
    • job_id
  2. customer table
    • id
    • first_name
    • last_name
    • address
  3. job id
    • id
    • name
    • address
    • job_date

Upvotes: 0

Views: 748

Answers (2)

Kinjal
Kinjal

Reputation: 91

If you have multiple where condition then you may use where function with orWhere like this:

\DB::table('id')->join('customer','customer.customer_id','id.customer_id')
->join('job','job.id','id.job_id')
->where(function ($query) use($text) {
                $query->where('customer.first_name', 'LIKE', '%' . $text .'%')
                      ->orWhere('customer.last_name', 'LIKE', '%' . $text . '%')
                      ->orWhere('customer.address', 'LIKE', '%' . $text . '%')
                      ->orWhere('job.name', 'LIKE', '%' . $text . '%')
                      ->orWhere('job.address', 'LIKE', '%' . $text . '%')
            })->get();

Upvotes: 0

user8824269
user8824269

Reputation:

See this

\DB::table('id')->join('customer','customer.customer_id','id.customer_id')
->join('job','job.id','id.job_id')
->where('customer.first_name', 'LIKE', '%' . $text . '%')
->orWhere('customer.last_name', 'LIKE', '%' . $text . '%')
->orWhere('customer.address', 'LIKE', '%' . $text . '%')
->orWhere('job.name', 'LIKE', '%' . $text . '%')
->orWhere('job.address', 'LIKE', '%' . $text . '%')
->get();

Upvotes: 2

Related Questions