我正在做一个乱七八糟的项目,其中数据库的结构是:
Object
id, title, description, url ... [and some more columns]
ObjectMeta
object_id, variable, value
那么我们给你:
Object: 1, 'Cool Book', 'This cool book is just for you!', ...
Object Meta:
1, 'author_name', 'John Doe'
1, 'released', 2014
1, 'price', 23.22
1, 'url', 'http://amazon.com/coolbook'
1, 'genre', 3
所以我需要执行查询,它将:
拉出所有类型为3的对象,然后按发布日期对这些对象进行排序。
$objs = static::Active()
->where('object', '=', $object)
->where('category','=',$category)
->whereIn('site_id', array(self::$site_id, 0))
->leftJoin('object_meta', function($join)
{
$join->on('object.id','=', 'object_meta.content_id');
})
->where('object_meta.variable', 'genre')
->where('object_meta.value', 3);
->where('object_meta.variable', 'released');
->orderBy('object_meta.value', 'desc');
->groupBy('object.id')
->paginate(20);
这个查询有0个结果,即使那里有很多这样的书。我知道第二个object_meta。变量where在这里是有罪的。我怎么写这个查询使它工作?
非常感谢你的帮助。
——编辑
我已经创建了一个解决方案,但它是非常非常糟糕的解决方案(有趣的是,我发誓上面的查询工作了一个月左右)。
$objs = static::Active()
->where('object', '=', $object)
->where('category','=',$category)
->whereIn('site_id', array(self::$site_id, 0))
->leftJoin('object_meta', function($join)
{
$join->on('object.id','=', 'object_meta.content_id');
})
->where('object_meta.variable', 'genre')
->where('object_meta.value', 3);
->groupBy('object.id')
->get();
foreach($objs as $obj)
{
$obj->id = $obj->object_id;
$objs_meta = ObjectMeta::where('object_id',$obj->object_id)->get();
foreach($objs_meta as $obj_meta)
{
$variable = $obj_meta->var;
$obj->$variable = $obj_meta->value;
}
}
$objs = $objs->sortBy(function($role){
return $role->released;
});
$objs = Paginator::make($objs->toArray(), sizeof($objs), $limit);
您需要内连接(join()
)而不是leftJoin()
,并且您需要两次:
$objs = static::Active()
->where('object', '=', $object)
->where('category','=',$category)
->whereIn('site_id', array(self::$site_id, 0))
->join('object_meta as genre', function ($join) {
$join->on('object.id', '=', 'genre.content_id')
->where('genre.variable', '=', 'genre')
->where('genre.value', '=', 3);
})
->join('object_meta as released', function ($join) {
$join->on('object.id', '=', 'released.content_id')
->where('released.variable', '=', 'released');
})
->orderBy('released.value', 'desc');
->select('object.*', 'released.value as released')
->distinct()
// or group by, doesn't matter in your case
// ->groupBy('object.id')
->paginate(20);
那么你的对象将有额外的属性released
与适当的值。
根据注释:
如果你想动态添加连接,那么我建议像下面这样的作用域:
public function scopeGenre($query, $value)
{
$query->join('object_meta as genre', function ($join) use ($value) {
$join->on('object.id', '=', 'genre.content_id')
->where('genre.variable', '=', 'genre')
->where('genre.value', '=', $value);
});
}
:
$query->where(..)->genre(3)->...