Many to many relation

[archansel]

The document only give example for one to many relationship, can I use search for many to many relationship?

For example: books have many to many relation with authors so I define another table book_author that contains the many to many relation

Book

• id
• name
• description

Author

• id
• name

Book_Author

• book_id
• author_id

So, how can I search book.name, book.description, and author.name?

[cmbirk]

+1

[haleksandre]

I think it's possible already, you just need to add the many to many the relations manually in the joins array

/**
 * Searchable rules.
 *
 * @var array
 */
protected $searchable = [
    'columns' => [
         'book.name' => 10, 
         'book.description' => 10,
         'author.name' => 5,
    ],
    'joins' => [
        'book_author' => ['book.id', 'book_author.book_id'],
        'author' => ['book_author.author_id', 'author.id'],
    ],
];

Then you can add the Eloquent relationship with the with() method if you need access to the Author model

Book::search($query)->with('authors')->get();

[archansel]

@haleksandre thanks man, it works. This should be included in documentation

[nowshad-sust]

@haleksandre you're solution seems good. But can't solve my problem though.

I used Conner\Tagging\Taggable package for tagging in my posts relations are like

posts

id name description

tagging_tags

id name slug count

tagging_tagged

taggable_id (just an integer not a foreign key) tag_slug tag_name

I've tried 'columns' => [ 'posts.title' => 10, 'posts.slug' => 10, 'tagging_tags.slug' => 2, 'tagging_tags.name' => 1 ], 'joins' => [ 'tagging_tagged' => ['tagging_tagged.taggable_id','posts.id'], 'tagging_tags' => [ 'tagging_tags.slug','tagging_tagged.tag_slug'], ],

But some posts are appearing multiple times. any help would be appreciated @nicolaslopezj .

[juandelperal]

@nowshad-sust I fixed adding a 'groupBy' => 'posts.id', in the model's $searchable array

[rajenghorecha]

Currently, I've the below table structure where project has 2 relations with users table one to one(General Contact) and many to many(Technical Contact). I have applied above solution but it search for only general contact / technical contacts not for both. How can set the configuration for both?

Projects id name general_contact(one user from users table) description

Projects_has_tech_users

• projects_id
• users_id

Users id firstname lastname email password role

Here is the configuration which I've set

protected $searchable = [ 'columns' => [ 'projects.name' => 1, 'users.first_name' => 2, 'users.last_name' => 2, ], 'joins' => [ 'projects_has_tech_users' => ['projects.id','projects_has_tech_users.projects_id'], 'users' => ['projects_has_tech_users.users_id', 'users.id'], 'users' => ['projects.general_contact', 'users.id'] ], 'groupBy'=>'projects.id' ];

[rajenghorecha]

I've got the solution by giving the alias with the join table

protected $searchable = [
    'columns' => [
        'projects.name' => 1,
        'tech.first_name' => 3,
        'tech.last_name' => 3,
        'gen.first_name' => 3,
        'gen.last_name' => 3,
        'projects.description' => 4,
    ],
    'joins' => [
        'projects_has_tech_users' => ['projects.id','projects_has_tech_users.projects_id'],
        'users as tech' => ['projects_has_tech_users.users_id', 'tech.id'],
        'users as gen' => ['projects.general_contact', 'gen.id']
    ],
    'groupBy'=>'projects.id'
];