Saturday, March 4, 2017

Laravel 5.2. Create a complex query using eloquent-relationships

I have several tables and want to join them, using eloquent-relationships, but I don't know how.

Picture with all tables:

all tables

First table (Leads) is connected with table sphere_attributes using relation:

leads.sphere_id = sphere_attributes.sphere_id

From this relation I want to get column "label".

Values for theese labels are stored in column "value" of another table "sphere_attribute_options".

Table Leads is related to sphere_attribute_options as:

    leads.sphere_id = sphere_attributes.sphere_id
    sphere_attributes.id = sphere_attribute_options.sphere_attr_id
    AND sphere_attribute_options.ctype=’agent’

I only need such values from sphere_attribute_options, where field fb_AID_OID=1 (AID= sphere_attributes.id, OID= sphere_attribute_options.id) of table sphere_bitmask_XX (XX = sphere_id).

Relation between tables sphere_bitmask_XX and leads:

sphere_bitmask_XX.user_id=leads.id AND sphere_bitmask_XX.type=’lead’

My Models:

Lead

namespace App\Models;

use Cartalyst\Sentinel\Users\EloquentUser;

class Lead extends EloquentUser
{

    protected $table = "leads";

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'agent_id', 'sphere_id',
    ];


    public function sphere()
    {
        return $this->hasOne('App\Models\Sphere', 'id', 'sphere_id');
    }

    public function labels()
    {
        return $this->hasMany('App\Models\SphereAttr', 'sphere_id', 'sphere_id');
    }
}

SphereAttrOptions

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class SphereAttrOptions extends Model
{
    protected $table = 'sphere_attribute_options';

    protected $fillable = ['sphere_attr_id', 'ctype', '_type', 'name', 'value', 'icon', 'position'];

    public function attribute()
    {
        return $this->belongsTo('App\Models\SphereAttr', 'id', 'sphere_attr_id');
    }
}

SphereAttr

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class SphereAttr extends Model
{
    protected $table = 'sphere_attributes';
    protected $fillable = ['_type', 'label', 'icon', 'required', 'position'];

    public function options()
    {
        return $this->hasMany('App\Models\SphereAttrOptions', 'sphere_attr_id', 'id')
            ->where('ctype', '=', 'agent')->orderBy('position');
    }

    public function sphere()
    {
        return $this->belongsTo('App\Models\Sphere', 'id', 'sphere_id');
    }

}

SphereMask

namespace App\Models;

use DB;
use Illuminate\Database\Eloquent\Model;

class SphereMask extends Model
{

}



via Ratcat71

Advertisement