如何从stories表中获取story_id和user_id。

Question

这是PHP Laravel，SQL LeftJoin查询问题。我有两个表，名为"stories“和"favorite_story”。我想基于"story_id“返回来自"stories”表的所有值和来自"favorite_story“表的匹配值，这是两个表中的公共字段。

在数组形式的结果中你可以看到"story_id"，"user_id“结果都是空的，这不应该是空的。Fav_id为空，因为"favorite_story“表为空，所以没有关系。

我希望"story_id“是从stories表中获取的，"user_id”是从"favorite_story“表中获取的。

这是我的控制器代码：

public function fanfiction(){
    $user_id = Session('userid');
    $data['stories'] =  DB::table('stories')
    ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')
    ->Select('*')
    ->get();

    return view('fanfiction', $data);
}

这是上述代码在数组形式中的结果。

        [0] => stdClass Object
            (
                [story_id] => 
                [story_title] => Third Story
                [story_desc] => This is "Times" story summary.
                [img] => 2018-12-26-70pgFK87ZN.png
                [story_genre] => Fighting
                [story_character] => Aoba Y.
                [story_rating] => Adult Only
                [story_warning] => Drug Use
                [story_date] => December 26, 2018
                [series_id] => 9999999
                [user_id] => 
                [fav_id] => 
            )

        [1] => stdClass Object
            (
                [story_id] => 
                [story_title] => Second Story
                [story_desc] => This is summary of this new story.
                [img] => 2018-12-19-e6JnEpo3ky.jpg
                [story_genre] => Friendship
                [story_character] => Aijsai
                [story_rating] => G
                [story_warning] => Rape (Actual)
                [story_date] => December 19, 2018
                [series_id] => 9999999
                [user_id] => 
                [fav_id] => 
            )

任何帮助都将不胜感激。

Answer 1

将您的查询更改为：

public function fanfiction()
{
    $user_id = session->get('userid');

    $data['stories'] =  DB::table('stories')
        ->leftJoin('favorite_story', 'favorite_story.story_id','=','stories.story_id')
        ->select('stories.*') // add other fields that you need from favorite_story table here
        ->get();

    return view('fanfiction', $data);
}

发生这种情况的原因是，它从favorite_story表中获取user_id和story_id，在本例中为null。