CODEWITHSUNDEEP
sqljoinfilterlaraveleloquent
Michael Messerli
Michael Messerli

Reputation: 95

Joining multiple tables together with eloquent and eager loading in laravel [implementing filter]

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.

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, ))

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'.

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.

Upvotes: 3

Views: 3382

Answers (1)

user1669496
user1669496

Reputation: 33078

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.

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'.

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

Upvotes: 7

Related Questions