Proper way to format a many to many query with the query builder

Hello, i'm trying to build a query with the query builder only (NOT the full eloquent) used outside laravel.

Here is the context:

A blog post can be about several models (like a top model). And a model can belong to several blog post.

Table: model_names
This table contains 2 models each with 2 fields:
**id**: 1. **Name**: Brenda
**id**: 3. **Name**: Lucia

Table: blog_posts
This table contains a post with 2 fields:
**id**: 12. **blog_title**: This is a title

Table: model_names_relations (holding the many to many foreign keys ids of the other 2 tables).
Contains 2 relations:
**id**: 1. **blog_post_id**: 12. **Model_name_id**: 3
**id**: 2. **blog_post_id**: 12. **Model_name_id**: 1

So as you can see, the blog post has a relation to 2 models.

My goal is to build a query, so that given a model id ( 12 in this case) will return related models names (Brenda and Lucia in this case). So I want the result of the query to be ultimately "Brenda" "Lucia"

How to achieve this with the query builder? I guess I should use joins ?

brakkar

9 years ago

Best Answer

Level 6

Turns out to be:

 $query = ( new DbSql )->db()->table( 'blog_posts' )
            ->join( 'model_names_relations', 'blog_posts.id', '=', 'model_names_relations.blog_post_id' )
            ->join( 'model_names', 'model_names.id', '=', 'model_names_relations.model_name_id' )
            ->where( 'blog_posts.id', '12' )
            ->select('model_names.name')
            ->get();

2 likes

JeffBeltran

8 years ago

Level 5

Thanks this helped me with my filters i have setup where i chain off a query builder object

Please or to participate in this conversation.