Filtering on another database table

[johnboc]

I am struggling to create a filter that uses a table "content_features" from another database "sac_repo_db".

If the user doesn’t select a filter the query is created correctly:

SELECT 'purchased_contents'.'id', 'purchased_contents'.'title', 'purchased_contents'.'description', 'sac_repo_db'.'content_features'.'content_type' FROM 'purchased_contents' inner join 'sac_repo_db'.'content_features' on 'purchased_contents'.'content_feature_id' = 'sac_repo_db'.'content_features'.'id' WHERE 'content_type_id' > '0'

However when a filter is applied the query omits the reference to the other database:

select 'purchased_contents'.'id', 'purchased_contents'.'title', 'purchased_contents'.'description', 'sac_repo_db'.'content_features'.'content_type' from 'purchased_contents' inner join 'sac_repo_db'.'content_features' on 'purchased_contents'.'content_feature_id' = 'sac_repo_db'.'content_features'.'id' where exists (select * from 'content_features' where 'purchased_contents'.'content_feature_id' = 'content_features'.'id' and 'content_type_id' in (6))

What it should create is this: select 'purchased_contents'.'id', 'purchased_contents'.'title', 'purchased_contents'.'description', 'sac_repo_db'.'content_features'.'content_type' from 'purchased_contents' inner join 'sac_repo_db'.'content_features' on 'purchased_contents'.'content_feature_id' = 'sac_repo_db'.'content_features'.'id' where exists (select * from 'sac_repo_db'.'content_features' where 'purchased_contents'.'content_feature_id' = 'sac_repo_db'.'content_features'.'id' and 'content_type_id' in (6))

I realise this is rather a complex question, but I think I am probably missing something fairly simple to get the filter to construct the correct query. It works fine if I use Any help would much appreciated.

PurchasedContentTableController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Cache;
use Illuminate\Http\Request;
use App\ModelFilters\PurchasedContentFilter;
use App\PurchasedContent;

class PurchasedContentTableController extends Controller
{
    public function filteredIndex(Request $request)
    {
        return PurchasedContent
            ::select('purchased_contents.id', 'purchased_contents.title', 'purchased_contents.description', 'sac_repo_db.content_features.content_type')
            ->join('sac_repo_db.content_features', 'purchased_contents.content_feature_id', '=', 'sac_repo_db.content_features.id')
            ->filter($request->all())->get();
    }
}

PurchasedContentFilter.php

<?php
namespace App\ModelFilters;

use EloquentFilter\ModelFilter;

class PurchasedContentFilter extends ModelFilter
{
    public $relations = [
    ];

    public function contentFeatures($content_type_ids)
    {        
        if ( count($content_type_ids) > 0 ) {
            return $this->whereHas('contentFeature', function($query) use ($content_type_ids)
            {
                return $query->whereIn('content_type_id', $content_type_ids);
            });
        }

        return $this->where('content_type_id', '>', 0);
    }
}

PurchasedContent.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use EloquentFilter\Filterable;

class PurchasedContent extends Model
{

    use Filterable;

    protected $guarded = ['id', 'created_at', 'updated_at'];

    protected $dates = ['purchased_at', 'created_at', 'updated_at'];

    public function modelFilter()
    {
        return $this->provideFilter(ModelFilters\PurchasedContentFilter::class);
    }

    public function contentFeature()
    {
        return $this->belongsTo('App\RepoModels\ContentFeature');
    }
}

ContentFeature.php

<?php

namespace App\RepoModels;

use Illuminate\Database\Eloquent\Model;

class ContentFeature extends Model
{

    protected $connection = 'mysql_repo';

    protected $guarded = ['id', 'created_at', 'updated_at'];

    protected $dates = ['created_at', 'updated_at'];

    public function contents()
    {
        return $this->hasMany(Content::class);
    }
}

[Tucker-Eric]

Looking at it briefly it looks like it's the way the relation is defined. Does it work when you update the PurchasedContent#contentFeature relation to:

public function contentFeature()
{
    return $this->belongsTo('App\RepoModels\ContentFeature', 'sac_repo_db.content_features');
}

[johnboc]

Thanks for the quick reply. The following change:

public function contentFeature()
{
    return $this->belongsTo('App\RepoModels\ContentFeature', 'purchased_contents.content_feature_id', 'sac_repo_db.content_features.id');
}

creates this query which is closer but still missing where exists (select * from **sac_repo_db**.content_features ...

select purchased_contents.id, purchased_contents.title, purchased_contents.description, sac_repo_db.content_features.content_type 
  from purchased_contents 
  inner join sac_repo_db.content_features 
  on purchased_contents.content_feature_id = sac_repo_db.content_features.id 
  where exists (select * from content_features where purchased_contents.content_feature_id = sac_repo_db.content_features.id and content_type_id in (6))

[Tucker-Eric]

Ahhh, what happens when you remove that definition we just did in PurchasedContent and in your ContentFeature model, add a $table property:

protected $table = 'sac_repo_db.content_features';

If that doesn't initially work, try it without the $connection property.

[johnboc]

Brilliant - that did the trick. Many thanks indeed.

Regards