does not work with hybridrelations between mysql and mongodb relationships

[tucker-eric]

does not work with hybridrelations between mysql and mongodb relationships

[Tucker-Eric]

Ok, it should be good. I added some tests for hybrid whereHas with and everything is passing as of this commit. https://github.com/Tucker-Eric/laravel-mongodb/commit/818d7f66e1468b4c5ba8601474d585a082958dbf

[Tucker-Eric]

Skimming over the other library it looks like the issue may be in the addHasWhere() method that library overwrites returns a whereIn() instead of a whereExists() or whereCountQuery().

What version of Laravel are you using?

Do you get the same error running the following without the filters?

User::whereHas('profile', function($query) {
    return $query->where('gender', 'male');
})->get();

[vesper8]

I think you're on the right track! I'm using Laravel 5.4. And yes I do get the same error running the code you supplied

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'exists ? and `gender` = ?)' at line 1 (SQL: select * from `users` where exists (select `user_id` from `profile` where `user_id` exists 1 and `gender` = male))

[Tucker-Eric]

It looks like they should override the has() method instead because that's where the exists constraint is being used. That needs to be a whereIn() or nested whereIn() if using whereHas() instead. If that is done correctly the filters would work between databases. All non-joined relations in the filters are queried using the whereHas() method which in turn uses the has method under the hood.

[vesper8]

well.. upon doing a little more digging on the very popular package https://github.com/jenssegers/laravel-mongodb/issues?utf8=%E2%9C%93&q=wherehas

It looks like having the "wherehas" working properly for hybrid relations has been a repeatedly requested feature going all the way back to 2014.. and the owner himself has said that implementing this seems very difficult.. and people continue to ask for it.. myself included.

I think unfortunately it must not be easy to make it work at all.. or in any case no one seems to know how to do it. If you think you have an idea of how to make it work and wanted to give it a try at adding a PR there.. I think that would make you a real hero ;)

[Tucker-Eric]

I threw together a rough patch for it. You wanna give it a test spin before I submit the pr?

https://github.com/Tucker-Eric/laravel-mongodb/commit/266305caeed70cf8ee787bcd0140c9fca2f0bcd6

[vesper8]

absolutely! will test it today and let you know if I run into any troubles.

wow thanks for taking this on!! I will support your PR as I'm sure others will.. it's been a long time coming!

Does using this branch also automagically makes your EloquentFilter work on mysql->mongo hybrid relations?

[Tucker-Eric]

Yup! Although I haven't tested the 2 together, there isn't anything that should break it in this branch. This will also work mongo->mysql relationships too. It just won't support belongsToMany() because that's​ queried with an inner join.

[vesper8]

Alright alright! I pulled in your branch and am glad I did because I found a problem.

On the one hand, this query which used to fail now works!

                    $query = User::whereHas('profile',
                        function ($query) {
                            $query->where('someBoolField', true);
                        })->with([
                        'profile' => function ($query) use ($profileFields) {
                            $query->select($profileFields);
                        },
                    ]);

So that's a win! Awesome!

On the other hand, this query which worked before, now fails!

        $this->data['matches'] = User::whereHas('matches',
            function ($query) {
                $query->where('user_id_1', \Auth::id())
                    ->orwhere('user_id_2', \Auth::id());
            })->with([
            'profile' => function ($query) use ($profileFields) {
                $query->select($profileFields);
            },
        ])->get($userFields)
            ->except(\Auth::id());

Where User and Match are mysql models and Profile is a mongodb model

So it seems that your fix has corrected the mysql->whereHas(mongodb)->with(mongodb) but is now interfering with the scenario mysql->whereHas(mysql)->with(mongodb)

this is my user model in case it's helpful

<?php

namespace App;

use EloquentFilter\Filterable;
use Jenssegers\Mongodb\Eloquent\HybridRelations;
use Laravel\Spark\User as SparkUser;

class User extends SparkUser
{
    use HybridRelations, Filterable;

    public function modelFilter()
    {
        return $this->provideFilter(\Omitted\Models\Filters\UserFilter::class);
    }

    public function profile()
    {
        return $this->hasOne('\Omitted\Models\Profile');
    }

    public function matches()
    {
        return $this->hasMany('\Omitted\Models\Match', 'user_id_1');
    }
}

Let me know if you need me to provide more info

[vesper8]

Seems to work beautifully!!

Tried the eloquent filter as well

                    $input = [
                        'gender' => 'female',
                    ];

                    $users = User::filter($input)->with('profile');

That works perfectly too!

And re-tried the two scenarios that I mentioned above and they now both work as expected!

That's some really fantastic work you've done here! Thank you so much! I'll be using your branch for now.

I hope @jenssegers approves your PR quickly! I know many of us have been waiting for has() and whereHas() to be added to his library for a long time so you're going to make a lot of happy people with this!

You rock :)

[Tucker-Eric]

I'm going to close this for now. We can reopen if there are issues with that pr.