Is it possible to make advanced queries using pivot tables?

[subsider]

Hi, Having the Product model with the following relationship through products_categories table:

    public function sectors()
    {
        return $this->belongsToMany('Category', 'products_categories', 'product_id', 'category_id');
    }

how can I use $searchable in order to join products with categories? In the readme file you show an example using 1:N relationships (user has many posts), but no idea how to reflect N:M relationship with this

Thanks in advance

[sergiocastrovale]

I'd like to know this as well. I have a Post table which links to category_post (pivot) and I'd like to be able to search for a Category and retrieve results.

[sergiocastrovale]

Basically, this: http://stackoverflow.com/a/17774479/2261056

[emacaste]

@sergiocastrovale how you do the trick?

[sergiocastrovale]

Hi, this is working for me. It joins 3 tables which have pivots to Texture.

protected $searchable = [
    'columns' => [
        'textures.name' => 20,
        'tags.name' => 10,
        'categories.name' => 5,
        'sizes.name' => 25
    ],
    'joins' => [
        'category_texture' => ['category_texture.texture_id', 'textures.id'],
        'categories' => ['categories.id','category_texture.category_id'],
        'tag_texture' => ['tag_texture.texture_id','textures.id'],
        'tags' => ['tags.id','tag_texture.tag_id'],
        'size_texture' => ['size_texture.texture_id','textures.id'],
        'sizes' => ['sizes.id','size_texture.size_id'],
    ],
];

And then:

Texture::with(['categories', 'tags', 'sizes'])->search($text)->distinct('textures.id')->get();

Please let me know if you find any issues with this - they might appear on my end as well.

[emacaste]

@sergiocastrovale Thx, it works very well, but it is veeeeeeeeery slow (due to subqueries i think). Don't you know if there is a more optimized solution for pivot search out of there?

[sergiocastrovale]

I'm not sure. This took a while to get right and it's the best way I could find following the documentation of the package. However if @nicolaslopezj could jump in on this with a better solution, that would be awesome.

[emacaste]

FYI, i have a lot of relations hasmany and manytomany with thousands records each...maybe that is the cause of slowness (in combination with subqueries)

[sergiocastrovale]

Yeah, it makes sense. I'm using a very small dummy database here, so I don't feel any real impact at all, but when the project goes live I'll have to browse through 15k+ results so your problem will be my problem as well ...

[emacaste]

@sergiocastrovale using straight eloquent methods dramatically speed up queries. Now i'm using whereHas, orWhereHas, etc. and mysql took 1.3 seconds, whereas with searchable i have to wait 12-13 seconds... I've lost score system (very interesting), but for now i privilege speed.

Maybe a good solution would be mixup eloquent for joins and score system?

[sergiocastrovale]

Could you post your equivalent query in straight eloquent? I'm not able to test it now but I'd like to check it tomorrow. The score system isn't my top priority as well (although it would be nice to have it).

[emacaste]

Sure, here the code:

$term = Input::get('term') . '%';

        $items = Item::with('subject', 'type')
            ->orWhereHas('subject', function ($query) use ($term) {
                $query->where('nome', 'LIKE', $term);
            })
            ->orWhereHas('author', function ($query) use ($term) {
                $query->where('nome', 'LIKE', $term);
            })
            ->orWhere('titolo', 'LIKE', $term)
            ->orWhere('descrizione', 'LIKE', $term)
            ->orWhere('commento', 'LIKE', $term)
            ->orWhere('note', 'LIKE', $term)
            ->orWhere('isbn', 'LIKE', $term)
            ->paginate(20);

Subject and Author are ManyToMany with Item. Eloquent translate orwherehas into subquery that not is the best approach, but for now it works well for me.

[sergiocastrovale]

Thanks a lot, I'll take a look at this tomorrow. But perhaps the best results will show up when I have the full database with me.

[jarektkaczyk]

@beniaminorossini Show the relations and number of rows involved, that make your query slow.

With core eloquent features, there's no way to achieve scoring using whereHas, but I'm on it right now and hopefully I'll find the most optimized solution to the issue soon.

[emacaste]

@jarektkaczyk thx a lot!

[jarektkaczyk]

@beniaminorossini @sergiocastrovale @nicolaslopezj

So I've worked on improving performance and usability, and here it goes: https://github.com/jarektkaczyk/eloquence/wiki/Builder---searchable-and-more

Check it out guys, the scoring algo is based on the one use here by Nicolas as well as the basic idea, however it is greatly improved:

• more than 4 times faster (eg. 2 secs vs 9 secs on my 2m * 120 * 30 rows search)
• compatible with all eloquent/query methods (including count, paginate etc)
• allows specifying relations/columns to search through at runtime - no need to define anything in the models
• fulltext search by default, with possibility to search for whole phrases explicitly or with wildcards
• and more..

Example:

User::search(
    '"Billy the Kid" "* Billy the Kid *" Billy Kid', // phrase with wildcards 
    ['name', 'posts.title', 'posts.comments.body'], // specify related columns 
    false // turn off fulltext 
)

It written from scratch with totally different approach in mind, so it couldn't be incorporated here as any PR, no intention to undervalue your great work Nicolas.

Worth mentioning that you don't have to worry about the joins - it works on eloquent relations with dot nesting, just like with('some.relation') method, so you literally don't have to touch your model (just use the base trait shipped with the package).

Give it a try and let me know.

[emacaste]

thx so much @jarektkaczyk i will try it soon

[sergiocastrovale]

That looks promising! I will see if my client lets me try this ;)