Is the following query is correct one?

Question

I am using laravel eloquent model and i want to join multiple tables so i wrote following query but i didn't get the expected result.

And the table structure is

resource_type 
  id
  type

communiction_links
  id
  inst_id
  rety_id
  cont_id
  value

contact
  id
  fname
  lname
  image
  park

And the query is

\App\Contact::join('communication_links', 'contacts.id', '=', 'communication_links.cont_id')
    ->join('resource_types','resource_types.id','=','communication_links.rety_id')
    ->select(
        'contacts.id',
        'contacts.image',
        'contacts.fname',
        'contacts.lname',
        'communication_links.value'
    )
    ->where('resource_types.type', 'LIKE', "{mobile}%")
    ->orWhere('communication_links.value', 'LIKE', "{$request->search_string}%")
    ->orWhere('contacts.fname', 'LIKE', "{$request->search_string}%")
    ->orWhere('contacts.lname', 'LIKE', "{$request->search_string}%")
    ->get();

Have I followed this correctly? What do you think?

Answer 1

With Eloquent you can define relationships between different tables.

With the schema you have provided you would have your Eloquent models set up something like:

Contact (Contact.php)

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Contact extends Model
{
    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    public $timestamps = false;

    /**
     * Communication Links Relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function communicationLinks()
    {
        return $this->hasMany(CommunicationLink::class, 'cont_id');
    }
}

ResourceType (ResourceType.php)

namespace App;

use Illuminate\Database\Eloquent\Model;

class ResourceType extends Model
{
    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    public $timestamps = false;
    /**
     * Communication Links Relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function communicationLinks()
    {
        return $this->hasMany(CommunicationLink::class, 'rety_id');
    }
}

CommunicationLink (CommunicationLink.php)

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class CommunicationLink extends Model
{
    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    public $timestamps = false;

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

    /**
     * Resource Types Relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\HasManyThrough
     */
    public function resourceType()
    {
        return $this->belongsTo(ResourceType::class, 'rety_id');
    }
}

Laravel's convention with Models names is that they will always be the singular version of the resource and the table name will be the plural e.g.

table name : posts Model name : Post

You don't have to follow this but it will mean you will have to declare the table name in your model. Also, as you haven't got created_at and updated_at in your tables you need to add public $timestamps = false; to your models.

With Eloquent you above query would become:

$results = \App\Contact::with('communicationLinks.resourceType')
    ->where('fname', 'LIKE', "{$request->search_string}%")
    ->orWhere('lname', 'LIKE', "{$request->search_string}%")
    ->orWhereHas('communicationLinks.resourceType', function ($q) {
        $q->where('type', 'LIKE', "{mobile}%");
    })
    ->orWhereHas('communicationLinks', function ($q) use ($request) {
        $q->where('value', 'LIKE', "{$request->search_string}%");
    })
    ->get();

If you're not bothered about the information from resource_types that you can change:

with('communicationLinks.resourceType')

to be:

with('communicationLinks')

For more information you can look at the documentation:

https://laravel.com/docs/5.3/eloquent https://laravel.com/docs/5.3/eloquent-relationships

Or check out these tutorials (as it's an older version some the application structure might be a bit different but the Eloquent Logic should be the same)

https://laracasts.com/series/laravel-5-fundamentals/episodes/8 https://laracasts.com/series/laravel-5-fundamentals/episodes/14

Hope this helps!