Ad

Not Able To Retrieve Data From Two Table In Octobercms

- 1 answer

I am new in OctoberCMS. I am facing problem retrieving data from two table. I have two tables Member and Profession.

Schema::create('members', function($table)
        {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('member_name');            
            $table->text('member_detail');
            $table->string('member_city');
            $table->string('member_state');           
            $table->integer('profession_id')->nullable()->unsigned();           
        });

and

Schema::create('profession', function($table)
        {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('title');          
        });

Member model related:

public $belongsTo = [
        'profession' => 'Harpal\Project\Models\Profession'                   
    ];

Profession model related:

public $hasOne = [
        'member' =>   'Harpal\Project\Models\Member'
    ];

In front-end, i want to retrieve data from both the tables using code

$members = Db::table('harpal_project_professions')
    ->join('harpal_project_members', 'harpal_project_professions.id', '=', 'harpal_project_members.profession_id')    
    ->select('harpal_project_professions.title', 'harpal_project_members.*')
   ->get();
    dd($this['members']);

It returns null in october cms. If i execute the above query in PHPmyAdmin it returns all the data. Can anyone help?

Ad

Answer

You have access to the relation from one model or the other. These examples are used in a plugin's component.php file and it's default.htm file.

I want to retrieve members:

PHP CODE

use Author\Plugin\Models\Members;
public function getMembers() {
    return Members::all();
}

TWIG CODE

{% set members = __SELF__.getMembers() %}

{% for member in members %}
<p>{{ member.name }}</p>
{% endfor %}

Now I want to get the Profession Titles. In the twig code I can access it with dot (.) scheme (this won't work with belongsToMany or hasMany).

TWIG CODE

{% set members = __SELF__.getMembers() %}

{% for member in members %}
<p>{{ member.name }} - {{ member.profession.title }}</p>
{% endfor %}

Getting to the Profession data in PHP Code is just as easy. For example in a component I can filter members by profession:

use Author\Plugin\Models\Members;
public function getMembers() {
    return Members::all();
}

public function filterMembers($profession = null) {
    if ($profession) { //If profession query the model
        return Members::whereHas('profession', function ($query) use ($profession) { //Create query function using laravel whereHas method
            $query->where('title', $profession); // We want to return members with matching title
        })->get();
    } else {
        return Members::all(); // If no profession return all
    }
}

TWIG CODE

{% set members = __SELF__.filterMembers('Manager') %} //Notice that we are looking for profession title of 'Manager'

{% for member in members %}
<p>{{ member.name }} - Manager</p> 
{% endfor %} //We can hard code Manager because ware looking for only members that are manager

Comment if you need to clarify somehting.

Ad
source: stackoverflow.com
Ad