Select from multiple tables with laravel fluent query builder

Question

I'm re-writing some PHP/MySQL to work with Laravel. One thing I would like to do is make the DB queries more succinct with the Fluent Query Builder but I'm a bit lost:

SELECT p.post_text, p.bbcode_uid, u.username, t.forum_id, t.topic_title, t.topic_time, t.topic_id, t.topic_poster
FROM phpbb_topics t, phpbb_posts p, phpbb_users u
WHERE t.forum_id = 9
AND p.post_id = t.topic_first_post_id
AND u.user_id = t.topic_poster
ORDER BY t.topic_time
DESC LIMIT 10

This queries a phpbb forum and gets posts: enter image description here

How could I re-write this to make use of the Fluent Query Builder syntax?

miken32 · Accepted Answer · 2019-02-26 21:33:57Z

29

Not tested but here is a start

return DB::table('phpbb_topics')
    ->join('phpbb_posts', 'phpbb_topics.topic_first_post_id', '=', 'phpbb_posts.post_id')
    ->join('phpbb_users', 'phpbb_topics.topic_poster', '=', 'phpbb_users.user_id')
    ->order_by('topic_time', 'desc')
    ->take(10)
    ->get(array(
        'post_text',
        'bbcode_uid',
        'username',
        'forum_id',
        'topic_title',
        'topic_time',
        'topic_id',
        'topic_poster'
    ));

edited Feb 26, 2019 at 21:33

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Jan 22, 2013 at 18:04

itachi

6,3933 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sadat Over a year ago

There is an option like below(tested in 5.3)- ->get(array('phpbb_topics.*', 'username', 'phpbb_posts.post_text' )

Daddi Al Amoudi Over a year ago

this one solved my question. how to link this answer to my question? EDIT: I got it

Gjaa · Accepted Answer · 2019-05-07 22:24:01Z

6

return DB::table(DB::raw('phpbb_topics t, phpbb_posts p, phpbb_users u')) 
->select(DB::raw('p.post_text, p.bbcode_uid, u.username, t.forum_id, t.topic_title, t.topic_time, t.topic_id, t.topic_poster'))
->where('phpbb_topics.topic_first_post_id', '=', 'phpbb_posts.post_id')
->where('phpbb_users', 'phpbb_topics.topic_poster', '=', 'phpbb_users.user_id')
->order_by('topic_time', 'desc')->take(10)->get();

edited May 7, 2019 at 22:24

Gjaa

1,5771 gold badge21 silver badges20 bronze badges

answered Aug 22, 2014 at 9:42

user3967434

711 silver badge1 bronze badge

1 Comment

Ashwin Bhamare Over a year ago

Use distinct(), otherwise, it will give a repetitive data as a base on the tables count

Collectives™ on Stack Overflow

Select from multiple tables with laravel fluent query builder

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related