Issue in laravel 5.3 relationship?

Question

I want to search in contacts table and in communication links table according to name or mobile no. where my mobile is in communication_links table in 'value' and fname and lname is in contacts table. but there is a relation in communication link and in resource_status by using rest_id. and again resource_status is connected with resource type.

This is my raw sql query

SELECT  contacts.fname, contacts.lname, communication_links.value FROM contacts ,communication_links,resource_status 
                    where 
                    (contacts.id = communication_links.cont_id)
                    AND
                    (communication_links.rest_id = resource_status.id)
                    AND
                    resource_type.status LIKE '{mobile}%' 
                    AND 
                   (communication_links.value LIKE '{$search_string}%' OR 
                    contacts.fname LIKE '{$search_string}% OR contacts.lname LIKE '{$search_string}%  )

I have following table structure

contacts
  id
  fname
  lname
  dob

communication_links
  id
  rest_id
  cont_id
  value

resource_type
  id
  type

resource_status
  id
  Status
  rety_id

And I created following models to maintain the relationship

Contact.php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Contact extends Model
{
    protected $connection = 'mysql_freesubs';

    public $timestamps = false;

    public function communicationLinks()
    {
        return $this->hasMany(Communication_link::class, 'cont_id');
    }
}

Communication_link.php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Communication_link extends Model
{
    protected $connection = 'mysql_freesubs';


    public $timestamps = false;

    public function resource()
    {
        return $this->belongsTo(Resource_status::class, 'rest_id');
    }

    public function contact()
    {
        return $this->belongsTo(Contact::class, 'cont_id');
    }
}

Resource_status.php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Resource_status extends Model
{
    protected $connection = 'mysql_freesubs';
    protected $table = 'resource_status';


}

Resource_type.php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Resource_type extends Model
{
     protected $connection = 'mysql_freesubs';

    public $timestamps = false;

    public function resourceStatuses()
    {
        return $this->hasMany(Resource_status::class, 'rety_id');
    }
}

I tried to write same query as i have given above but i haven't got a desire result:

$parent_contact = \App\Contact::with('communicationLinks.resource')
                                            ->whereHas('communicationLinks.resource', function ($query) {
                                                    $query->where('status', 'LIKE', "{mobile}%");
                                                })

                                            ->whereHas('communicationLinks.contact',function ($query) use($request) {
                                                $query->where('communicationLinks.value','LIKE',"{$request->search_string}%")
                                                      ->orWhere('fname','LIKE',"{$request->search_string}%")
                                                      ->orWhere('lname','LIKE',"{$request->search_string}%");
                                                })
                                             ->get();

Answer 1

   $query = DB::table("contacts as a")
              ->select(array("a.fname", "b.lname", "b.value"))
              ->join("communication_links AS b", "a.id", "=", "b.cont_id")
              ->join("resource_type AS c", "b.rest_id", "=", "c.id")
              ->where("a.status", "like", $mobile."%")
              ->where("b.value", "like", $search_string."%")
              ->orWhere("a.fname", "like", $search_string."%")
              ->orWhere("a.lname", "like", $search_string."%")
              ->get();

That should do the job for you.

You can just iterate through it to get your values as desired