WhereHas() query is really as slow as lazy turtle, so I created and still using a trait that I glue to any laravel model which required a simple join requests. This trait make a scope function whereJoin(). You can just pass there a joined model class name, where clause params and enjoy. This trait take care of table names and related details in query. Well, it's for my personal use and ofc feel free to modify this monstruosity.
<?php
namespace App\Traits;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\JoinClause;
/** @mixin Model */
trait ModelJoinTrait
{
/**
* @param string|\Countable|array $on
* @param $column
* @param $whereOperator
* @param $value
* @param Model $exemplar
* @return array
*/
function _modelJoinTraitJoinPreset($on, $column, $whereOperator, $value, $exemplar){
$foreignTable = $exemplar->getTable();
$foreignId = $exemplar->getKeyName();
$localTable = $this->getTable();
$localId = $this->getKeyName();
//set up default join and condition parameters
$joinOn =[
'local' => $localTable.'.'.$localId,
'foreign'=> $foreignTable.'.'.$foreignId,
'operator' => '=',
'type'=>'inner',
'alias'=>'_joint_id',
'column'=>$column,
'where_operator'=>$whereOperator,
'value'=>$value
];
//config join parameters based on input
if(is_string($on)){
//if $on is string it treated as foreign key column name for join clause
$joinOn['foreign'] = $foreignTable.'.'.$on;
} elseif (is_countable($on)){
//if $is array or collection there can be join parameters
if(isset($on['local']) && $on['local'])
$joinOn['local'] = $localTable.'.'.$on['local'];
if(isset($on['foreign']) && $on['foreign'])
$joinOn['foreign'] = $localTable.'.'.$on['foreign'];
if(isset($on['operator']) && $on['operator'])
$joinOn['operator'] = $on['operator'];
if(isset($on['alias']) && $on['alias'])
$joinOn['alias'] = $on['alias'];
}
//define join type
$joinTypeArray = ['inner', 'left', 'right', 'cross'];
if(is_countable($on) && isset($on['type']) && in_array($on['type'], $joinTypeArray))
$joinOn = $on['type'];
return $joinOn;
}
/**
* @param Model $exemplar
* @param string|array|\Countable $joinedColumns
* @param string|array|\Countable $ownColumns
* @param string $jointIdAlias
* @return array
*/
function _modelJoinTraitSetColumns($exemplar, $joinedColumns, $ownColumns, $jointIdAlias = '_joint_id')
{
$foreignTable = $exemplar->getTable();
$foreignId = $exemplar->getKeyName();
$localTable = $this->getTable();
$localId = $this->getKeyName();
if(is_string($joinedColumns))
$foreignColumn = ["$foreignTable.$joinedColumns"];
else if(is_countable($joinedColumns)) {
$foreignColumn = array_map(function ($el) use ($foreignTable) {
return "$foreignTable.$el";
}, $joinedColumns);
} else {
$foreignColumn = ["$foreignTable.*"];
}
if(is_string($ownColumns))
$ownColumns = ["$localTable.$ownColumns"];
elseif(is_countable($ownColumns)) {
$ownColumns = array_map(function ($el) use ($localTable) {
return "$localTable.$el";
}, $ownColumns);
} else {
$ownColumns = ["$localTable.*"];
}
$columns = array_merge($foreignColumn, $ownColumns);
if($foreignId == $localId){
$columns = array_merge(["$foreignTable.$foreignId as $jointIdAlias"], $columns);
}
return $columns;
}
/**
* @param Builder $query
* @param string|array|\Countable $on
* @param Model $exemplar
*/
function _modelJoinTraitJoinPerform($query, $on, $exemplar){
$funcTable = ['left'=>'leftJoin', 'right'=>'rightJoin', 'cross'=>'crossJoin', 'inner'=>'join'];
$query->{$funcTable[$on['type']]}($exemplar->getTable(),
function(JoinClause $join) use ($exemplar, $on){
$this->_modelJoinTraitJoinCallback($join, $on);
}
);
}
function _modelJoinTraitJoinCallback(JoinClause $join, $on){
$query = $this->_modelJoinTraitJoinOn($join, $on);
$column = $on['column'];
$operator = $on['where_operator'];
$value = $on['value'];
if(is_string($column))
$query->where($column, $operator, $value);
else if(is_callable($column))
$query->where($column);
}
/**
* @param JoinClause $join
* @param array|\Countable $on
* @return JoinClause
*/
function _modelJoinTraitJoinOn(JoinClause $join, $on){
//execute join query on given parameters
return $join->on($on['local'], $on['operator'], $on['foreign']);
}
/**
* A scope function used on Eloquent models for inner join of another model. After connecting trait in target class
* just use it as ModelClass::query()->whereJoin(...). This query function forces a select() function with
* parameters $joinedColumns and $ownColumns for preventing overwrite primary key on resulting model.
* Columns of base and joined models with same name will be overwritten by base model
*
* @param Builder $query Query given by Eloquent mechanism. It's not exists in
* ModelClass::query()->whereJoin(...) function.
* @param string $class Fully-qualified class name of joined model. Should be descendant of
* Illuminate\Database\Eloquent\Model class.
* @param string|array|\Countable $on Parameter that have join parameters. If it is string, it should be foreign
* key in $class model. If it's an array or Eloquent collection, it can have five elements: 'local' - local key
* in base model, 'foreign' - foreign key in joined $class model (default values - names of respective primary keys),
* 'operator' = comparison operator ('=' by default), 'type' - 'inner', 'left', 'right' and 'cross'
* ('inner' by default) and 'alias' - alias for primary key from joined model if key name is same with key name in
* base model (by default '_joint_id')
* @param Closure|string $column Default Eloquent model::where(...) parameter that will be applied to joined model.
* @param null $operator Default Eloquent model::where(...) parameter that will be applied to joined model.
* @param null $value Default Eloquent model::where(...) parameter that will be applied to joined model.
* @param string[] $joinedColumns Columns from joined model that will be joined to resulting model
* @param string[] $ownColumns Columns from base model that will be included in resulting model
* @return Builder
* @throws \Exception
*/
public function scopeWhereJoin($query, $class, $on, $column, $operator = null, $value=null,
$joinedColumns=['*'], $ownColumns=['*']){
//try to get a fake model of class to get table name and primary key name
/** @var Model $exemplar */
try {
$exemplar = new $class;
} catch (\Exception $ex){
throw new \Exception("Cannot take out data of '$class'");
}
//preset join parameters and conditions
$joinOnArray = $this->_modelJoinTraitJoinPreset($on, $column, $operator, $value, $exemplar);
//set joined and base model columns
$selectedColumns = $this->_modelJoinTraitSetColumns($exemplar, $joinedColumns, $ownColumns, $joinOnArray['alias']);
$query->select($selectedColumns);
//perform join with set parameters;
$this->_modelJoinTraitJoinPerform($query, $joinOnArray, $exemplar);
return $query;
}
}
You can use it like this (Model Goods in example have a dedicated extended data model GoodsData with hasOne relationship between them):
$q = Goods::query();
$q->whereJoin(GoodsData::class, 'goods_id',
function ($q){ //where clause callback
$q->where('recommend', 1);
}
);
//same as previous exmple
$q->whereJoin(GoodsData::class, 'goods_id',
'recommend', 1); //where clause params
// there we have sorted columns from GoodsData model
$q->whereJoin(GoodsData::class, 'goods_id',
'recommend', 1, null, //where clause params
['recommend', 'discount']); //selected columns
//and there - sorted columns from Goods model
$q->whereJoin(GoodsData::class, 'goods_id',
'recommend', '=', 1, //where clause params
['id', 'recommend'], ['id', 'name', 'price']); //selected columns from
//joined and base model
//a bit more complex example but still same. Table names is resolved
//by trait from relevant models
$joinData = [
'type'=>'inner' // inner join `goods_data` on
'local'=>'id', // `goods`.`id`
'operator'=>'=' // =
'foreign'=>'goods_id', // `goods_data`.`goods_id`
];
$q->whereJoin(GoodsData::class, $joinData,
'recommend', '=', 1, //where clause params
['id', 'recommend'], ['id', 'name', 'price']); //selected columns
return $q->get();
Resulting SQL query will be like this
select
`goods_data`.`id` as `_joint_id`, `goods_data`.`id`, `goods_data`.`recommend`,
`goods`.`id`, `goods`.`name`, `goods`.`price` from `goods`
inner join
`goods_data`
on
`goods`.`id` = `goods_data`.`goods_id`
and
-- If callback used then this block will be a nested where clause
-- enclosed in parenthesis
(`recommend` = ? )
-- If used scalar parameters result will be like this
`recommend` = ?
-- so if you have complex queries use a callback for convenience
In your case there should be like this
$q = Replay::query();
$q->whereJoin(Player::class, 'replay_id', 'battletag_name', 'test');
//or
$q->whereJoin(Player::class, 'replay_id',
function ($q){
$q->where('battletag_name', 'test');
}
);
$q->limit(100);
To use it more efficiently, you can go like this:
// Goods.php
class Goods extends Model {
use ModelJoinTrait;
//
public function scopeWhereData($query, $column, $operator = null,
$value = null, $joinedColumns = ['*'], $ownColumns = ['*'])
{
return $query->whereJoin(
GoodsData::class, 'goods_id',
$column, $operator, $value,
$joinedColumns, $ownColumns);
}
}
// -------
// any.php
$query = Goods::whereData('goods_data_column', 1)->get();
PS I dont run any automated tests for this so be careful in use. It works just fine in my case, but there may be unexpected behaviour in yours.
idcolumn improves query performance by less then 1% so it's negligible – Jettiejettison