在laravel(实现过滤器)中将多个表连接在一起，并进行有效且快速的加载

What I have already and what I'm trying to do:

我已经拥有的和我正在尝试做的:

I have a website that lets users make posts advertising themselves to other users. Now, I'm trying to implement a filter for these posts but filter by values in the users table, posts table and three others. This is where I'm running into some trouble.

我有一个网站，让用户发布自己的广告给其他用户。现在，我尝试为这些post实现一个过滤器，但是要根据users表、posts表和其他三个表中的值进行过滤。这就是我遇到麻烦的地方。

Code that works, as is, no filter:

有效的代码，因为没有过滤器:

$posts = Post::with('user','lookingfors','playstyles', 'postcomments')->orderBy('id', 'DESC')->paginate(10);

return View::make('posts/index', compact('posts'));

This is what I've tried:

这就是我所尝试的:

$posts = User::leftjoin('posts', function($join)use($region){
            $join->on('users.id', '=', 'posts.user_id');
            $join->on('playstyle_post.post_id', '=', 'posts.id'); // join pivot, then join pivot to playstyles
            $join->on('playstyle_post.playstyle_id', '=', 'playstyles.id');
            //$join->on;
        })->where('users.region_id', 'like', $region)->get();

This one keeps bringing up this error:

这个错误不断出现:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'playstyle_post.post_id' in 'on clause' (SQL: select * from users left join posts on users.id = posts.user_id and playstyle_post.post_id = posts.id and playstyle_post.playstyle_id = playstyles.id where users.region_id like ?) (Bindings: array ( 0 => 6, ))

SQLSTATE[42S22]:未找到的列:1054未知列'playstyle_post。在“on子句”(SQL: select * from users)上的post_id' (SQL: select *)。id =。user_id playstyle_post。post_id =。id和playstyle_post。playstyle_id =战斗方式。id,用户。region_id like ?)(绑定:数组(0 => 6，)

Also:

另外:

$posts = Post::with('lookingfors', 'playstyles', 'postcomments')
        ->leftjoin('users', 'posts.user_id', '=', 'users.id')
        //->join('playstyle_post', 'posts.id', '=', 'playstyle_post.post_id')
        ->paginate(10);

This one is odd ( well for me ), when 'leftjoin' is commented out obviously it works just as before, but when I have it as it is now it displays the view as it should and even pulls all the right values from the users table but does not display any of the information thats being requested through the 'with'.

这一个是奇数(对我来说),当“leftjoin”显然被注释掉了它的工作原理就像之前,但当我把它作为现在它显示认为它应该,甚至把所有正确的值从用户表,但不显示任何信息通过”与“被请求。

I've tried a couple other things in the last two weeks, but I've already thrown them out when they didn't work. This has been kicking my ass for the better half of a month and I can't wrap my head around why I can't get it to work.

在过去的两周里，我尝试了一些其他的东西，但当它们不起作用时，我已经把它们扔掉了。这事已经折磨了我半个月了，我也不明白为什么我不能让它工作。

So really, my trouble right now is just trying to join the tables and returning it to the view in an eloquent fashion so I can keep treating it the same. The filter itself will be a problem of its own, but getting the joins to work has proved to be the hard part.

所以，我现在的麻烦就是试着加入到讨论中，然后以一种有说服力的方式把它回归到观点中，这样我就可以继续把它当作一样的东西来对待。过滤器本身将是一个问题，但是使连接工作已被证明是困难的部分。

1 个解决方案

#1

Instead of passing a bunch of relationship names to the with() function, try passing an array, setting keys as the names of the relationships and the values as callback functions and pass in the query for actual editing. Also will have to use use ($region) where needed. Hopefully you get the idea.

与其向with()函数传递一系列关系名称，不如尝试传递一个数组，将键设置为关系的名称，将值设置为回调函数，并将查询传递给实际的编辑。还必须在需要时使用($region)。希望你们能理解。

I put in some examples for adding filters on each relationship. Because you are working locally with each one, you shouldn't have to worry about prefixing them with table names or aliases.

我在每个关系中加入了一些添加过滤器的例子。因为您正在本地处理每个表，所以不需要担心在它们前面加上表名或别名。

$posts = Post::with(array(
'user' => function($query) use ($region)
{
    // User constraints here
    $query->where('status', 'A');
    $query->where('deleted', '0');
}, 
'lookingfors' => function($query)
{
    // lookingfors constraints here
    $query->where('name', 'somelookingforname');
}, 
'playstyles' => function($query)
{
    // playstles constraints here
    $query->where('name', 'someplaystylesname');
}, 
'postcomments' => function($query)
{
    // Some postcomments constraints here
    $query->where('name', 'somepostcommentsname');
}))
->orderBy('id', 'DESC')
->paginate(10);

Looking at your attempt at left joins though, I don't know if these relationships are correct. My example assumes all the relationships can relate directly back to the user table. Otherwise, you will need to change the keys on the array to match how the relationships should be.

看看你尝试左连接，我不知道这些关系是否正确。我的示例假设所有关系都可以直接关联到用户表。否则，您将需要更改数组中的键，以匹配关系应该如何。

For example, if trying to relate to the playstyles table, but the relationship between playstyles and posts doesn't exist because you need to go through the users table first, you would just change 'playstyles' to 'users.playstyles'.

例如，如果试图与playstyles表关联，但是playstyles和post之间的关系并不存在，因为您需要首先遍历users表，那么只需将“playstyles”更改为“user .playstyles”。

If you post your migrations, I could help you further.

如果你发布你的迁移，我可以进一步帮助你。

#1